0

I'm reading this SO post and am confused. Suppose I have a field that I've been told from our data engineering team is in UTC time. How can I cast it as PST (Pacific time, California)?

select 
    '2021-02-12 05:27:51' as if_this_is_already_utc_then,
    '2021-02-12 05:27:51' at time zone 'pst' as is_this_pst,
    '2021-02-12 05:27:51' at time zone 'utc' at time zone 'pst' as or_is_this_pst

Returns: enter image description here

The two attempts at PST show different timestamps. Which is correct, if any? I'm confused because on the linked post they first seem to convert to UTC and then again to EST. Which do I need here if I know that the original timestamp is UTC and I want to get it to PST California?

Doug Fir
  • 19,971
  • 47
  • 169
  • 299
  • Check `SELECT now(), now() at time zone 'pst';` The second should be in PST time, the first might be in another time zone. – Luuk Mar 06 '21 at 14:08
  • Hi @Luuk OK, this returned two identicals `2021-03-06 09:11:02`? – Doug Fir Mar 06 '21 at 14:11
  • Ok, then `show timezone` also returns "pst". And if you do `SELECT now() at time zone 'Europe/Amsterdam';` you see my local time... (about 15:13, or '03:13 PM' ) – Luuk Mar 06 '21 at 14:14
  • Hmm, you meant to say that your local time is not 09:11 but 06:11 ?? (according to [this](https://www.timeanddate.com/worldclock/converter.html?iso=20210306T140000&p1=tz_pt&p2=16&p3=224)) – Luuk Mar 06 '21 at 14:17
  • Yeah, `SELECT now() at time zone 'Europe/Amsterdam';` returns 15:23 and it's about 10 mins since your comment. – Doug Fir Mar 06 '21 at 14:24
  • ok then `select now() at time zone 'pst';` should return a time like 06:33, if that is not the case then please check the time on your server. – Luuk Mar 06 '21 at 14:34
  • Yep, gives `2021-03-06 07:18:26`. This appears to be correct! I can just do the direct to PST translation then? – Doug Fir Mar 06 '21 at 15:19
  • 1
    That, simply, seems to be the case ! – Luuk Mar 06 '21 at 15:23
  • Thank you! Perhaps I should delete this post? – Doug Fir Mar 06 '21 at 15:38
  • If you REALLY think that it is useless to anyone searching for timezones and datetime in postgresql.... (but there will be many useless post left... ) – Luuk Mar 06 '21 at 15:40
  • 1
    **Caution:** You should not use the timezone abbreviation (PST/PDT) but the full timezone name (so "America/Los_Angeles" or "US/Pacific". This is because the abbreviation does not adjust for daylight savings while the **full name automatically adjusts for daylight savings**. – Belayer Mar 06 '21 at 19:33

0 Answers0