Extract date part from timestamptz

I am trying to extract the hour from a timestamp with a timezone. However, my times are coming up incorrectly.

Here’s an example, I am using Dbeaver with my timezone set to EST:

SELECT '2020-01-24 14:27:12' AT TIME ZONE 'US/Pacific' as foo,
EXTRACT(HOUR FROM foo) as ex,
DATE_PART('HOUR', foo::timestamp) as dp

RETURNS:

foo                 |ex |dp
2020-01-24 17:27:12 |22 | 22

• Why is my time coming up 3 hours ahead, it should be 3 hours behind?
• Extract and DATE_PART don’t seem to get me the hour I would like. It looks like it’s taking 17 as EST and then converting it to UTC. Here’s what I am expecting to get:

foo                 |ex |dp
2020-01-24 11:27:12 |11 | 11

Answer

Check if your timezone is set to EST:

SELECT current_setting('TIMEZONE');

or with:

show timezone;

If it is not you can set it like this:

set timezone to est;

AS shown in this DEMO

If that is not working try with convert_timezone

select convert_timezone('US/Pacific', '2020-01-24 14:27:12')

And exploring the mater on hand I have found this fact:

Note Amazon Redshift doesn’t validate POSIX-style time zone specifications, so it is possible to set the time zone to an invalid value. For example, the following command doesn’t return an error, even though it sets the time zone to an invalid value.

set timezone to ‘xxx36’;

from this source: https://docs.aws.amazon.com/redshift/latest/dg/CONVERT_TIMEZONE.html