Time values, like date values, may be entered in to a table in a number of ways. Commonly used formats are listed in Table 3-20 . These apply to values of type time , and time with time zone .

Table 3-20. Valid Time Formats

Format Example

Description

01:24

ISO-8601, detailed to minutes

01:24 AM

Equivalent to 01:24 (the AM attached is for readability only, and does not affect the value)

01:24 PM

Equivalent to 13:24 (the hour must be less-than or equal to 12 to use PM)

13:24

24-hour time, equivalent to 01:24 PM

01:24:11

ISO-8601, detailed to seconds

01:24:11.112

ISO-8601, detailed to microseconds

012411

ISO-8601, detailed to seconds, formatted numerically

In addition to these formats, PostgreSQL allows for further description of a time value which is defined as time with time zone by supporting extra time zone parameters following the time value. The supported formats are illustrated in Table 3-21 .

Table 3-21. Valid Time Zone Formats

Format Example

Description

01:24:11-7

ISO-8601, 7 hours behind GMT

01:24:11-07:00

ISO-8601, 7 hours, zero minutes behind GMT

01:24:11-0700

ISO-8601, 7 hours, zero minutes behind GMT

01:24:11 PST

ISO-8601, Pacific Standard Time (7 hours behind GMT)

The time with time zone data type is mainly supported by PostgreSQL to adhere to existing SQL standards, and for portability with other database management systems. If you need to work with time zones, it is recommended that you use the timestamp data type discussed in the section called Timestamps . This is primarily because of the fact that, due to daylight savings, time zones cannot always be meaningfully interpretted without an associated date.

Internally, PostgreSQL keeps track of all time zone information as a numeric offset of GMT (Greenwich Mean Time), which is also known as UTC (Universal Coordinated Time). By default, PostgreSQL's time display will use the time zone that your server's operating system is configured for. If you wish the time value to operate under a different time zone, there are four ways in which you can modify the output.

Set the TZ environment variable on the server

This variable is found by the backend server as the default time zone when the postmaster starts up. It can be set, for example, in the postgres user's .bash_profile file with a Bash export TZ='zone' command.

Set the PGTZ environment variable on the client

This variable can be set by a client written with the libpq library (such as psql ).

Use the SET TIMEZONE TO zone SQL statement

This SQL command sets the time zone for the session to zone (e.g., SET TIMEZONE TO UTC

Use the timestamp AT TIME ZONE 'zone' SQL convention

This SQL92 keyword can be used to specify zone as a text time zone (e.g., PST ) or as an interval (e.g., interval('-07:00') ). This clause may be applied in the middle of a SQL statement following a value which contains a timestamp (e.g., SELECT my_timestamp AT TIME ZONE 'PST' .

Note Time Zone Caveats
 

If an invalid time zone is specified, then most systems will default to the GMT time zone.

Additionally, if the compiler option USE_AUSTRALIAN_RULES was set when PostgreSQL was built, the EST time zone will refer to Australian Eastern Standard Time( with an offset of +10:00 hours from GMT) rather than US Eastern Standard Time.