DATETIME

SinceVersion 1.2.0

DATETIME

Description

DATETIME([P]) Date and time type. The optional parameter P indicates the time precision and the value range is [0, 6], that is, it supports up to 6 decimal places (microseconds). 0 when not set. Value range is [‘0000-01-01 00:00:00[.000000]‘,’9999-12-31 23:59:59[.999999]‘]. The form of printing is ‘yyyy-MM-dd HH:mm:ss.SSSSSS’

note

  1. DATETIME supports temporal precision up to microseconds. When parsing imported DATETIME type data using the BE side (e.g. using Stream load, Spark load, etc.), or using the FE side with the [Nereids](../../../query-acceleration/nereids) on, decimals exceeding the current precision will be **rounded**.
  2. DATETIME reads support resolving the time zone in the format of the original DATETIME literal followed by the time zone:
  1. <date> <time>[<timezone>]
  1. For the specific supported formats for `<timezone>`, see [timezone](../../../advanced/time-zone). Note that the `DATE`, `DATEV2`, `DATETIME`, and `DATETIMEV2` types **don't** contain time zone information. For example, if an input time string "2012-12-12 08:00:00+08:00" is parsed and converted to the current time zone "+02:00", and the actual value "2012-12-12 02:00:00" is stored in the DATETIME column, the value itself will not change, no matter how much the cluster environment variables are changed.

example

  1. mysql> select @@time_zone;
  2. +----------------+
  3. | @@time_zone    |
  4. +----------------+
  5. | Asia/Hong_Kong |
  6. +----------------+
  7. 1 row in set (0.11 sec)
  9. mysql> insert into dtv23 values ("2020-12-12 12:12:12Z"), ("2020-12-12 12:12:12GMT"), ("2020-12-12 12:12:12+02:00"), ("2020-12-12 12:12:12America/Los_Angeles");
  10. Query OK, 4 rows affected (0.17 sec)
  12. mysql> select * from dtv23;
  13. +-------------------------+
  14. | k0                      |
  15. +-------------------------+
  16. | 2020-12-12 20:12:12.000 |
  17. | 2020-12-12 20:12:12.000 |
  18. | 2020-12-13 04:12:12.000 |
  19. | 2020-12-12 18:12:12.000 |
  20. +-------------------------+
  21. 4 rows in set (0.15 sec)