Date and Time Data Types

1

DATE

Supported, with differences.

GaussDB supports the date data type. Compared with MySQL, GaussDB has the following differences in specifications:

• Input formats:
  • Separator: A backslash (\) is regarded as an escape character in both MySQL and GaussDB. However, MySQL supports \0, but GaussDB does not support \0. Therefore, GaussDB reports an error when the backslash is used as a separator and the separator is followed by 0.

2

DATETIME[(fsp)]

Supported, with differences.

GaussDB supports the datetime data type. Compared with MySQL, GaussDB has the following differences in specifications:

• Input formats:
  • Separator: A backslash (\) is regarded as an escape character in both MySQL and GaussDB. However, MySQL supports \0, but GaussDB does not support \0. Therefore, GaussDB reports an error when the backslash is used as a separator and the separator is followed by 0.

3

TIMESTAMP[(fsp)]

Supported, with differences.

GaussDB supports the timestamp data type. Compared with MySQL, GaussDB has the following differences in specifications:

• Input formats:
  • Separator: A backslash (\) is regarded as an escape character in both MySQL and GaussDB. However, MySQL supports \0, but GaussDB does not support \0. Therefore, GaussDB reports an error when the backslash is used as a separator and the separator is followed by 0.
• Default value:
  • In MySQL 5.7, the default value of the timestamp column is the real time when data is inserted. Same as MySQL 8.0, GaussDB has no default value set for this column. That is, when null is inserted, the value is null.

4

TIME[(fsp)]

Supported, with differences.

GaussDB supports the time data type. Compared with MySQL, GaussDB has the following differences in specifications:

• Input formats:
  • Separator: A backslash (\) is regarded as an escape character in both MySQL and GaussDB. However, MySQL supports \0, but GaussDB does not support \0. Therefore, GaussDB reports an error when the backslash is used as a separator and the separator is followed by 0.
• When the hour, minute, second, and nanosecond of the time type are 0, the sign bits of GaussDB and MySQL may be different.

5

YEAR[(4)]

Supported.

GaussDB supports the year data type. Compared with MySQL, GaussDB has the following differences in specifications:

• When a field of the year type is created, "year" is displayed in GaussDB and "year(4)" is displayed in MySQL.

-- GaussDB
m_db=# create table t_year (c_year year);
CREATE TABLE
m_db=# desc t_year;
 Field  | Type | Null | Key | Default | Extra 
--------+------+------+-----+---------+-------
 c_year | year | YES  |     |         | 
(1 row)
m_db=# create table t1 as(select * from t_year);
INSERT 0 0
m_db=# desc t1;
 Field  | Type | Null | Key | Default | Extra 
--------+------+------+-----+---------+-------
 c_year | year | YES  |     |         | 
(1 row)
-- MySQL
mysql> create table t_year (c_year year);
Query OK, 0 rows affected (0.01 sec)
mysql> desc t_year;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| c_year | year(4) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> create table t1 as(select * from t_year);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc t1;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| c_year | year(4) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)