Date & Time

Date and Time Data Types

Name	Aliases	Storage Size	Resolution	Min Value	Max Value
DATE		4 bytes	day	1000-01-01	9999-12-31
TIMESTAMP	DATETIME	8 bytes	microsecond	1000-01-01 00:00:00	9999-12-31 23:59:59.999999 UTC

Functions

See Date & Time Functions.

Example

CREATE TABLE test_dt
  (
     date DATE,
     ts   TIMESTAMP
  );

DESC test_dt;
+-------+--------------+------+---------+-------+
| Field | Type         | Null | Default | Extra |
+-------+--------------+------+---------+-------+
| date  | DATE         | NO   | 0       |       |
| ts    | TIMESTAMP    | NO   | 0       |       |
+-------+--------------+------+---------+-------+

-- A TIMESTAMP value can optionally include a trailing fractional seconds part in up to microseconds (6 digits) precision.

INSERT INTO test_dt
VALUES      ('2022-04-07',
             '2022-04-07 01:01:01.123456'),
            ('2022-04-08',
             '2022-04-08 01:01:01');

SELECT *
FROM   test_dt;
+------------+----------------------------+
| date       | ts                         |
+------------+----------------------------+
| 2022-04-07 | 2022-04-07 01:01:01.123456 |
| 2022-04-08 | 2022-04-08 01:01:01.000000 |
+------------+----------------------------+

-- Databend recognizes TIMESTAMP values in several formats.

CREATE TABLE test_formats
  (
     id INT,
     a  TIMESTAMP
  );

INSERT INTO test_formats
VALUES      (1,
             '2022-01-01 02:00:11'),
            (2,
             '2022-01-02T02:00:22'),
            (3,
             '2022-02-02T04:00:03+00:00'),
            (4,
             '2022-02-03');

SELECT *
FROM   test_formats;

 ----
 1  2022-01-01 02:00:11.000000
 2  2022-01-02 02:00:22.000000
 3  2022-02-02 04:00:03.000000
 4  2022-02-03 00:00:00.000000

-- Databend automatically adjusts and shows TIMESTAMP values based on your current timezone.

CREATE TABLE test_tz
  (
     id INT,
     t  TIMESTAMP
  );

SET timezone='UTC';

INSERT INTO test_tz
VALUES      (1,
             '2022-02-03T03:00:00'),
            (2,
             '2022-02-03T03:00:00+08:00'),
            (3,
             '2022-02-03T03:00:00-08:00'),
            (4,
             '2022-02-03'),
            (5,
             '2022-02-03T03:00:00+09:00'),
            (6,
             '2022-02-03T03:00:00+06:00');

SELECT *
FROM   test_tz;

 ----
 1  2022-02-03 03:00:00.000000
 2  2022-02-02 19:00:00.000000
 3  2022-02-03 11:00:00.000000
 4  2022-02-03 00:00:00.000000
 5  2022-02-02 18:00:00.000000
 6  2022-02-02 21:00:00.000000

SET timezone='Asia/Shanghai';

SELECT *
FROM   test_tz;

 ----
 1  2022-02-03 11:00:00.000000
 2  2022-02-03 03:00:00.000000
 3  2022-02-03 19:00:00.000000
 4  2022-02-03 08:00:00.000000
 5  2022-02-03 02:00:00.000000
 6  2022-02-03 05:00:00.000000