CODEWITHSUNDEEP
sqlpostgresqldatetimestamptimezone
Matías L.
Matías L.

Reputation: 11

How to convert timestamp without timezone and integer representing timezone to a new timestamp without timezone in PostgreSQL

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.

Upvotes: 0

Views: 2370

Answers (3)

Matías L.
Matías L.

Reputation: 11

Managed to get the expected result with:

extract(hour from snapshot_date + interval '1 hour' * timezone)

Upvotes: 1

Adrian Klaver
Adrian Klaver

Reputation: 19570

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

Upvotes: 0

user330315
user330315

Reputation:

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))

Upvotes: 2

Related Questions