Get TZ abbrev for given timestamp and TZ name

Question

Is it possible to get correct TZ abbreviation after converting column of type timestamptz ?

What I am after, sof example, is to display "2016-06-16 16:00:00-04" in "US/Pacific" timezone with target TZ abbrev. I can get this easily:

06/16/2016 01:00 pm

But I cannot get following:

06/16/2016 01:00 pm PDT

Once converted, timestamptz becomes timestamp without timezone and to_char specifier 'TZ' becomes meaningless...

Here an example of what I have tried:

SELECT
    scheduled_dt                                                                           AS scheduled_dt
  , to_char(scheduled_dt, 'TZ')                                                            AS scheduled_dt_orig_tz  
  -- correct value, but missing TZ  
  , timezone('US/Pacific', scheduled_dt)                                                   AS schedules_dt_converted_tz
  , to_char(timezone('US/Pacific', scheduled_dt), 'MM/DD/YYYY HH:MI pm')                   AS scheduled_dt_converted_tz_localized
   -- after conversion TZ is lost
  , to_char(timezone('US/Pacific', scheduled_dt), 'MM/DD/YYYY HH:MI pm TZ')                AS scheduled_dt_converted_tz_localized_missing_tz
  -- with cast, wrong TZ is displayed 
  , to_char(timezone('US/Pacific', scheduled_dt)::timestamptz, 'MM/DD/YYYY HH:MI pm TZ')   AS scheduled_dt_converted_tz_localized_wrong_tz
FROM orders
LIMIT 5

and sample output:

"scheduled_dt","scheduled_dt_orig_tz","schedules_dt_converted_tz","scheduled_dt_converted_tz_localized","scheduled_dt_converted_tz_localized_missing_tz","scheduled_dt_converted_tz_localized_wrong_tz"
"2016-06-16 16:00:00-04","EDT","2016-06-16 13:00:00","06/16/2016 01:00 pm","06/16/2016 01:00 pm ","06/16/2016 01:00 pm EDT"
"2014-07-15 08:00:00-04","EDT","2014-07-15 05:00:00","07/15/2014 05:00 am","07/15/2014 05:00 am ","07/15/2014 05:00 am EDT"
"2012-11-27 10:15:00-05","EST","2012-11-27 07:15:00","11/27/2012 07:15 am","11/27/2012 07:15 am ","11/27/2012 07:15 am EST"
"2011-03-02 08:30:00-05","EST","2011-03-02 05:30:00","03/02/2011 05:30 am","03/02/2011 05:30 am ","03/02/2011 05:30 am EST"
"2016-02-12 14:23:09-05","EST","2016-02-12 11:23:09","02/12/2016 11:23 am","02/12/2016 11:23 am ","02/12/2016 11:23 am EST"

score 5 · Accepted Answer · answered Sep 13 '16 at 14:19

5

No, because timestamptz (aka timestamp with time zone) isn't really a timestamp with a time zone, in terms of what's stored... you provide a timestamp and a time zone, and internally it's just stored as a UTC value.

It sounds like basically you should be storing the target time zone in a separate field along with the timestamp.

answered Sep 13 '16 at 14:19

Jon Skeet

1,421,763
867
9,128
9,194

1

I am storing the target TZ with full name, stored in joining table (warehouse_locations), so I can covert each row. What I am missing is a 'moment.js TZ library like functionality' that can format the timestamp with short TZ name taking into consideration DST....So I can get 'PDT' or 'PST' based on particular date and timezone....I am already doing this in code but was wondering if it is possible to do it in PG directly – zam6ak Sep 13 '16 at 14:38
@zam6ak: I would personally avoid doing it in the database even if you could. I would transfer it to whatever code is talking to the database *as a timestamp and separate time zone* and do string formatting there. You tend to be in a much better position to do formatting with rich culture information etc. – Jon Skeet Sep 13 '16 at 14:40
I absolutely agree. However, the volume of data in my case is significant (500K and more rows). So I was trying to avoid "translation" step (essentially a loop in a code where each timestamp column is being converted) for sake of gaining speed... – zam6ak Sep 13 '16 at 14:49
@zam6ak: Well you'd only be transferring that to the database... I don't expect it would be significantly faster at doing that conversion than your client code (assuming you're using a reasonable client platform) - and I'd expect the data transfer cost to be more than the conversion time anyway. – Jon Skeet Sep 13 '16 at 14:51

score 3 · Answer 2 · edited May 23 '17 at 12:24

You cannot extract a time zone (name or abbreviation) from a timestamp or timestamptz, because it's simply not there. Detailed explanation:

Since you are storing the target TZ with full name, you might extract the associated time zone abbreviation from the system view pg_timezone_names (which is based on the internal function pg_timezone_names()).

SELECT abbrev
FROM   pg_timezone_names
WHERE  name = 'US/Pacific';

abbrev
--------
PDT

However, per documentation:

The displayed information is computed based on the current value of CURRENT_TIMESTAMP.

This means, you get PDT in summer and PST in winter, based on the current time, not based on the timestamp you want to display.

Not the solution you need, sorry.

The function definition of pg_timezone_names in the PostgreSQL source code might give you ideas how to implement your own C function.

Time zone names with identical properties yield different result when applied to timestamp

On closer inspection it's not the solution you need, sorry. You need a function that takes the time zone name and a timestamp to produce the matching time zone abbreviation ... — Erwin Brandstetter, Sep 13 '16 at 15:32
Correct. I have to do it in the code....Both yours and Jon's answer are correct and full of extra really helpful info. Since Jon answered first, I hope you don't mind if I mark his reply as answer. — zam6ak, Sep 13 '16 at 16:47

Get TZ abbrev for given timestamp and TZ name

2 Answers2