Thread: Timezone abbreviations - out but not in?
One of our guys in Pakistan noticed a problem with Slony that seems to have manifested itself since the last zic update. Slony uses timeofday() as the default value for a timestamp column: -- Executing query: SET timezone='Asia/Karachi'; SELECT timeofday()::timestamp with time zone; ERROR: invalid input syntax for type timestamp with time zone: "Tue Jun 10 19:16:23.186000 2008 PKST" After a little digging, it was suggested by Heikki that clock_timestamp() would be a better bet in 8.2+, however, this appears to have similar issues depending on how it's (mis)used: -- Executing query: set timezone='Asia/Karachi'; set datestyle='SQL'; select clock_timestamp()::text::timestamp with time zone; ERROR: invalid input syntax for type timestamp with time zone: "10/06/2008 18:40:36.769046 PKST" It seems like a bug that we happily output PKST as a timezone (in a 'timestamp with time zone'), but won't accept it back in. Perhaps we should only output names that we can read back, and revert to a numeric offset in other cases? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
"Dave Page" <dpage@pgadmin.org> writes:
> It seems like a bug that we happily output PKST as a timezone (in a
> 'timestamp with time zone'), but won't accept it back in.
[ shrug... ] The set of timezone abbrevs recognized on input is
user-configurable, so that situation will always be possible.
It appears though that our tznames list is a bit out of date: it
has PKT but not PKST or PKDT, which evidently are the current spellings
in use for that zone. It might be time for someone to go through
the zic database again to see what else has changed.
regards, tom lane
On Tue, Jun 10, 2008 at 4:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Dave Page" <dpage@pgadmin.org> writes: >> It seems like a bug that we happily output PKST as a timezone (in a >> 'timestamp with time zone'), but won't accept it back in. > > [ shrug... ] The set of timezone abbrevs recognized on input is > user-configurable, so that situation will always be possible. Right, but shouldn't we always output something we know we can read back in (unambiguously), assuming a server with no user defined abbreviations? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
"Dave Page" <dpage@pgadmin.org> writes:
> On Tue, Jun 10, 2008 at 4:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> [ shrug... ] The set of timezone abbrevs recognized on input is
>> user-configurable, so that situation will always be possible.
> Right, but shouldn't we always output something we know we can read
> back in (unambiguously), assuming a server with no user defined
> abbreviations?
The user can remove abbreviations as well as add them, or change them
in inconsistent ways (indeed the whole point of that feature was to
cope with the fact that the same abbrevs mean different things to
different people). I don't think you can make any useful guarantees
at all.
regards, tom lane
Dave Page wrote: > On Tue, Jun 10, 2008 at 4:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "Dave Page" <dpage@pgadmin.org> writes: > >> It seems like a bug that we happily output PKST as a timezone (in a > >> 'timestamp with time zone'), but won't accept it back in. > > > > [ shrug... ] The set of timezone abbrevs recognized on input is > > user-configurable, so that situation will always be possible. > > Right, but shouldn't we always output something we know we can read > back in (unambiguously), assuming a server with no user defined > abbreviations? That makes no sense because it amounts to saying that we can't ever use any abbreviation. A more useful restriction would be to only output those that are in the set of input-acceptable abbreviations, but perhaps this is not easy to implement. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Tue, Jun 10, 2008 at 4:51 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Dave Page wrote: >> Right, but shouldn't we always output something we know we can read >> back in (unambiguously), assuming a server with no user defined >> abbreviations? > > That makes no sense because it amounts to saying that we can't ever use > any abbreviation. For user defined abbreviations, used for output, yes. But as Tom points out, the user can remove or change abbreviations that way as well so it wouldn't work then anyway. > A more useful restriction would be to only output > those that are in the set of input-acceptable abbreviations, but perhaps > this is not easy to implement. Or just output offsets in every case :-p -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Alvaro Herrera <alvherre@commandprompt.com> writes:
> ... A more useful restriction would be to only output
> those that are in the set of input-acceptable abbreviations, but perhaps
> this is not easy to implement.
I think that is actually what Dave is suggesting, but I don't really
agree with it. To me it's a feature not a bug that timezone input
and output are decoupled. Would you have the input code refuse to
recognize abbrevs that are inconsistent with the current timezone
setting, even though the user intentionally set them up that way?
regards, tom lane
"Dave Page" <dpage@pgadmin.org> writes:
> Or just output offsets in every case :-p
Which is what the default ISO datestyle does ... I believe pg_dump
is careful to force ISO style for exactly this reason.
regards, tom lane