Postgres Pro
Downloads
Home   >   mailing lists

Thread: Platform dependency in timestamp parsing

Platform dependency in timestamp parsing

From
Tom Lane
Date:
Using current sources, the following sequence:

set DateStyle TO 'Postgres';
set TimeZone TO 'PST8PDT';
select '2001-09-22T18:19:20'::timestamp(2);

produces
        timestamptz
------------------------------Sat Sep 22 11:19:20 2001 PDT

on my HPUX box, and evidently also on your machine because that's
what's in the timestamptz expected file.  However, on a LinuxPPC
machine I get
        timestamptz
------------------------------Sat Sep 22 18:19:20 2001 PDT

ie, the value after 'T' is interpreted as local time not GMT time.

Question 1: which behavior is correct per spec?  I'd have expected
local time myself, but I'm not sure where this is specified.

Question 2: where to look for the reason for the difference in the
code?  I'm a tad surprised that the HP box behaves more like
yours does than the LinuxPPC box ...
        regards, tom lane

Re: Platform dependency in timestamp parsing

From
Thomas Lockhart
Date:
> Using current sources, the following sequence:
> set DateStyle TO 'Postgres';
> set TimeZone TO 'PST8PDT';
> select '2001-09-22T18:19:20'::timestamp(2);
> produces... (snip) ...
> on my HPUX box, and evidently also on your machine because that's
> what's in the timestamptz expected file.  However, on a LinuxPPC
> machine I get ... (snip) ...
> ie, the value after 'T' is interpreted as local time not GMT time.
> Question 1: which behavior is correct per spec?  I'd have expected
> local time myself, but I'm not sure where this is specified.

It should be local time.

> Question 2: where to look for the reason for the difference in the
> code?  I'm a tad surprised that the HP box behaves more like
> yours does than the LinuxPPC box ...

Me too :)

It is a one line fix in datetime.c, on or about line 918. It needs a
"tmask = 0;" for the new DTK_ISO_TIME case so that the "feature bitmask"
is not altered by the "T" in the string. When it is altered, it thinks
that a time zone was already specified, so does not try to determine
one.

Before:

thomas=# select timestamp '2001-10-19T16:47';  
------------------------2001-10-19 09:47:00-07

After:

thomas=# select timestamp '2001-10-19T16:47';
------------------------2001-10-19 16:47:00-07

I have patches...
                   - Thomas

Re: Platform dependency in timestamp parsing

From
Thomas Lockhart
Date:
I've applied patches; all regression tests pass and the
'yyy-mm-ddThh:mm:ss' is now handled correctly afaict.

There is an ongoing issue regarding precision and rounding for cases
with large interval spans. I've patched the tree with a possible
solution involving counting significant figures before rounding, but I
don't think it is the right one. Especially since it involves a log10()
call :(
                     - Thomas