Types for date and time

Name Format Description

DATE

YYYYYY-MM-DD

Dates

TIME

[YYYYY-MM-DD ]HH:MM[:SS][.MS]

Time

TIMESTAMP

YYYYYY-MM-DD hh:mm[:ss][.zzzzzzzz].

Time moments

TIMESTAMPTZ.

YYYYYY-MM-DD hh:mm[:ss][.zzzzzzzz][+-TT[:tt] | ZONE]

Time moments with time zone

Dates

  • DATE

The DATE type is used to store a date (a combination of year, month and day). Dates are counted according to the Gregorian calendar, even for times before its introduction.

Data for this type must be formatted according to the standard ISO 8601:
YYYYYY-MM-DD.

Time

  • TIME

The TIME type is used to store time (hours, minutes, seconds and microseconds within a day).

Data for this type must be formatted according to the standard ISO 8601:
[YYYYY-MM-DD ]HH:MM:SS[.MS].

The TIME type should only be used in rare cases where the date portion of the timestamp may be ignored. In most cases, the type should be used to represent specific points in time TIMESTAMP.

Moments in time

  • TIMESTAMP

  • TIMESTAMPTZ

The TIMESTAMP and TIMESTAMPTZ types are used to store timestamps — specific moments in time. They combine date (type DATE) and time (type TIME) information.

For the TIMESTAMP type, the data must be formatted according to the ISO 8601 standard:
YYYYYY-MM-DD hh:mm:ss[.zzzzzzzz].

For the TIMESTAMPTZ (TIMESTAMP TIME ZONE) type, the data shall be formatted in the same way, but it is allowed to add a time zone:
YYYYYY-MM-DD hh:mm:ss[.zzzzzzzz][+-TT[:tt] | ZONE].

Decimal places beyond the supported precision are ignored and do not cause an error.

Time zones can be specified either via an indentation in hours from the specified time (+01, -01) or by explicitly specifying an identifier from list of time zones tz_database.

Examples

Let’s create a table dates with columns of types DATE, TIME and TIMESTAMP and write different valid values into them:

CREATE TABLE dates(
    date DATE,
    time TIME,
    timestamp TIMESTAMP);

INSERT INTO dates VALUES
    ('2025-01-02', '1:1', '2025-01-02 1:1:00'),
    ('2025-01-02', '1:01:11', '2025-01-02 1:1:11.111'),
    ('2025-01-02', '01:01:11.1234567', '2025-01-02 1:1:11.1234567');

SELECT * FROM dates;
+------------+-----------------+----------------------------+
|    date    |       time      |          timestamp         |
+------------+-----------------+----------------------------+
| 2025-01-02 | 01:01:00        | 2025-01-02 01:01:00        |
+------------+-----------------+----------------------------+
| 2025-01-02 | 01:01:11        | 2025-01-02 01:01:11.111000 |
+------------+-----------------+----------------------------+
| 2025-01-02 | 01:01:11.123456 | 2025-01-02 01:01:11.123456 |
+------------+-----------------+----------------------------+

Note that in the programme output, the time column shows the time in the standard form and with the mandatory value of seconds (despite the different input methods). In the timestamp column, the time is also displayed in standard form. Microseconds are only displayed if they have been set and with an accuracy of 6 digits.

Let’s create a table time_stamps with columns of types TIMESTAMP and TIMESTAMPTZ and write in them the same pairs of values — with and without time zone:

CREATE TABLE time_stamps(
    timestamp TIMESTAMP,
    timestamptz TIMESTAMPTZ);

INSERT INTO time_stamps VALUES
    ('2025-01-02 0:0:0',       '2025-01-02 0:0:0'),
    ('2025-01-02 0:0:0+01:00', '2025-01-02 0:0:0+01:00');

SELECT * FROM time_stamps;
+---------------------+---------------------------+
|      timestamp      |        timestamptz        |
+---------------------+---------------------------+
| 2025-01-02 00:00:00 | 2025-01-02 00:00:00+00:00 |
+---------------------+---------------------------+
| 2025-01-01 23:00:00 | 2025-01-01 23:00:00+00:00 |
+---------------------+---------------------------+

Note that in both cases the time zone indication is handled correctly, but in the case of TIMESTAMPTZ it is stored in the table, while in the case of TIMESTAMP type it is not.

To demonstrate working with time zones, let’s insert into the time_stamps table the time values with different time zone indications. For clarity, we will insert the same values in two columns with types VARCHAR and TIMESTAMPTZ:

CREATE TABLE time_stamps(
    timestampt_text VARCHAR,
    timestamptz TIMESTAMPTZ);

INSERT INTO time_stamps VALUES
    ('2025-01-01 0:0:0 UTC', '2025-01-01 0:0:0 UTC'),
    ('2025-01-01 0:0:0 CET', '2025-01-01 0:0:0 CET'),
    ('2025-01-01 0:0:0+01' , '2025-01-01 0:0:0+01' ),
    ('2025-01-01 0:0:0-01' , '2025-01-01 0:0:0-01' ),
    ('2025-01-01 0:0:0+25' , '2025-01-01 0:0:0+25' ),
    ('2025-01-01 0:0:0-25' , '2025-01-01 0:0:0-25' );

SELECT * FROM time_stamps;
+----------------------+---------------------------+
|    timestampt_text   |        timestamptz        |
+----------------------+---------------------------+
| 2025-01-01 0:0:0 UTC | 2025-01-01 00:00:00+00:00 |
+----------------------+---------------------------+
| 2025-01-01 0:0:0 CET | 2024-12-31 23:00:00+00:00 |
+----------------------+---------------------------+
| 2025-01-01 0:0:0+01  | 2024-12-31 23:00:00+00:00 |
+----------------------+---------------------------+
| 2025-01-01 0:0:0-01  | 2025-01-01 01:00:00+00:00 |
+----------------------+---------------------------+
| 2025-01-01 0:0:0+25  | 2024-12-30 23:00:00+00:00 |
+----------------------+---------------------------+
| 2025-01-01 0:0:0-25  | 2025-01-02 01:00:00+00:00 |
+----------------------+---------------------------+

Let’s try to enter invalid data into the dates table:

CREATE TABLE dates(
    date DATE);

INSERT INTO dates VALUES
    ('2025-12-13'),
    ('2025-13-13');
ERROR: ConversionException: Conversion Error: date field value out of range:
"2025-13-13"

The data cannot be entered because an invalid month number — 13 — is specified.

CREATE TABLE dates(
    time TIME);

INSERT INTO dates VALUES
    ('1:1'),
    ('24:1');
ERROR: ConversionException: Conversion Error: time field value out of range:
"24:1", expected format is ([YYYY-MM-DD ]HH:MM:SS[.MS])

Data cannot be entered because a invalid hour value — 24 — is specified.

CREATE TABLE dates(
    timestamp TIMESTAMP);

INSERT INTO dates VALUES
    ('2025-01-01 1');
ERROR: ConversionException: Conversion Error: invalid timestamp field format:
"2025-01-01 1", expected format is (YYYY-MM-DD HH:MM:SS[.US][±HH:MM| ZONE])

Data cannot be entered because a non-valid time value — 1 — is set.

CREATE TABLE dates(
    timestamptz TIMESTAMPTZ);

INSERT INTO dates VALUES
    ('2025-01-01 0:0:0 Europe/Moscow'),
    ('2025-01-01 0:0:0 Asia/Srednekolymsk'),
    ('2025-01-01 0:0:0 MSK');
ERROR: NotImplementedException: Not implemented Error: Unknown TimeZone 'MSK'

The data cannot be entered because a invalid time zone value MSK is set. The values Europe/Moscow and Asia/Srednekolymsk are valid because they are included in list of time zone identifiers tz_database.

See also