Help Center/ GaussDB/ MySQL Compatibility(Distributed)/ MySQL Compatibility in M-compatible Mode/ Data Types/ Date and Time Data Types
Updated on 2024-12-06 GMT+08:00
View PDF
Share

Date and Time Data Types

Table 1 Date and time data types

No.

MySQL

GaussDB

Difference

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 does not support ODBC syntax literals:

    { d 'str' }

    { t 'str' }

    { ts 'str' }

  • GaussDB supports standard SQL literals, and precision can be added after type keywords, but MySQL does not support the following:

    DATE[(n)] 'str'

    TIME[(n)] 'str'

    TIMESTAMP[(n)] '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.
Parent topic: Data Types