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 only a 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM6e6ff5063eaede9d9a00a456b4fac61aa8a827819b762e671cbeeacbd5d325970cf3f013e5a9ff60317d43eeb952bc7c@asav-2.01.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers