Redshift display timestamp with offset is not working

Question

Hi,

I want to display timestamp with time zone offset, and checking the example for AT TIME ZONE which shows that statement SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST'; shall return following result "2001-02-16 19:38:40-08" which is what i am after. 
But, when i run the example in our cluster, it returns 2001-02-17 03:02:40 +00.
I also tried with TO_CHAR(), but result is the same.
I am using web-based query editor V2.
Where is the issue, does anyone know?

Answer

The example in the doc is a little confusing.

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

And the return of:
```
2001-02-16 19:38:40-08
```
This means the timezone of the cluster that executed this is 8 hours behind UTC. I'm assuming it is Pacific Daylight Savings time or PDT. MST is 6 hours behind UTC so the result is a subtraction of 2 hours.

In your cluster, the result is:
```
2001-02-17 03:02:40 +00
```

So your cluster is in UTC it has to add hours to get to the UTC timezone.

You can alter your user to specify the timezone you are in too.
https://docs.aws.amazon.com/redshift/latest/dg/r_timezone_config.html

Redshift display timestamp with offset is not working

Relevanter Inhalt