Time and Date
Data Type | Size Size (Not Null) | Resolution | Min Value | Max Value | Zero Value | Precision |
---|---|---|---|---|---|---|
DATE | 8 bytes 4 bytes | day | 1000-01-01 | 9999-12-31 | 0000-00-00 | yyyy-mm-dd |
TIME | 8 bytes 4 bytes | second | -838:59:59 | 838:59:59 | 00:00:00 | hhh:mm:ss |
TIME(6) | 8 bytes 4 bytes | microsecond | -838:59:59.000000 | 839:59:59.999999 | 00:00:00.000000 | hhh:mm:ss.mmmmmm |
DATETIME | 12 bytes 8 bytes | second | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 | 0000-00-00 00:00:00 | yyyy-mm-dd hh:mm:ss |
DATETIME(6) | 12 bytes 8 bytes | microsecond | 1000-01-01 00:00:00.000000 | 9999-12-31 23:59:59.999999 | 0000-00-00 00:00:00.000000 | yyyy-mm-dd hh:mm:ss.mmmmmm |
TIMESTAMP | 4 bytes 4 bytes | second | 1970-01-01 00:00:01 | 2038-01-19 03:14:07 | 0000-00-00 00:00:00 | hh:mm:ss |
TIMESTAMP(6) | 8 bytes 8 bytes | microsecond | 1970-01-01 00:00:01.000000 | 2038-01-19 03:14:07.999999 | 0000-00-00 00:00:00.000000 | hh:mm:ss.mmmmmm |
YEAR | 2 bytes 1 byte | year | 1901 | 2155 | 0000 | yyyy |
Any data being ingested that does not match the precision of the destination datatype will be converted. For example, a value like 2020-12-31 23:59:59
being inserted into a column with the datatype of DATE
would be converted to 2020-12-31
.
Note: It is recommended to use DATETIME
or DATETIME(6)
column types instead of TIMESTAMP
or TIMESTAMP(6)
because TIMESTAMP
types only support dates through 2038-01-19
as opposed to 9999-12-31
for DATETIME
types. This can avoid the need to do potentially time-consuming application and database maintenance and conversion in the future.
Storing Times and Dates
You can use a string literal to store a time value or a date value. When you do this, the literal is implicitly converted to the time or date. For example, if the table t
contains a column dt
of type DATETIME
, you can use the following statement to store the literal.
INSERT INTO t(dt) VALUES ('2020-11-21');
Alternatively, you can use the TO_
Date and Time Functions, such as TO_DATE
and TO_TIMESTAMP
to explicitly convert a string literal to a time value or a date value.
Inserting or Updating a Column With the Current Timestamp
To allow inserts or updates of a TIMESTAMP
, TIMESTAMP(6)
, DATETIME
, or a DATETIME(6)
column with the current timestamp, define the column with the clause:
DEFAULT <timefunction>
, to insert a column with the current timestamp.ON UPDATE <timefunction>
, to update a column with the current timestamp.DEFAULT <timefunction> ON UPDATE <timefunction>
, to insert and update a column with the current timestamp.
where <timefunction>
is one of the following values: NOW()
, NOW(6)
, CURRENT_TIMESTAMP()
, CURRENT_TIMESTAMP(6)
.
Examples
The following example demonstrates the use of DEFAULT CURRENT_TIMESTAMP()
in column definition:
CREATE TABLE s(id INT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP()); INSERT INTO s(id) VALUES(1);
SELECT * FROM s; **** +------+---------------------+ | id | ts | +------+---------------------+ | 1 | 2020-05-21 09:12:25 | +------+---------------------+
The following example demonstrates the use of ON UPDATE CURRENT_TIMESTAMP(6)
in column definition:
CREATE TABLE s1(id INT, ts DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6)); INSERT INTO s1(id,ts) VALUES(1,"2017-07-01");
SELECT * FROM s1; **** +------+----------------------------+ | id | ts | +------+----------------------------+ | 1 | 2017-07-01 00:00:00.000000 | +------+----------------------------+
UPDATE s1 SET id = 2 WHERE id = 1;
SELECT * FROM s1; **** +------+----------------------------+ | id | ts | +------+----------------------------+ | 2 | 2020-05-21 09:36:47.124979 | +------+----------------------------+
The following example demonstrates the use of DEFAULT NOW(6) ON UPDATE NOW(6)
in column definition:
CREATE TABLE s2(id INT, ts DATETIME(6) DEFAULT NOW(6) ON UPDATE NOW(6)); INSERT INTO s2(id) VALUES(1); INSERT INTO s2(id) VALUES(2);
SELECT * FROM s2; **** +------+----------------------------+ | id | ts | +------+----------------------------+ | 2 | 2020-05-21 11:21:30.998275 | | 1 | 2020-05-21 11:21:30.992868 | +------+----------------------------+
UPDATE s2 SET id = 2 WHERE id = 1;
SELECT * FROM s2; **** +------+----------------------------+ | id | ts | +------+----------------------------+ | 2 | 2020-05-21 11:21:30.998275 | | 2 | 2020-05-21 11:23:39.134793 | +------+----------------------------+
Warning
Prior to SingleStore 7, when you inserted or updated a record, the first TIMESTAMP
or TIMESTAMP(6)
column would automatically be populated with the current timestamp. Such columns did not use DEFAULT CURRENT_TIMESTAMP()
or ON UPDATE CURRENT_TIMESTAMP()
clause in their column definitions. In SingleStore 7 and later, this behavior has been deprecated; TIMESTAMP
or TIMESTAMP(6)
columns without these clauses will be populated with a NULL
value instead of the current timestamp. For instance, NULL
values are populated in the ts
column in the following example, when run on SingleStore 7 or later.
CREATE TABLE r(d DATETIME(6), ts TIMESTAMP(6), id INT); INSERT r(d, id) VALUES("2017-07-01", 1);
SELECT * FROM r; **** +----------------------------+----------------------------+------+ | d | ts | id | +----------------------------+----------------------------+------+ | 2017-07-01 00:00:00.000000 | NULL | 1 | +----------------------------+----------------------------+------+
UPDATE r SET id = 2 WHERE id = 1;
SELECT * FROM r; **** +----------------------------+----------------------------+------+ | d | ts | id | +----------------------------+----------------------------+------+ | 2017-07-01 00:00:00.000000 | NULL | 2 | +----------------------------+----------------------------+------+
The explicit_defaults_for_timestamp Variable
The variable explicit_defaults_for_timestamp
determines if certain nonstandard behaviors for default values and NULL-value are disabled for the TIMESTAMP
column. By default, the variable is enabled, which disables the nonstandard behaviors.
If the explicit_defaults_for_timestamp
variable is enabled, SingleStoreDB handles the TIMESTAMP
column behavior as follows:
Assigning a
TIMESTAMP
column value ofNULL
does not set it to the current timestamp. To set to the current timestamp, set theTIMESTAMP
column toCURRENT_TIMESTAMP()
orNOW()
.If the
TIMESTAMP
column is not declared asNOT NULL
, then it is automatically declared with theNULL
attribute and permitsNULL
values. Assigning a value ofNULL
to the column sets it toNULL
, not the current timestamp.TIMESTAMP
column declared with theNOT NULL
attribute do not permitNULL
values.TIMESTAMP
columns explicitly declared with theNOT NULL
attribute and without an explicitDEFAULT
attribute are treated as having no default value.To declare
TIMESTAMP
column with theDEFAULT CURRENT_TIMESTAMP()
orON UPDATE CURRENT_TIMESTAMP()
attributes, these must be explicitly specified.The first
TIMESTAMP
column in a table is handled in the same way as theTIMESTAMP
columns following the first one.
If the explicit_defaults_for_timestamp
variable is disabled, SingleStoreDB handles the TIMESTAMP
column behavior as follows:
If the
TIMESTAMP
column is not declared asNULL
, then it is automatically declared with theNOT NULL
attribute. Assigning a value ofNULL
to the column is permitted and sets the column to the current timestamp.The first
TIMESTAMP
column in a table, if not declared asNULL
,DEFAULT CURRENT_TIMESTAMP()
orON UPDATE CURRENT_TIMESTAMP()
, is automatically declared with theDEFAULT CURRENT_TIMESTAMP()
andON UPDATE CURRENT_TIMESTAMP()
attributes.TIMESTAMP
columns following the first one, if not declared with theNULL
attribute or aDEFAULT
attribute, are automatically declared asDEFAULT
“0000-00-00 00:00:00” (the “zero” timestamp).
Handling of Time Zones
SingleStoreDB currently ignores any time zone offsets on query writes and reads for DATETIME
and TIMESTAMP
data types. The time zone defaults to UTC. If you choose to change the time zone for your cluster, please follow the instructions in the Setting the Time Zone section.
See the following examples for DATETIME
and TIMESTAMP
respectively:
Note
We recommend that you keep your system_time_zone
at UTC and then have your application convert from UTC to the user’s local time zone.
CREATE TABLE dt (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, col DATETIME NOT NULL ); INSERT INTO dt (col) VALUES ('2022-01-01 10:10:10'), ('2022-01-01 10:10:10+05:30'), ('2022-01-01 10:10:10-08:00');
SELECT * FROM dt; **** +----+---------------------+ | id | col | +----+---------------------+ | 1 | 2022-01-01 10:10:10 | | 2 | 2022-01-01 10:10:10 | | 3 | 2022-01-01 10:10:10 | +----+---------------------+
CREATE TABLE ts (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, col TIMESTAMP NOT NULL ); INSERT INTO ts (col) VALUES ('2022-01-01 10:10:10'), ('2022-01-01 10:10:10+05:30'), ('2022-01-01 10:10:10-08:00');
SELECT * FROM ts; **** +----+---------------------+ | id | col | +----+---------------------+ | 1 | 2022-01-01 10:10:10 | | 2 | 2022-01-01 10:10:10 | | 3 | 2022-01-01 10:10:10 | +----+---------------------+
Note
Time zone offsets are also ignored when data is ingested via LOAD DATA
and or pipelines.
If the data_conversion_compatibility_level
is 7.5
(or lower) and if any extra characters are included in the query (including a timezone), an error will be generated.
SELECT @@data_conversion_compatibility_level; **** +---------------------------------------+ | @@data_conversion_compatibility_level | +---------------------------------------+ | 7.5 | +---------------------------------------+
CREATE TABLE dt1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, col DATETIME NOT NULL); INSERT INTO dt1 (col) VALUES ('2022-01-01 10:10:10'), ('2022-01-01 10:10:10+05:30'), ('2022-01-01 10:10:10-08:00'); ERROR 2363 (HY000): Leaf Error (127.0.0.1:3307): Invalid DATE/TIME in type conversion