Postgres Pro
Downloads
Home   >   mailing lists

Thread: Timezones -- what comes out does not go in?

Timezones -- what comes out does not go in?

From
"Julian Scarfe"
Date:
The timezone on our boxes is set to Etc/UCT (possibly a distro default).

(psql 8.1.4)

=> select to_char(current_timestamp, 'HH24:MI:SS TZ');
   to_char
--------------
 09:05:48 UCT
(1 row)

=>  select '09:05:48 UCT'::time;
ERROR:  invalid input syntax for type time: "09:05:48 UCT"

UTC works, of course:

=> select '09:05:48 UTC'::time;
   time
----------
 09:05:48
(1 row)


Is this behaviour expected? Desirable?

Thanks
Julian

Re: Timezones -- what comes out does not go in?

From
Tom Lane
Date:
"Julian Scarfe" <julian@avbrief.com> writes:
> The timezone on our boxes is set to Etc/UCT (possibly a distro default).
> (psql 8.1.4)

> => select to_char(current_timestamp, 'HH24:MI:SS TZ');
>    to_char
> --------------
>  09:05:48 UCT
> (1 row)

> =>  select '09:05:48 UCT'::time;
> ERROR:  invalid input syntax for type time: "09:05:48 UCT"

This works in CVS HEAD, thanks to some hard work by Joachim Wieland.

http://archives.postgresql.org/pgsql-patches/2006-07/msg00077.php
http://archives.postgresql.org/pgsql-committers/2006-07/msg00258.php

            regards, tom lane

Re: Timezones -- what comes out does not go in?

From
Alvaro Herrera
Date:
Julian Scarfe wrote:
> The timezone on our boxes is set to Etc/UCT (possibly a distro default).
>
> (psql 8.1.4)
>
> => select to_char(current_timestamp, 'HH24:MI:SS TZ');
>   to_char
> --------------
> 09:05:48 UCT
> (1 row)
>
> =>  select '09:05:48 UCT'::time;
> ERROR:  invalid input syntax for type time: "09:05:48 UCT"
>
> Is this behaviour expected? Desirable?

It is expected, because back in 8.1 the timezones recognized came from a
hardcoded table.  It's not desirable of course.  But on the other hand,
it's fixed in the development code:

alvherre=# set timezone to 'Etc/UCT';
SET
alvherre=# select to_char(current_timestamp, 'HH24:MI:SS TZ');
   to_char
--------------
 14:19:05 UCT
(1 fila)

alvherre=# select '14:19:05 UCT'::time;
   time
----------
 14:19:05
(1 fila)


--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Timezones -- what comes out does not go in?

From
"Julian Scarfe"
Date:
From: "Tom Lane" <tgl@sss.pgh.pa.us>

> This works in CVS HEAD, thanks to some hard work by Joachim Wieland.

One of these days I'll find an issue *before* you folks have patched it. :-)

Thanks

Julian