Date and time types are a convenient way to store date and time related data in a uniform SQL data structure, without having to worry about the conventions involved with storage (e.g., if you were to try to store such information in a character data type). PostgreSQL uses Julian dates for all date and time calculations. By fixing the length of a year at about 365.24 days, these Julian dates can correctly predict and calculate any date after 4713BC, as well as far into the future.

PostgreSQL supports all of the SQL92-defined date and time types, shown in Table 3-14 , with some PostgreSQL-specific variations. Perhaps most notable of these variations is the extended flexibility pertaining to time zones.

Table 3-14. Date and Time Types

Name

Storage

Description

Range

date

4 bytes

A calendar date (year, month, and day)

4713 BC to 32767 AD

time

4 bytes

The time of day only, without time zone information

00:00:00.00 to 23:59:59.99

time with time zone

4 bytes

The time of day only, including a time zone

00:00:00.00+12 to 23:59:59.99-12

timestamp

8 bytes

Both the calendar date and time, with time zone information

1903 AD to 2037 AD

interval

12 bytes

A general time span interval

-1780000000 years to 17800000 years