How AT TIME ZONE Works in PostgreSQL

In PostgreSQL, you can use the AT TIME ZONE clause to convert a timestamp to another timezone.

Syntax

You can use it with any of the following three variants:

timestamp with time zone AT TIME ZONE zone
timestamp without time zone AT TIME ZONE zone
time with time zone AT TIME ZONE zone

Where zone is the timezone that you want the value on the left to be converted to.

Just to be clear, the only difference between the three variants is in the data type of the timestamp to be converted.

The three data types are:

timestamp with time zone
timestamp without time zone
time with time zone

The result you get will depend on the data type.

The following table outlines the result that each variant produces.

Expression	Return Type	Description
`timestamp with time zone AT TIME ZONE zone`	`timestamp without time zone`	Convert given time stamp with time zone to the new time zone, with no time zone designation
`timestamp without time zone AT TIME ZONE zone`	`timestamp with time zone`	Treat given time stamp without time zone as located in the specified time zone
`time with time zone AT TIME ZONE zone`	`time with time zone`	Convert given time with time zone to the new time zone

Variant 1

Here’s what happens when you convert a timestamp with time zone value.

SELECT timestamp with time zone '2025-11-20 00:00:00+00' AT TIME ZONE 'Africa/Cairo';

Result:

2025-11-20 02:00:00

When the original timestamp is a timestamp with time zone value, it is shifted to the specified timezone, and the result is returned without a timezone offset.

Here’s what happens if I change the timezone offset of the input value.

SELECT timestamp with time zone '2025-11-20 00:00:00+01' AT TIME ZONE 'Africa/Cairo';

Result:

2025-11-20 01:00:00

The resulting timestamp is shifted according to the input timezone.

And if you specify timestamp with time zone, but the original timestamp doesn’t actually include a timezone, then the local timezone is assumed.

SELECT timestamp with time zone '2025-11-20 00:00:00' AT TIME ZONE 'Africa/Cairo';

Result:

2025-11-19 16:00:00

In this case, the resulting timestamp has been put back eight hours. This means that my local timezone is eight hours ahead of Africa/Cairo.

We can see the timezone offset that was used by selecting the input value directly.

SELECT timestamp with time zone '2025-11-20 00:00:00';

Result:

2025-11-20 00:00:00+10

Variant 2

Here’s what happens when the original timestamp uses a timestamp without time zone value.

In other words, it doesn’t include a timezone.

SELECT timestamp without time zone '2025-11-20 00:00:00' AT TIME ZONE 'Africa/Cairo';

Result:

2025-11-20 08:00:00+10

When the original timestamp doesn’t include a timezone, the result is displayed using the current TimeZone setting and the timezone offset is appended.

This applies any time you specify timestamp without time zone, even if the timestamp does in fact contain a timezone offset.

SELECT timestamp without time zone '2025-11-20 00:00:00+12' AT TIME ZONE 'Africa/Cairo';

Result:

2025-11-20 08:00:00+10

This is to be expected, because if I select just the timestamp without time zone value, this is what I get:

SELECT timestamp without time zone '2025-11-20 00:00:00+12';

Result:

2025-11-20 00:00:00

Variant 3

Here’s what happens when the original timestamp uses a time with time zone value.

SELECT time with time zone '00:00:00+00' AT TIME ZONE 'Africa/Cairo';

Result:

02:00:00+02

It converts the given time with time zone value to the new time zone, and appends the timezone offset to the result.

Here’s what happens if I change the timezone offset of the input value.

SELECT time with time zone '00:00:00+01' AT TIME ZONE 'Africa/Cairo';

Result:

01:00:00+02

The resulting timestamp is shifted according to the input timezone.

And if you specify time with time zone, but the original timestamp doesn’t actually include a timezone, the local timezone is assumed.

SELECT time with time zone '00:00:00' AT TIME ZONE 'Africa/Cairo';

Result:

16:00:00+02

In this case, the resulting timestamp has been put back eight hours (because my local timezone is eight hours ahead of Africa/Cairo).

We can see the timezone offset that was used by selecting the input value directly.

SELECT time with time zone '00:00:00';

Result:

00:00:00+10

Time Without Timezone

Although the Postgres documentation doesn’t include a time without time zone variant, here’s an example anyway.

SELECT time without time zone '00:00:00' AT TIME ZONE 'Africa/Cairo';

Result:

16:00:00+02

So in this example the local timezone was assumed for the timezone to be converted.