Thread: to_char_at_timezone()?
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
Marko Tiikkaja <marko@joh.to> writes:
> 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:
> ...
> Any thoughts? The patch is quite trivial.
I'm not convinced that it's all that trivial. Is the input timestamp or
timestamptz, and what's the semantics of that exactly (ie what timezone
rotation happens)? One's first instinct is often wrong in this area.
regards, tom lane
On 11/5/14, 12:59 AM, Tom Lane wrote: > Marko Tiikkaja <marko@joh.to> writes: >> 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: >> ... >> Any thoughts? The patch is quite trivial. > > I'm not convinced that it's all that trivial. Is the input timestamp or > timestamptz, and what's the semantics of that exactly (ie what timezone > rotation happens)? One's first instinct is often wrong in this area. In my example, the input is a "timestamptz", and the output is converted to the target time zone the same way timestamptz_out() does, except based on the input timezone instead of TimeZone. Not sure whether it would make sense to do this for "timestamp", or whether there's even a clear intuitive behaviour there. .marko
On 11/04/2014 04:04 PM, Marko Tiikkaja wrote: > On 11/5/14, 12:59 AM, Tom Lane wrote: >> Marko Tiikkaja <marko@joh.to> writes: >>> 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: >>> ... >>> Any thoughts? The patch is quite trivial. >> >> I'm not convinced that it's all that trivial. Is the input timestamp or >> timestamptz, and what's the semantics of that exactly (ie what timezone >> rotation happens)? One's first instinct is often wrong in this area. > > In my example, the input is a "timestamptz", and the output is converted > to the target time zone the same way timestamptz_out() does, except > based on the input timezone instead of TimeZone. > > Not sure whether it would make sense to do this for "timestamp", or > whether there's even a clear intuitive behaviour there. Why wouldn't we just add the timezone as an additional parameter? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 11/5/14, 7:36 PM, Josh Berkus wrote: > On 11/04/2014 04:04 PM, Marko Tiikkaja wrote: >> In my example, the input is a "timestamptz", and the output is converted >> to the target time zone the same way timestamptz_out() does, except >> based on the input timezone instead of TimeZone. >> >> Not sure whether it would make sense to do this for "timestamp", or >> whether there's even a clear intuitive behaviour there. > > Why wouldn't we just add the timezone as an additional parameter? Are you suggesting that we add a new overload of to_char() instead of a new function to_char_at_timezone()? That sounds a bit confusing, but that might just be me. .marko