DATE, DATETIME, and TIMESTAMP types
DATE, DATETIME, and TIMESTAMP are related. This section describes their features, similarities, and differences.
DATE
The DATE type is used to store values that have a date part but no time part.
DATE values are retrieved and displayed in the 'YYYY-MM-DD' format, with a range from '0000-00-00' to '9999-12-31'.
seekdb allows you to assign values to DATE columns by using strings or numbers.
Syntax:
DATE
DATETIME
The DATETIME type is used to store values that have both a date and a time part.
DATETIME values are retrieved and displayed in the 'YYYY-MM-DD hh:mm:ss' format, with a range from '0000-00-00 00:00:00' to '9999-12-31 23:59:59'.
Syntax:
DATETIME[(fsp)]
The optional fsp value specifies the precision of the fractional seconds, with a range of [0,6]. A value of 0 indicates no fractional part. If omitted, the default precision is 0.
DATETIME columns can be automatically initialized and updated to the current date and time by using the DEFAULT and ON UPDATE clauses.
DATETIME values can include a fractional part of seconds, with a precision of up to microseconds (6 digits), in the format 'YYYY-MM-DD hh:mm:ss[.fraction]', with a range 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 components 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 value specifies the precision of the fractional seconds, with a range of [0,6]. A value of 0 indicates no fractional part. If omitted, the default precision is 0.
Example:
CREATE TABLE t(C1 TIMESTAMP(6));
TIMESTAMP columns can be automatically initialized and updated to the current date and time by using the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. By default, the first TIMESTAMP column has these attributes, and you can also define other TIMESTAMP columns in the table to have these attributes.
TIMESTAMP values can include a fractional part of seconds, with a precision of up to microseconds (6 digits), in the format 'YYYY-MM-DD hh:mm:ss[.fraction]', with a range 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 components by a decimal point; other separators are not recognized.
seekdb 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 zone, but you can also change the time zone of each connection. If the same time zone is not used for both conversions, 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.