Time and Date

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_TIMESTAMPto explicitly convert a string literal to a time value or a date value.

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:

where <timefunction> is one of the following values: NOW(), NOW(6), CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP(6).

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

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

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

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

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

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:

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

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