DATE, DATETIME and TIMESTAMP types are related to each other. This topic describes their features, similarities, and differences.
DATE
The DATE type is used to represent a value that has only the date part.
A DATE value is searched and displayed in the 'YYYY-MM-DD' format in the range of '0000-00-00' to '9999-12-31'.
OceanBase Database allows you to assign string or numeric values to a DATE column.
The syntax is as follows:
DATE
DATETIME
The DATETIME type is used to represent a value that contains both date and time parts.
A DATETIME value is searched and displayed in the 'YYYY-MM-DD hh:mm:ss' format in the range of '0000-00-00 00:00:00' to '9999-12-31 23:59:59'.
The syntax is as follows:
DATETIME[(fsp)]
The optional parameter fsp specifies the precision of the fractional part. The value range is [0,6]. The value 0 indicates that the time value does not have the fractional part. If you omit this parameter, the default value 0 is used.
You can use the DEFAULT and ON UPDATE clauses to specify the automatic initialization and update of the date and time values in the DATETIME column.
A DATETIME value can be displayed in the 'YYYY-MM-DD hh:mm:ss[.fraction]' format where the fractional part can contain up to six decimal places, which means the time can be accurate to microseconds. The value ranges from '0000-00-00 00:00:00.000000' to '9999-12-31 23:59:59.999999'. The fractional part is separated from other digits with a decimal point. Other separators are not recognized.
TIMESTAMP
The TIMESTAMP type is used to represent a value that contains both date and time parts.
The syntax is as follows:
TIMESTAMP[(fsp)]
The optional parameter fsp specifies the precision of the fractional part. The value range is [0,6]. The value 0 indicates that the time value does not have the fractional part. If you omit this parameter, the default value 0 is used.
Here is an example:
CREATE TABLE t(C1 TIMESTAMP(6));
You can use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses to specify the automatic initialization and update of the date and time values in the TIMESTAMP column. By default, the first TIMESTAMP column has such attributes. You can also specify these attributes for other TIMESTAMP columns in the table.
A TIMESTAMP value can be displayed in the 'YYYY-MM-DD hh:mm:ss[.fraction]' format where the fractional part can contain up to six decimal places, which means the time can be accurate to microseconds. The value ranges from '0000-00-00 00:00:00.000000' to '9999-12-31 23:59:59.999999'. The fractional part is separated from other digits with a decimal point. Other separators are not recognized.
OceanBase Database converts a TIMESTAMP value from the local time into the UTC time for storage and converts the UTC time back into the local time for retrieval. By default, the time zone on the server is used as the current time zone for each connection. However, you can also change the time zone for each connection. If you use different time zones during the two-way time conversion, the value retrieved after the time zone change may be different from the stored value. As long as the time zones remain unchanged, the stored values can be retrieved. The current time zone can be the value of the time_zone variable.