Updated on 2025-09-09 GMT+08:00

Date and Time Data Types

Table 1 Date and Time Data Types

MySQL

GaussDB

Difference

DATE

Supported, with differences.

GaussDB supports the DATE data type and differs from MySQL in terms of the following specifications:

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 followed by 0.

DATETIME[(fsp)]

Supported, with differences.

GaussDB supports the DATETIME data type and differs from MySQL in terms of the following specifications:

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 followed by 0.

TIMESTAMP[(fsp)]

Supported, with differences.

GaussDB supports the TIMESTAMP data type and differs from MySQL in terms of the following specifications:

  • 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 followed by 0.
  • MySQL supports explicit_defaults_for_timestamp. When explicit_defaults_for_timestamp is set to off, setting the default value of the TIMESTAMP column and inserting NULL are non-standard behaviors. The default value of explicit_defaults_for_timestamp is off in MySQL 5.7 and is on in MySQL 8.0. GaussDB does not support explicit_defaults_for_timestamp. The behavior is the same as that when explicit_defaults_for_timestamp is set to on in MySQL. For details about explicit_defaults_for_timestamp, see the note below the table.

TIME[(fsp)]

Supported, with differences.

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

  • 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 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.

YEAR[(4)]

Supported.

-

  • GaussDB does not support ODBC syntax literals:

    { d 'str' }

    { t 'str' }

    { ts 'str' }

  • If you specify a precision for the DATETIME, TIME, or TIMESTAMP data type greater than the maximum precision supported by the data type, GaussDB truncates the precision to the maximum precision supported by the data type, whereas MySQL reports an error.
  • In MySQL, when explicit_defaults_for_timestamp is set to off, the processing logic of the TIMESTAMP columns is as follows:
    • If NULL or NOT NULL attribute is not explicitly specified for a column, the NOT NULL attribute will be automatically added. When a NULL value is inserted into such a column, the NULL value is replaced with the current timestamp.
    • If the NULL attribute is not specified for the first TIMESTAMP column in a table, the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes will be automatically added to the column.
    • If the NULL attribute is not specified for the second and subsequent TIMESTAMP columns in a table, the DEFAULT '0000-00-00 00:00:00' attribute will be automatically added to the columns.
  • In MySQL, when explicit_defaults_for_timestamp is set to off, the processing logic of the TIMESTAMP columns is as follows:
    • When a NULL value is inserted into a TIMESTAMP column, the NULL value is not replaced with the current timestamp.
    • If NULL or NOT NULL attribute is not explicitly specified for a column, the NULL attribute will be automatically added.
    • When a NULL value is inserted into a column with the NOT NULL attribute specified, an error is reported in strict mode, and '0000-00-00 00:00:00' is inserted in loose mode.
    • DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes will not be automatically added to any TIMESTAMP columns.