Date and Time Data Types

**Table 1** Date and Time Data Types
Data Type	Differences Compared with MySQL
DATE	None
DATETIME[(fsp)]	For details about the differences, see the description below the table.
TIMESTAMP[(fsp)]	GaussDB supports the TIMESTAMP data type. Compared with MySQL, GaussDB has the following differences in specifications: 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. For other differences, see the description below the table.
TIME[(fsp)]	GaussDB supports the TIME data type. Compared with MySQL, GaussDB has the following differences in specifications: When the hour, minute, second, and nanosecond of the TIME type are 0, the sign bits of GaussDB and MySQL may be different. For other differences, see the description below the table.
YEAR[(4)]	GaussDB supports the YEAR data type. Compared with MySQL, GaussDB has the following differences in specifications: In MySQL 5.7, the YEAR column is displayed as YEAR(4) by default. GaussDB is consistent with MySQL 8.0, displaying only YEAR.

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.