TIME

The AT TIME ZONE construct converts TIME and TIMEZONE types to different time zones.

Syntax

timestamp AT TIME ZONE zone

Semantics


timestamp	TIMESTAMP	converts UTC to local time in given time zone
	TIMESTAMP WITH TIME ZONE	converts local time in given time zone to UTC
	TIME WITH TIME ZONE	converts local time across time zones
zone	is the desired time zone specified either as a text string (for example: `'PST'`) or as an interval (for example: `INTERVAL '-08:00'`). In the text case, the available zone names are abbreviations.

Examples

The local time zone is PST8PDT.

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';

Result: 2001-02-16 19:38:40-05

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';

Result: 2001-02-16 18:38:40

The first example takes a zone-less time stamp and interprets it as MST time (UTC- 7) to produce a UTC time stamp, which is then rotated to PST (UTC-8) for display. The second example takes a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).

p	(precision) specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range 0 to 6.
WITH TIME ZONE	specifies that valid values must include a time zone
WITHOUT TIME ZONE	specifies that valid values do not include a time zone (default). If a time zone is specified in the input it is silently ignored.
TIMETZ	is the same as `TIME WITH TIME ZONE` with no precision

Data Type	Low Value	High Value	Resolution
TIME [p]	00:00:00.00	23:59:59.99	1 MS / 14 digits
TIME [p] WITH TIME ZONE	00:00:00.00+12	23:59:59.99-12	1 ms / 14 digits

Example	Description
`04:05:06.789`	ISO 8601
`04:05:06`	ISO 8601
`04:05`	ISO 8601
`040506`	ISO 8601
`04:05 AM`	same as 04:05; AM does not affect value
`04:05 PM`	same as 16:05; input hour must be <= 12
`04:05:06.789-8`	ISO 8601
`04:05:06-08:00`	ISO 8601
`04:05-08:00`	ISO 8601
`040506-08`	ISO 8601
`04:05:06 PST`	time zone specified by name