The DATE, DATETIME, and TIMESTAMP types are related. This section describes their characteristics, similarities, and differences.
DATE
The DATE type is used to represent values that have a date part but no time part.
The retrieval and display format for DATE values is 'YYYY-MM-DD', with a range from '0000-00-00' to '9999-12-31'.
OceanBase AI Database allows you to assign values to a DATE column by using strings or numbers.
The syntax is as follows:
DATE
DATETIME
The DATETIME data type is used to represent values that contain both date and time parts.
The retrieval and display format for DATETIME values is 'YYYY-MM-DD hh:mm:ss', with a range from '0000-00-00 00:00:00' to '9999-12-31 23:59:59'.
The syntax is as follows:
DATETIME[(fsp)]
The optional fsp value specifies the precision of the number of decimal places in seconds, with a range of [0,6]. A value of 0 indicates no decimal part. If omitted, the default precision is 0.
The DATETIME column uses the DEFAULT and ON UPDATE clauses to specify automatic initialization with the current date and time, and automatic update on update.
A DATETIME value can include a fractional second value with a precision of up to microseconds (6 digits), in the format 'YYYY-MM-DD hh:mm:ss[.fraction]'. The range is from '0000-00-00 00:00:00.000000' to '9999-12-31 23:59:59.999999'. The fractional part must always be separated from other time fields by a decimal point; other separators are not recognized.
TIMESTAMP
The TIMESTAMP data type is used to represent values that contain both date and time parts.
The syntax is as follows:
TIMESTAMP[(fsp)]
You can specify the precision of the decimal seconds using the fsp value, which ranges from [0,6]. A value of 0 indicates no decimal part. If omitted, the default precision is 0.
For example:
CREATE TABLE t(C1 TIMESTAMP(6));
You can use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses for a TIMESTAMP column to specify automatic initialization with the current date and time, and automatic update on update. By default, the first TIMESTAMP column in a table has these attributes, but you can also define them for other TIMESTAMP columns in the table.
TIMESTAMP values can include a fractional second value, with a maximum precision of microseconds (6 digits), in the format 'YYYY-MM-DD hh:mm:ss[.fraction]'. The range is from '0000-00-00 00:00:00.000000' to '9999-12-31 23:59:59.999999'. The fractional part must always be separated from other time fields by a decimal point; other separators are not recognized.
OceanBase AI Database converts TIMESTAMP values from the current time zone to UTC for storage and then converts them back from UTC to the current time zone for retrieval. By default, the current time zone for each connection follows the server's time, but you can also change the time zone for each connection. If different time zones are used for the round-trip conversion, the retrieved values may differ from the stored values after changing the time zone. As long as the time zone setting remains unchanged, the stored values can be retrieved. The current time zone can be obtained as the value of the time_zone system variable.