# Temporal Data Types

+ **`DATE`** example: `'2018-01-04'` with format **`YYYY-MM-DD`** 
+ **`TIME`** example: `'12:59:02'` with format **`HH:MM:SS`** 
+ **`DATETIME`** example: `'2018-01-04 12:59:02'` with format **`YYYY-MM-DD HH:MM:SS`** 
+ **`TIMESTAMP`** example: `'2018-01-04 12:59:02'` with format **`YYYY-MM-DD HH:MM:SS`** 
+ **`YEAR`** example: `1548` with format **`YYYY`** 
+ **`TIME`**, **`DATETIME`** and **`TIMESTAMP`**  accept microseconds with up to 6 digits in the format **`HH:MM:SS[.fraction]`** 
+ **`STRICT_TARNS_TABLE`** `SQL` mode will affect TiDB's acceptance of zero date values
```
tidb> SET SQL_MODE='';
tidb> INSERT INTO test.dropme (day) VALUES (~!@'0000 00-00-00'@!~);
Query OK, 1 row affected, ~!@1 warning@!~ (0.02 sec)

tidb> SET SQL_MODE='STRICT_TRANS_TABLES';
tidb> INSERT INTO test.dropme (day) VALUES (~!@'0000 00-00-00'@!~);
~!@ERROR 1292 (22007): Incorrect datetime value@!~: '0000 00-00-00' for column 'day' at row 1
```

------------------------------------------------------------
Note that the TIMESTAMP data type is affected by the 2038 issue. If the stored value is greater than 2038, the DATETIME type is recommended.

SQL strict mode:

```
tidb> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

tidb> insert into test.dropme (day) values ('0000 00-00-00');
Query OK, 1 row affected, 1 warning (0.02 sec)

tidb> set sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

tidb> insert into test.dropme (day) values ('0000 00-00-00');
ERROR 1292 (22007): Incorrect datetime value: '0000 00-00-00' for column 'day' at row 1
```
- TiDB supports all MySQL date and time types, including DATE, TIME, DATETIME, TIMESTAMP, and YEAR.

- For date and time value types, note:
  - The date part must be in the format "year-month-day" (for example, 1998-09-04), not "month-day-year" or "day-month-year".
  - If the year part of a date is 2 digits, TiDB converts it according to the specific rule that the year is two digits.
  - If the format must be a numeric type, TiDB automatically converts date or time values to numeric types.

- DATE type
  - The DATE type contains only the date part, not the time part. The DATE type has the format YYYY-MM-DD and the supported range is 0000-01-01 to 9999-12-31.
`DATE` 

- TIME type
  - The TIME type is HH:MM:SS [.fraction], and the supported range is - 838:59:59 .000000 to 838:59:59 .000000. TIME can be used not only to indicate the time of day, but also to refer to the time interval between two events. The fsp parameter indicates the precision of seconds. The range is 0 to 6, and the default value is 0.
`TIME[(fsp)]` 
- Note:
  - Note the abbreviated form of TIME. For example, 11:12 means 11:12:00 instead of 00:11:12. But 112 means 00:11:12. These differences depend on the presence or absence of characters.

- DATETIME type
  - The DATETIME type is a combination of date and time in the format YYYY-MM-DD HH:MM:SS [.fraction]. The supported range is 0000-01-01 00:00:00 .000000 to 9999-12-31 23:59:59 .999999. The fsp parameter indicates the precision of seconds. The range is 0 to 6, and the default value is 0. TiDB supports string or number conversion to DATETIME type.
`DATETIME[(fsp)]` 

- TIMESTAMP type
  - The TIMESTAMP type is a combination of date and time. The supported range is from 1970-01-01 00:00:01 .000000 to 2038-01-19 03:14:07 .999999. The fsp parameter indicates the precision of seconds. The range is 0 to 6, and the default value is 0. In TIMESTAMP, zeros are not allowed in the month part or date part, with the only exception being the zero value itself 0000-00-00 00:00:00.
`TIMESTAMP[(fsp)]`