Time Data Types

PostgreSQL includes a number of built-in data types specifically designed to handle time- and date-related data.

A number of built-in constants are useful to know for simplifying date-time entry. The following is a list of them:

now—Constant that stores a timestamp upon storage.

today—Constant that refers to midnight on the current day.

tomorrow—Constant that refers to midnight on the next day.

yesterday—Constant that refers to midnight of the previous day.

PostgreSQL evaluates constants at the start of a transaction, and this might result in undesired behavior. For instance, using the now constant in a series inserted inside a transaction will result in all rows having the same timestamp. A way around this is to use the now() function, which is evaluated upon each call, not during transaction creation.

DATE

Description

Holds a value that describes a particular day. Many different input formats are supported (see the following section).

Inputs

Valid range from 4713BC t o 3 2767 A D

Possible input formats:

June 22, 1971—Standard prose format of date.

June 22, 200 BC—Specifying the era.

1971-0622—ISO format (yyyy-mm-dd).

6/22/ 1971—U.S. mode.

22/6/1971—European mode (not a valid date in U.S. mode).

19710622 or 710622—ISO format (yyyymmdd or yymmdd).

1971.174 or 71.174—Year and the day of the year.

Storage Size

4 bytes

Notes

Valid month formats and abbreviations:

January	Jan
February	Feb
March	Mar
April	Apr
May	May
June	Jun
July	Jul
August	Aug
September	Sep or Sept
October	Oct
November	Nov
December	Dec

Valid days of the week and abbreviations:

Monday	Mon
Tuesday	Tue or Tues
Wednesday	Wed or Weds
Thursday	Thu,Thur, or Thurs
Friday	Fri
Saturday	Sat
Sunday	Sun

The preceding describes the input formats; the output formats are specified by the DATESTYLE variable (see the SET SQL command).

INTERVAL

Description

Holds a time-interval value.

Inputs

The input format for INTERVAL is as follows:

Qnt Unit [Qnt Unit …] Direction 

Valid values for Qnt are as follows:

-2147483648 to +2147483648

Valid values for Unit are as follows (plurals are also valid):

Second 

Hour 

Minute 

Day 

Week 

Month 

Year 

Decade 

Century 

Millennium 

Valid values for Direction are as follows:

Ago—For items in the past.

[blank ]—For future items.

Storage Size

12 bytes

Example Data

1 Week Ago

5 Years 3 Months Ago

30 Days

Notes

INTERVAL is accurate to a resolution of .000001 second (1 microsecond).

TIME

Description

Holds an entry for a time-based value.

Inputs

The valid range for TIME is from 00: 00: 00.00 to 23: 59: 59.99.

The valid input formats that TIME can take are as follows:

08: 24—ISO format

08:24: 50—ISO format

08:24: 50.15—ISO format

082450—ISO format

08:24 PM—Standard

20: 24—24-hour format

z—Same as 00:00:00

zulu—Same as 00:00:00

Storage Size

4 bytes

Notes

The TIME data type is a SQL-compatible format. The TIME data type is accurate to a resolution of .000001 (1 microsecond).

TIME WITH TIME ZONE

Description

Holds an entry for a time-based value with included time-zone information.

Inputs

The valid range for TIME WITH TIME ZONE is from 00:00:00.00+12 to 23:59:59.99-12.

The valid input formats that TIME WITH TIME ZONE can take are as follows:

08:246—ISO format

08:24:506—ISO format

08:24:50.156—ISO format

0824506—ISO format

Storage Size

4 bytes

Notes

TIME WITH TIME ZONE will accept any time-based input format that is also legal for the TIME data type, except time zone information is appended to the end.

The TIME data type is a SQL-compatible format. The TIME WITH TIME ZONE data type is accurate to a resolution of .000001 (1 microsecond).

TIMESTAMP

Description

Holds values that represent time and date information.

Inputs

The valid range for TIMESTAMP is from 471301-01 00:00:00.00 BC to 146500112-31 23:59:59.99 AD.

The valid input formats that TIMESTAMP can take are as follows:

Date Time [Era] [Time Zone] 

For instance:

2001-11-24 08:23:11—Standard TIMESTAMP.

2001-11-24 08:23:11 AD -6: 00— with era and time zone.

November 11, 2001 08:23:11—Prose-style TIMESTAMP.

Storage Size

8 bytes

Notes

Because of the inclusion of time, date, era, and time-zone information, the TIMESTAMP is a popular data type for storage of temporal elements.