B.2. Date/Time Key Words

Table B-1 shows the tokens that are recognized as names of months.

Table B-2 shows the tokens that are recognized as names of days of the week.

Table B-3 shows the tokens that serve various modifier purposes.

The key word ABSTIME is ignored for historical reasons: In very old releases of PostgreSQL, invalid values of type abstime were emitted as Invalid Abstime. This is no longer the case however and this key word will likely be dropped in a future release.

Table B-4 shows the time zone abbreviations recognized by PostgreSQL in date/time input values. Note that these names are not necessarily used for date/time output — output is driven by the official timezone abbreviation(s) associated with the currently selected timezone parameter setting. (It is likely that future releases will make some use of timezone for input as well.)

The table is organized by time zone offset from UTC, rather than alphabetically. This is intended to facilitate matching local usage with recognized abbreviations for cases where these might differ.

Table B-6 shows the time zone names recognized by PostgreSQL as valid settings for the timezone parameter. Note that these names are conceptually as well as practically different from the names shown in Table B-4: most of these names imply a local daylight-savings time rule, whereas the former names each represent just a fixed offset from UTC.

In many cases there are several equivalent names for the same zone. These are listed on the same line. The table is primarily sorted by the name of the principal city of the zone.

In addition to the names listed in the table, PostgreSQL will accept POSIX-style time zone specifications of the form STDoffset or STDoffsetDST, where STD is a zone abbreviation, offset is a numeric offset in hours west from UTC, and DST is an optional daylight-savings zone abbreviation, assumed to stand for one hour ahead of the given offset. For example, if EST5EDT were not already a recognized zone name, it would be accepted and would be functionally equivalent to USA East Coast time. When a daylight-savings zone name is present, it is assumed to be used according to the same daylight-savings transition rules used in the zic time zone database's posixrules entry. In a standard PostgreSQL installation, posixrules is the same as US/Eastern, so that POSIX-style time zone specifications follow USA daylight-savings rules. If needed, you can adjust this behavior by replacing the posixrules file.

One should be wary that the POSIX-style time zone feature can lead to silently accepting bogus input, since there is no check on the reasonableness of the zone abbreviations. For example, SET TIMEZONE TO FOOBAR0 will work, leaving the system effectively using a rather peculiar abbreviation for UTC.