# Time and Date | Data Type | Size | Size (Not Null) | Resolution | Value | Precision | | ------------ | -------- | --------------- | ----------- | ---------------------------------------------------------------------------------------------------------- | -------------------------- | | DATE | 8 bytes | 4 bytes | day | **Zero**: 0000-00-00**Min**: 1000-01-01**Max**: 9999-12-31 | yyyy-mm-dd | | TIME | 8 bytes | 4 bytes | second | **Zero**: 00:00:00**Min**: -838:59:59**Max**: 838:59:59 | hhh:mm:ss | | TIME(6) | 8 bytes | 4 bytes | microsecond | **Zero**: 00:00:00.000000**Min**: -838:59:59.000000**Max**: 838:59:59.000000 | hhh:mm:ss.mmmmmm | | DATETIME | 12 bytes | 8 bytes | second | **Zero**: 0000-00-00 00:00:00**Min**: 1000-01-01 00:00:00**Max**: 9999-12-31 23:59:59 | yyyy-mm-dd hh:mm:ss | | DATETIME(6) | 12 bytes | 8 bytes | microsecond | **Zero**: 0000-00-00 00:00:00.000000**Min**: 1000-01-01 00:00:00.000000**Max**: 9999-12-31 23:59:59.999999 | yyyy-mm-dd hh:mm:ss.mmmmmm | | TIMESTAMP | 4 bytes | 4 bytes | second | **Zero**: 0000-00-00 00:00:00**Min**: 1970-01-01 00:00:01**Max**: 2038-01-19 03:14:07 | yyyy-mm-dd hh:mm:ss | | TIMESTAMP(6) | 8 bytes | 8 bytes | microsecond | **Zero**: 0000-00-00 00:00:00.000000**Min**: 1970-01-01 00:00:01.000000**Max**: 2038-01-19 03:14:07.999999 | yyyy-mm-dd hh:mm:ss.mmmmmm | | YEAR | 2 bytes | 1 byte | year | **Zero**: 0000**Min**: 1901**Max**: 2155 | 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/date-and-time-functions.md), 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 `, to insert a column with the current timestamp. * `ON UPDATE `, to update a column with the current timestamp. * `DEFAULT ON UPDATE `, to insert and update a column with the current timestamp. where `` 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; ``` ```output +------+---------------------+ | 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; ``` ```output +------+----------------------------+ | id | ts | +------+----------------------------+ | 1 | 2017-07-01 00:00:00.000000 | +------+----------------------------+ ``` ```sql UPDATE s1 SET id = 2 WHERE id = 1; ``` ```sql SELECT * FROM s1; ``` ```output +------+----------------------------+ | 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; ``` ```output +------+----------------------------+ | 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; ``` ```output +------+----------------------------+ | 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; > > ``````output > > +----------------------------+----------------------------+------+ > | 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; > > ``````output > > +----------------------------+----------------------------+------+ > | d | ts | id | > +----------------------------+----------------------------+------+ > | 2017-07-01 00:00:00.000000 | NULL | 2 | > +----------------------------+----------------------------+------+ > > ``` ## `explicit_defaults_for_timestamp` The `explicit_defaults_for_timestamp` engine variable 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 `DEFAULT`“0000-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](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/setting-the-time-zone.md) 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; ``` ```output +----+---------------------+ | 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; ``` ```output +----+---------------------+ | 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; ``` ```output +---------------------------------------+ | @@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'); ``` ```output 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); ``` ```output ERROR 1264 (22003): Leaf Error (127.0.0.1:3306): Out of range value for column 'zy' ``` ```sql SELECT @@data_conversion_compatibility_level; ``` ```output +---------------------------------------+ | @@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; ``` ```output +------+------+ | id | zy | +------+------+ | 2 | 0000 | +------+------+ 1 row in set (0.321 sec) ``` *** Modified at: December 18, 2025 Source: [/db/v9.1/reference/sql-reference/data-types/time-and-date/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/time-and-date/) (An index of the documentation is available at /llms.txt)