SingleStore Managed Service

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

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.

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

INSERT INTO s(id) VALUES(2);

SELECT * FROM s;
****
+------+---------------------+
| id   | ts                  |
+------+---------------------+
|    1 | 2020-05-21 09:12:25 |
|    2 | 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, SingleStore DB 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 DB 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).