Date and Time Types

Introduction

Next, we will explore Date and Time Types as follows:

DATE
- Only stores date, month and year. Completely ignores time or time zone. Used for birth dates, founding dates, holidays, etc.
- The INSERT method uses the standard ISO YYYY-MM-DD string format.
TIME (TIME without time zone)
- Only stores hours, minutes and seconds in a day without date and time zone.
- Suitable for store opening/closing hours, alarm times, etc.
TIMETZ (TIME with time zone)
- Stores time of day with a time zone offset.
- This data type is rarely used in practice because it only stores time and time zone without date information, making application scenarios scarce.
TIMESTAMP (TIMESTAMP without time zone)
- Stores date, month, year, hours, minutes, seconds and fractional seconds (up to 6 decimal places).
- Note that it DOES NOT store time zone information. If you input 2026-07-28 10:00:00+07, Postgres will ignore +07 and store only 2026-07-28 10:00:00. When retrieving data, regardless of where the Database is located globally, it retains 10:00:00.
TIMESTAMPTZ (TIMESTAMP with time zone)
- Used to store date and time along with a time zone, recommended in most cases because it helps data remain absolutely consistent globally.
- No matter which country users insert data from or at what time, the event occurrence time stays accurately synchronized.
- In reality, Postgres does not store the time zone with the row data, but converts it to standard UTC (Coordinated Universal Time) before storing.
- When you execute SELECT, Postgres automatically converts from that UTC time to the current Client/Session time zone for display.
INTERVAL (Time interval)
- It does not represent a fixed point in time, but rather the length of a time span (such as 2 hours 30 minutes, 3 days 5 hours, 1 year 2 months).
- Supports powerful calculations like direct addition/subtraction of INTERVAL with date or TIMESTAMP.

Size & Limits

| Data Type   | Storage Size | Precision                  | Range                                    |
| :---------- | :----------- | :------------------------- | :--------------------------------------- |
| date        | 4 bytes      | 1 day                      | 4713 BC to 5874897 AD                    |
| time        | 8 bytes      | 1 microsecond (0.000001 s) | 00:00:00 to 24:00:00                     |
| timetz      | 12 bytes     | 1 microsecond              | 00:00:00+15:59 to 24:00:00-15:59         |
| timestamp   | 8 bytes      | 1 microsecond              | 4713 BC to 294276 AD                     |
| timestamptz | 8 bytes      | 1 microsecond              | 4713 BC to 294276 AD                     |
| interval    | 16 bytes     | 1 microsecond              | -178,000,000 years to +178,000,000 years |

Detail

Create a table as follows:

CREATE TABLE test (
    col_date DATE,
    col_time TIME,
    col_timetz TIMETZ,
    col_timestamp TIMESTAMP,
    col_timestamptz TIMESTAMPTZ,
    col_duration INTERVAL
);

Use the following queries to test. When an invalid value is INSERTed, an appropriate error message will appear:

DATE

INSERT INTO test(col_date) VALUES ('2026-01-01');
INSERT INTO test(col_date) VALUES ('2026-02-30');

TIME

INSERT INTO test(col_time) VALUES ('14:30:20');
INSERT INTO test(col_time) VALUES ('14:70:00');

TIMETZ

INSERT INTO test(col_timetz) VALUES ('14:30:40-15:00');
INSERT INTO test(col_timetz) VALUES ('14:30:40+15:00');
INSERT INTO test(col_timetz) VALUES ('14:30:40-16:00');
INSERT INTO test(col_timetz) VALUES ('14:30:40+16:00');

Supported time zone range is from -15 to +15.

TIMESTAMP

INSERT INTO test(col_timestamp) VALUES ('2026-01-01 14:30:40');
INSERT INTO test(col_timestamp) VALUES ('2026-01-01 14:70:40');

TIMESTAMPTZ

INSERT INTO test(col_timestamptz) VALUES ('2026-01-01 14:30:40-15:00');
INSERT INTO test(col_timestamptz) VALUES ('2026-01-01 14:30:40+15:00');
INSERT INTO test(col_timestamptz) VALUES ('2026-01-01 14:30:40-16:00');
INSERT INTO test(col_timestamptz) VALUES ('2026-01-01 14:30:40+16:00');

Supported time zone range is from -15 to +15. This data type is recommended because it contains complete specific time information.

INTERVAL

-- Statement 1
INSERT INTO test(col_duration) VALUES ('1 year 2 months 3 days 4 hours 5 minutes 6 seconds 7 milliseconds');
INSERT INTO test(col_duration) VALUES ('1 year 2 month 3 day 4 hour 5 minute 6 second 7 millisecond');

-- Statement 2
INSERT INTO test(col_duration) VALUES ('1y 2mon 3d 4h 5m 6s 7ms');

-- Statement 3
INSERT INTO test(col_duration) VALUES ('2 hours 30 minutes');

-- Statement 4
INSERT INTO test(col_duration) VALUES ('1.5 seconds');
INSERT INTO test(col_duration) VALUES ('2 minute 1.5 seconds');
INSERT INTO test(col_duration) VALUES ('2 minute 1.5 seconds 4ms');

-- Statement 5
INSERT INTO test(col_duration) VALUES ('P1Y2M3DT4H');

-- Statement 6
INSERT INTO test(col_duration) VALUES (now() + interval '10 days');
SELECT now() + interval '10 days';