to_char_at_timezone()? - Mailing list pgsql-hackers

Hi,

9.4 FINALLY added the UTC offset formatting pattern to to_char(). 
However, it falls a bit short in the sense that it's always the time 
zone offset according to the effective TimeZone value.  This has a few 
issues as far as I can tell:
  1) It's not truly controlled by the query which produces the 
timestamptz values in the case of e.g. functions  2) Having to SET LOCAL before a query is quite ugly  3) It supports
onlya single TimeZone value per query
 

So I got into thinking whether it would make sense to provide a new 
function, say, to_char_at_timezone() to solve this problem.  For example:

local:marko=#* select now();              now
------------------------------- 2014-11-05 00:43:47.954662+01
(1 row)

local:marko=#* select to_char_at_timezone(now(), 'YYYY-MM-DD 
HH24:MI:SSOF', 'Etc/Utc');   to_char_at_timezone
------------------------ 2014-11-04 23:43:47+00
(1 row)

local:marko=#* select to_char_at_timezone(now(), 'YYYY-MM-DD 
HH24:MI:SSOF', 'America/Los_Angeles');   to_char_at_timezone
------------------------ 2014-11-04 15:43:47-08
(1 row)


Any thoughts?  The patch is quite trivial.


.marko