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';

Statement 1: Natural time units can be fully used. Whether you add s to the unit or not, the INSERT succeeds.

Statement 2: This query uses units with abbreviated characters.

Statement 3: You can combine any time units together.

Statement 4: Decimal values (like 1.5) can be used for time, but must be placed at the end of the time string.

Statement 5: The string P1Y2M3DT4H is a standard ISO 8601 interval format fully supported by PostgreSQL for the INTERVAL data type.

• P (Period): Mandatory starting character representing Period
• 1Y: 1 Year
• 2M: 2 Months
• 3D: 3 Days
• T (Time): Separator character between date and time/minute/second parts
• 4H: 4 Hours
• Specific meaning of this string: 1 year, 2 months, 3 days and 4 hours.

Statement 6: INTERVAL can be used together with the now function, but saving directly into an INTERVAL column is not supported.

• now() + interval '10 days' fetches the time 10 days later.

Happy coding!

See more articles here.