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	838:59:59.000000	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	yyyy-mm-dd 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	yyyy-mm-dd 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 data type will be converted. For example, a value like 2020-12-31 23:59:59 being inserted into a column with the data type of DATE would be converted to 2020-12-31.

Note: SingleStore recommends using 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.

SQL

INSERT INTO t(dt) VALUES ('2020-11-21');

Alternatively, you can use the TO_Date and Time Functions, such as TO_DATE and TO_TIMESTAMPto 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:

SQL

CREATE TABLE s(id INT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP());
INSERT INTO s(id) VALUES(1);

SQL

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:

SQL

CREATE TABLE s1(id INT, ts DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6));
INSERT INTO s1(id,ts) VALUES(1,"2017-07-01");

SQL

SELECT * FROM s1;

+------+----------------------------+
| id   | ts                         |
+------+----------------------------+
|    1 | 2017-07-01 00:00:00.000000 |
+------+----------------------------+

SQL

UPDATE s1 SET id = 2 WHERE id = 1;

SQL

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:

SQL

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);

SQL

SELECT * FROM s2;

+------+----------------------------+
| id   | ts                         |
+------+----------------------------+
|    2 | 2020-05-21 11:21:30.998275 |
|    1 | 2020-05-21 11:21:30.992868 |
+------+----------------------------+

SQL

UPDATE s2 SET id = 2 WHERE id = 1;

SQL

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.

SQL

CREATE TABLE r(d DATETIME(6), ts TIMESTAMP(6), id INT);
INSERT r(d, id) VALUES("2017-07-01", 1);

SQL

SELECT * FROM r;

+----------------------------+----------------------------+------+
| d                          | ts                         | id   |
+----------------------------+----------------------------+------+
| 2017-07-01 00:00:00.000000 | NULL                       |    1 |
+----------------------------+----------------------------+------+

SQL

UPDATE r SET id = 2 WHERE id = 1;

SQL

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, SingleStore handles the TIMESTAMP column behavior as follows:

Assigning a TIMESTAMP column value of NULL does not set it to the current timestamp. To set to the current timestamp, set the TIMESTAMP column to CURRENT_TIMESTAMP() or NOW().
If the TIMESTAMP column is not declared as NOT NULL, then it is automatically declared with the NULL attribute and permits NULL values. Assigning a value of NULL to the column sets it to NULL, not the current timestamp.
TIMESTAMP column declared with the NOT NULL attribute do not permit NULL values.
TIMESTAMP columns explicitly declared with the NOT NULL attribute and without an explicit DEFAULT attribute are treated as having no default value.
To declare TIMESTAMP column with the DEFAULT CURRENT_TIMESTAMP() or ON UPDATE CURRENT_TIMESTAMP() attributes, these must be explicitly specified.
The first TIMESTAMP column in a table is handled in the same way as the TIMESTAMP columns following the first one.

If the explicit_defaults_for_timestamp variable is disabled, SingleStore handles the TIMESTAMP column behavior as follows:

If the TIMESTAMP column is not declared as NULL, then it is automatically declared with the NOT NULL attribute. Assigning a value of NULL to the column is permitted and sets the column to the current timestamp.
The first TIMESTAMP column in a table, if not declared as NULL, DEFAULT CURRENT_TIMESTAMP() or ON UPDATE CURRENT_TIMESTAMP(), is automatically declared with the DEFAULT CURRENT_TIMESTAMP() and ON UPDATE CURRENT_TIMESTAMP() attributes.
TIMESTAMP columns following the first one, if not declared with the NULL attribute or a DEFAULT attribute, are automatically declared as DEFAULT0000-00-00 00:00:00 (the zero timestamp).

Handling of Time Zones

SingleStore 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

SingleStore recommends that you keep your system_time_zone at UTC and then have your application convert from UTC to the user’s local time zone.

SQL

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');

SQL

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 |
+----+---------------------+

SQL

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');

SQL

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.0 or higher and if any extra characters are included in the query (including a timezone), an error will be generated.

SQL

SELECT @@data_conversion_compatibility_level;

+---------------------------------------+
| @@data_conversion_compatibility_level |
+---------------------------------------+
| 7.5                                   |
+---------------------------------------+

SQL

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

Zero Value Time and Date

The ability to enter a zero value into a time or date column (for example, 0000 for a YEAR column) is controlled by the data_conversion_compatibility_level.

SQL

CREATE TABLE ztest(id int, zy year);

SQL

INSERT INTO ztest VALUES(2, 0000);

ERROR 1264 (22003): Leaf Error (127.0.0.1:3306): Out of range value for column 'zy'

SQL

SELECT @@data_conversion_compatibility_level;

+---------------------------------------+
| @@data_conversion_compatibility_level |
+---------------------------------------+
| 8.0                                   |
+---------------------------------------+

Entering a zero value for year is restricted via data_conversion_compatibility_level. Set the value to a lower version in order to insert zero values:

SQL

SET GLOBAL data_conversion_compatibility_level = '6.0';

INSERT INTO ztest VALUES(2, 0000);

SELECT * FROM ztest;

+------+------+
| id   | zy   |
+------+------+
|    2 | 0000 |
+------+------+
1 row in set (0.321 sec)