DATE, DATETIME, and TIMESTAMP types are related. This topic describes their features, similarities, and differences.
DATE
The DATE type is used to store values that have a date part but no time part.
The retrieval and display format of a DATE value is 'YYYY-MM-DD', and the value range is from '0000-00-00' to '9999-12-31'.
OceanBase Database allows you to assign a value to a DATE column by using a string or a number.
Syntax:
DATE
DATETIME
The DATETIME type is used to store values that have both a date and a time part.
The retrieval and display format of a DATETIME value is 'YYYY-MM-DD hh:mm:ss', and the value range is from '0000-00-00 00:00:00' to '9999-12-31 23:59:59'.
Syntax:
DATETIME[(fsp)]
The optional fsp parameter specifies the precision of the fractional seconds, which ranges from 0 to 6. A value of 0 indicates no fractional part. If you omit this parameter, the default precision is 0.
You can use the DEFAULT and ON UPDATE clauses to specify the automatic initialization and update of a DATETIME column with the current date and time.
A DATETIME value can have a fractional part of seconds with a precision of up to six digits (microseconds), in the format 'YYYY-MM-DD hh:mm:ss[.fraction]', with a value range from '0000-00-00 00:00:00.000000' to '9999-12-31 23:59:59.999999'. The fractional part must be separated from other time parts by a decimal point. Other separators are not recognized.
TIMESTAMP
The TIMESTAMP type is used to store values that have both a date and a time part.
Syntax:
TIMESTAMP[(fsp)]
The optional fsp parameter specifies the precision of the fractional seconds, which ranges from 0 to 6. A value of 0 indicates no fractional part. If you omit this parameter, the default precision is 0.
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 a TIMESTAMP column with the current date and time. By default, the first TIMESTAMP column has these attributes. You can also define other TIMESTAMP columns in the table to have these attributes.
A TIMESTAMP value can have a fractional part of seconds with a precision of up to six digits (microseconds), in the format 'YYYY-MM-DD hh:mm:ss[.fraction]', with a value range from '0000-00-00 00:00:00.000000' to '9999-12-31 23:59:59.999999'. The fractional part must be separated from other time parts by a decimal point. Other separators are not recognized.
OceanBase Database converts TIMESTAMP values from the current time zone to UTC for storage, and then converts them back to the current time zone for retrieval. By default, the current time zone of each connection follows the server's time, but you can change the time zone for each connection. If the time zones are not the same during the conversion, the retrieved values may differ from the stored values. As long as the time zone settings remain unchanged, the stored values can be retrieved. The current time zone can be used as the value of the time_zone system variable.