Let's say I have a table with two fields: snapshot_date (timestamp without timezone) and timezone (integer),
| snapshot_date | timezone |
|---|---|
| 2021-01-12 08:00:00 | -3 |
| 2021-01-12 00:00:00 | -5 |
I need to extract the local hour from this table, I tried with (EXTRACT (hour from timestamp))::integer timezone, this would work for the first row, giving an hour result of 5 but with the second row you get -5, which should actually be 19 since the corresponding date adjusted with timezone is 2021-01-11 19:00:00. So what I want to do is calculate a new snapshot_date adjusted with the timezone, so I can correctly extract the hour after.
CodePudding user response：
You need to first add the number of hours to the timestamp, then extract the hour:
extract(hour from snapshot_date make_interval(hours => timezone))
CodePudding user response：
Something like:
show timezone; US/Pacific
select ('2021-01-12 08:00:00'::timestamp::text || (-3)::text)::timestamptz;
2021-01-12 03:00:00-08
select extract(hour from('2021-01-12 08:00:00'::timestamp::text || (-3)::text)::timestamptz);
3
CodePudding user response：
Managed to get the expected result with:
extract(hour from timestamp interval '1 hour' * timezone)