Re: [HACKERS] to_char_at_timezone()?
On 11/04/2014 04:04 PM, Marko Tiikkaja wrote: On 11/5/14, 12:59 AM, Tom Lane wrote: Marko Tiikkaja ma...@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
Re: [HACKERS] to_char_at_timezone()?
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
[HACKERS] 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(), '-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(), '-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
Re: [HACKERS] to_char_at_timezone()?
Marko Tiikkaja ma...@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
Re: [HACKERS] to_char_at_timezone()?
On 11/5/14, 12:59 AM, Tom Lane wrote: Marko Tiikkaja ma...@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
