Обсуждение: Bug #630: date/time storage problem: timestamp parsed incorrectly...

Sean Chittenden (sean@chittenden.org) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
date/time storage problem: timestamp parsed incorrectly...

Long Description
It looks like a bad parser or defaults for time values.  The example code below explains the problem best.  I'm not
surewhy, or where... but it took me about a day to track down (PostgreSQL is never wrong!).  If I include a timezone,
thingsseem to work.  For some reason, only dates from yesterday and today break things... I think it's because -7 is
thesame as my timezone, PST (now -7). 

Sample Code
CREATE TABLE timestamp_test (
  utc_timestamp TIMESTAMP NOT NULL
);

INSERT INTO timestamp_tmp VALUES ('2002-4-7 2:0:0.0');
SELECT * from timestamp_tmp;
        utc_date        
------------------------
 2036-06-02 22:55:24-07
(1 row)
INSERT INTO timestamp_tmp VALUES ('2002-4-7 -8 2:0:0.0');
SELECT * from timestamp_tmp;
        utc_date        
------------------------
 2036-06-02 22:55:24-07
 2002-04-07 03:00:00-07
(2 rows)


No file was uploaded with this report

> date/time storage problem: timestamp parsed incorrectly...
> It looks like a bad parser or defaults for time values.  The example code below explains the problem best.  I'm not
surewhy, or where... but it took me about a day to track down (PostgreSQL is never wrong!).  If I include a timezone,
thingsseem to work.  For some reason, only dates from yesterday and today break things... I think it's because -7 is
thesame as my timezone, PST (now -7). 

Well, as long as you realize that PostgreSQL is always right you are on
track ;)

I'm guessing that you have a damaged timezone database on your system.
What time zone does your system think it is in? What system are you
running on? I'm not seeing a problem on my Linux box running 7.2 (well,
except for the jump at the time zone boundary):

lockhart=# select timestamp '2002-4-7 2:0:0.0';
      timestamptz
------------------------
 2002-04-07 01:00:00-08
(1 row)

But that is not the 2036 result you are seeing, so I can only speculate
on your specific problem...

                     - Thomas

> > date/time storage problem: timestamp parsed incorrectly...
>
> > It looks like a bad parser or defaults for time values.  The
> > example code below explains the problem best.  I'm not sure why,
> > or where... but it took me about a day to track down (PostgreSQL
> > is never wrong!).  If I include a timezone, things seem to work.
> > For some reason, only dates from yesterday and today break
> > things... I think it's because -7 is the same as my timezone, PST
> > (now -7).
>
> Well, as long as you realize that PostgreSQL is always right you are
> on track ;)
>
> I'm guessing that you have a damaged timezone database on your
> system.  What time zone does your system think it is in? What system
> are you running on? I'm not seeing a problem on my Linux box running
> 7.2 (well, except for the jump at the time zone boundary):
>
> lockhart=# select timestamp '2002-4-7 2:0:0.0';
>       timestamptz
> ------------------------
>  2002-04-07 01:00:00-08
> (1 row)
>
> But that is not the 2036 result you are seeing, so I can only
> speculate on your specific problem...

ACK!  Hmm... fresh build of FreeBSD:

$ uname -a
FreeBSD ninja1.internal 4.5-STABLE FreeBSD 4.5-STABLE #0: Fri Apr  5 18:08:12 PST 2002
root@ninja1.internal:/opt/obj/opt/src/sys/NINJA i386 

$ psql
# SELECT timestamp '2002-4-7 2:0:0.0';
      timestamptz
------------------------
 2036-06-02 22:57:08-07
(1 row)

# SELECT version();
                          version
------------------------------------------------------------
 PostgreSQL 7.2 on i386--freebsd4.5, compiled by GCC 2.95.3
(1 row)

This isn't happy making.  What OS are you running?  Seems like a lower
level problem.  Do you know if it's a system call making the
formatting call?  -sc

--
Sean Chittenden

> $ uname -a
> FreeBSD ninja1.internal 4.5-STABLE FreeBSD 4.5-STABLE #0: Fri Apr  5 18:08:12 PST 2002
root@ninja1.internal:/opt/obj/opt/src/sys/NINJA i386 
> $ psql
> # SELECT timestamp '2002-4-7 2:0:0.0';
>       timestamptz
> ------------------------
>  2036-06-02 22:57:08-07
> # SELECT version();
>                           version
> ------------------------------------------------------------
>  PostgreSQL 7.2 on i386--freebsd4.5, compiled by GCC 2.95.3
> This isn't happy making.  What OS are you running?  Seems like a lower
> level problem.  Do you know if it's a system call making the
> formatting call?

PostgreSQL uses system calls to get the current time zone if it is not
specified in the input string.

I'm running a fairly new Linux (Mandrake distro), which has the zinc
package as part of glibc-2.2.4

Do you have another way to verify your time zone setup? Do you have the
"zdump" command to look at your time zone info? If you want to step
through your PostgreSQL code, I could give you some suggestions on what
to look for:

1) run the backend using "gdb postgres"

2) before telling gdb to "run", set a breakpoint on DecodeDateTime and
DetermineLocalTimeZone. Use "b DecodeDateTime".

3) run the program. Use "run <dbname>" where <dbname> might be your user
name.

4) at the prompt, type in the above query.

5) at first breakpoint, continue by typing "c".

6) at the DetermineLocalTimeZone breakpoint, start stepping through code
with "s". Check values as you go along.

hth

                      - Thomas

> >  PostgreSQL 7.2 on i386--freebsd4.5, compiled by GCC 2.95.3 This
> > isn't happy making.  What OS are you running?  Seems like a lower
> > level problem.  Do you know if it's a system call making the
> > formatting call?
>=20
> PostgreSQL uses system calls to get the current time zone if it is
> not specified in the input string.

I'm inclined to agree after having stepped through things.

> I'm running a fairly new Linux (Mandrake distro), which has the zinc
> package as part of glibc-2.2.4

The what package?  <:~)

> Do you have another way to verify your time zone setup? Do you have
> the "zdump" command to look at your time zone info?

It appears to be correct:

$ date
Tue Apr  9 14:40:51 PDT 2002
$ zdump=20
$ zdump PST PSD GMT CST
PST  Tue Apr  9 21:40:15 2002 GMT
PSD  Tue Apr  9 21:40:15 2002 GMT
GMT  Tue Apr  9 21:40:15 2002 GMT
CST  Tue Apr  9 21:40:15 2002 GMT
$ zdump -v PST PSD GMT CST
PST  Fri Dec 13 20:45:52 1901 UTC =3D Fri Dec 13 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
PST  Sat Dec 14 20:45:52 1901 UTC =3D Sat Dec 14 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
PST  Mon Jan 18 03:14:07 2038 UTC =3D Mon Jan 18 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0
PST  Tue Jan 19 03:14:07 2038 UTC =3D Tue Jan 19 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0
PSD  Fri Dec 13 20:45:52 1901 UTC =3D Fri Dec 13 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
PSD  Sat Dec 14 20:45:52 1901 UTC =3D Sat Dec 14 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
PSD  Mon Jan 18 03:14:07 2038 UTC =3D Mon Jan 18 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0
PSD  Tue Jan 19 03:14:07 2038 UTC =3D Tue Jan 19 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0
GMT  Fri Dec 13 20:45:52 1901 UTC =3D Fri Dec 13 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
GMT  Sat Dec 14 20:45:52 1901 UTC =3D Sat Dec 14 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
GMT  Mon Jan 18 03:14:07 2038 UTC =3D Mon Jan 18 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0
GMT  Tue Jan 19 03:14:07 2038 UTC =3D Tue Jan 19 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0
CST  Fri Dec 13 20:45:52 1901 UTC =3D Fri Dec 13 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
CST  Sat Dec 14 20:45:52 1901 UTC =3D Sat Dec 14 20:45:52 1901 GMT isdst=3D=
0 gmtoff=3D0
CST  Mon Jan 18 03:14:07 2038 UTC =3D Mon Jan 18 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0
CST  Tue Jan 19 03:14:07 2038 UTC =3D Tue Jan 19 03:14:07 2038 GMT isdst=3D=
0 gmtoff=3D0

> If you want to step through your PostgreSQL code, I could give you
> some suggestions on what to look for:
[snip]

(gdb) b DecodeDateTime
Breakpoint 1 at 0x811568d: file datetime.c, line 892.
(gdb) b DetermineLocalTimeZone
Breakpoint 2 at 0x81161a9: file datetime.c, line 1463.
(gdb) run foo
Starting program: /opt/ports/databases/postgresql7/work/postgresql-7.2/src/=
backend/postgres foo
DEBUG:  database system was shut down at 2002-04-09 14:42:06 PDT
DEBUG:  checkpoint record is at 0/12B514
DEBUG:  redo record is at 0/12B514; undo record is at 0/0; shutdown TRUE
DEBUG:  next transaction id: 109; next oid: 32942
DEBUG:  database system is ready

POSTGRES backend interactive interface=20
$Revision: 1.245 $ $Date: 2002/01/10 01:11:45 $

backend> create table tt ( tt timestamp );
backend> insert into tt values ('2002-4-7 2:0:0.0');

Breakpoint 1, DecodeDateTime (field=3D0xbfbff670, ftype=3D0xbfbff60c, nf=3D=
2,=20
    dtype=3D0xbfbff5c4, tm=3D0xbfbff6d4, fsec=3D0xbfbff5c8, tzp=3D0xbfbff5d=
0)
    at datetime.c:892
warning: Source file is more recent than executable.

892     {
(gdb) c
Continuing.

Breakpoint 2, DetermineLocalTimeZone (tm=3D0xbfbff6d4) at datetime.c:1463
1463    {
(gdb) s
1466            if (HasCTZSet)
(gdb) n
1468            else if (IS_VALID_UTIME(tm->tm_year, tm->tm_mon, tm->tm_mda=
y))
(gdb) print *tm
$1 =3D {tm_sec =3D 0, tm_min =3D 0, tm_hour =3D 2, tm_mday =3D 7, tm_mon =
=3D 4,=20
  tm_year =3D 2002, tm_wday =3D -1077938292, tm_yday =3D 138716656, tm_isds=
t =3D -1,=20
  tm_gmtoff =3D -1077938388, tm_zone =3D 0xbfbff72c "\214=F7=BF=BF"}
[snip]
1515            return tz;
(gdb) print tz
$2 =3D 1077938388
(gdb) print *tm
$3 =3D {tm_sec =3D 0, tm_min =3D 0, tm_hour =3D 2, tm_mday =3D 7, tm_mon =
=3D 4,=20
  tm_year =3D 2002, tm_wday =3D -1077938292, tm_yday =3D 138716656, tm_isds=
t =3D 0,=20
  tm_gmtoff =3D -1077938388, tm_zone =3D 0xbfbff72c "\214=F7=BF=BF"}
(gdb) n
DecodeDateTime (field=3D0xbfbff670, ftype=3D0xbfbff60c, nf=3D2, dtype=3D0xb=
fbff5c4,=20
    tm=3D0xbfbff6d4, fsec=3D0xbfbff5c8, tzp=3D0xbfbff5d0) at datetime.c:1448
1448            return 0;
(gdb) print *tm
$4 =3D {tm_sec =3D 0, tm_min =3D 0, tm_hour =3D 2, tm_mday =3D 7, tm_mon =
=3D 4,=20
  tm_year =3D 2002, tm_wday =3D -1077938292, tm_yday =3D 138716656, tm_isds=
t =3D 0,=20
  tm_gmtoff =3D -1077938388, tm_zone =3D 0xbfbff72c "\214=F7=BF=BF"}


It looks as though the data is getting parsed correctly.  Could it be
that the data is getting written incorrectly?

[further down in the gdb session]
OidFunctionCall3 (functionId=3D1150, arg1=3D139024360, arg2=3D0, arg3=3D429=
4967295)
    at fmgr.c:1193
[snip]
1197            return result;
(gdb) n
0x80a40e3 in stringTypeDatum (tp=3D0x847ee00,=20
    string=3D0x84957e8 "2002-4-7 2:0:0.0", atttypmod=3D-1) at parse_type.c:=
181
181             return OidFunctionCall3(op,
(gdb) n
coerce_type (pstate=3D0x8495288, node=3D0x8495430, inputTypeId=3D705,=20
    targetTypeId=3D1184, atttypmod=3D-1) at parse_coerce.c:83

83                              pfree(val);
(gdb) print *pstate
$9 =3D {parentParseState =3D 0x0, p_rtable =3D 0x8495708, p_joinlist =3D 0x=
0,=20
  p_namespace =3D 0x0, p_last_resno =3D 2, p_forUpdate =3D 0x0,=20
  p_hasAggs =3D 0 '\000', p_hasSubLinks =3D 0 '\000', p_is_insert =3D 1 '\0=
01',=20
  p_is_update =3D 0 '\000', p_target_relation =3D 0x847fba0,=20
  p_target_rangetblentry =3D 0x84953a0}
[snip]
backend> select * from tt;
blank
         1: tt  (typeid =3D 1184, len =3D 8, typmod =3D -1, byval =3D f)
        ----
         1: tt =3D "2036-06-02 22:19:48-07"       (typeid =3D 1184, len =3D=
 8, typmod =3D -1, byval =3D f)
        ----

What seems to be particularly interesting is the following:

backend> insert into tt values ('2002-4-8 2:0:0.0');
blank
         1: tt  (typeid =3D 1184, len =3D 8, typmod =3D -1, byval =3D f)
        ----
backend> insert into tt values ('2002-4-9 2:0:0.0');
blank
         1: tt  (typeid =3D 1184, len =3D 8, typmod =3D -1, byval =3D f)
        ----
backend> select * from tt;
blank
         1: tt  (typeid =3D 1184, len =3D 8, typmod =3D -1, byval =3D f)
        ----
         1: tt =3D "2036-06-02 22:19:48-07"       (typeid =3D 1184, len =3D=
 8, typmod =3D -1, byval =3D f)
        ----
         1: tt =3D "2002-04-08 02:00:00-07"       (typeid =3D 1184, len =3D=
 8, typmod =3D -1, byval =3D f)
        ----
         1: tt =3D "2002-04-09 02:00:00-07"       (typeid =3D 1184, len =3D=
 8, typmod =3D -1, byval =3D f)
        ----

It seems as if this problem only happens with dates that happen
_during_ the date switch.

backend> insert into tt values ('2002-4-7 2:30:0.0');
blank
         1: tt  (typeid =3D 1184, len =3D 8, typmod =3D -1, byval =3D f)
        ----
backend> insert into tt values ('2002-4-7 3:0:0.0');
blank
         1: tt  (typeid =3D 1184, len =3D 8, typmod =3D -1, byval =3D f)
        ----
backend> select * from tt;
blank
         1: tt  (typeid =3D 1184, len =3D 8, typmod =3D -1, byval =3D f)
        ----
         1: tt =3D "2036-06-02 22:19:48-07"       (typeid =3D 1184, len =3D=
 8, typmod =3D -1, byval =3D f)
        ----
         1: tt =3D "2036-06-02 22:49:48-07"       (typeid =3D 1184, len =3D=
 8, typmod =3D -1, byval =3D f)
        ----
         1: tt =3D "2002-04-07 03:00:00-07"       (typeid =3D 1184, len =3D=
 8, typmod =3D -1, byval =3D f)
        ----

Ideas where to look?  -sc

--=20
Sean Chittenden

Err... brain-o on my part (didn't know what I was looking for until I
put in a date that does exist and followed it through):

> (gdb) b DecodeDateTime
> Breakpoint 1 at 0x811568d: file datetime.c, line 892.
> (gdb) b DetermineLocalTimeZone
> Breakpoint 2 at 0x81161a9: file datetime.c, line 1463.
> (gdb) run foo
>=20
> backend> create table tt ( tt timestamp );
> backend> insert into tt values ('2002-4-7 2:0:0.0');

If I use 3am on the 7th, I get the following:

(gdb) print *tm
$2 =3D {tm_sec =3D 0, tm_min =3D 0, tm_hour =3D 3, tm_mday =3D 7, tm_mon =
=3D 3,
 tm_year =3D 102, tm_wday =3D 0, tm_yday =3D 96, tm_isdst =3D 1,
 tm_gmtoff =3D -25200, tm_zone =3D 0x28420c78 "PDT"}

Looks like it's a "bug" in mktime() on FreeBSD: it doesn't seem to do
so well with invalid times that happen between daylight savings
time...  or is that a postgres thing for not kicking up an error (out
of bounds time)?  Or should 2am PST be converted to 3am?  -sc

--=20
Sean Chittenden

> Looks like it's a "bug" in mktime() on FreeBSD: it doesn't seem to do
> so well with invalid times that happen between daylight savings
> time...  or is that a postgres thing for not kicking up an error (out
> of bounds time)?  Or should 2am PST be converted to 3am?  -sc

Here is the man page on Linux:

The  mktime() function converts a broken-down time structure, expressed
as local time, to calendar time representation. The function ignores the
specified contents of the structure members tm_wday and tm_yday and
recomputes them from the other information in the broken-down time
structure. If structure members are outside their legal interval, they
will be normalized (so that, e.g., 40 October is changed into 9
November). Calling mktime() also sets the external variable tzname with
information about the current time zone. If the specified broken-down
time cannot be represented as calendar time (seconds since the epoch),
mktime() returns a value of (time_t)(-1) and does not alter the tm_wday
and tm_yday members of the broken-down time structure.


Does that look similar to FreeBSD?

I don't think that our code checks explicitly for a "-1" return, since
the range is checked just before the call, but it would probably be a
good idea if it did (assuming that other mktime() implementations had
the same convention for an error return of course).

This is the first report I can remember in 6 years of this particular
symptom, and I have the strong feeling that no matter what we end up
doing there *is* a problem with the FreeBSD database of time zones or
(possibly) in its implementation of mktime().

What do you see as the return value from mktime()?

                     - Thomas

On FreeBSD newsyslog shows the same interesting sort of problem witha
'time' value of @T02 on the day of the leap change, sudden;y it'll balk
saying the format of the line is wrong.  Could be related on an outside
area as newsyslog uses mktime and some ISO time format.

Sean Chittenden wrote:

>Err... brain-o on my part (didn't know what I was looking for until I
>put in a date that does exist and followed it through):
>
>>(gdb) b DecodeDateTime
>>Breakpoint 1 at 0x811568d: file datetime.c, line 892.
>>(gdb) b DetermineLocalTimeZone
>>Breakpoint 2 at 0x81161a9: file datetime.c, line 1463.
>>(gdb) run foo
>>
>>backend> create table tt ( tt timestamp );
>>backend> insert into tt values ('2002-4-7 2:0:0.0');
>>
>
>If I use 3am on the 7th, I get the following:
>
>(gdb) print *tm
>$2 = {tm_sec = 0, tm_min = 0, tm_hour = 3, tm_mday = 7, tm_mon = 3,
> tm_year = 102, tm_wday = 0, tm_yday = 96, tm_isdst = 1,
> tm_gmtoff = -25200, tm_zone = 0x28420c78 "PDT"}
>
>Looks like it's a "bug" in mktime() on FreeBSD: it doesn't seem to do
>so well with invalid times that happen between daylight savings
>time...  or is that a postgres thing for not kicking up an error (out
>of bounds time)?  Or should 2am PST be converted to 3am?  -sc
>

Sean Chittenden <sean@chittenden.org> writes:
> Looks like it's a "bug" in mktime() on FreeBSD: it doesn't seem to do
> so well with invalid times that happen between daylight savings
> time...  or is that a postgres thing for not kicking up an error (out
> of bounds time)?  Or should 2am PST be converted to 3am?  -sc

We've seen a *lot* of problems on various platforms with mktime
misbehaving on corner cases ... although I have to admit that failures
in PST/PDT zone are a new one on me (at least from an American
perspective, that ain't exactly a corner case).  The
DetermineLocalTimeZone() routine in datetime.c is supposed to try to
defend against the more common forms of mktime brain-damage.  Perhaps
you can suggest a way of improving it to work around this FreeBSD
problem.

            regards, tom lane

> > Looks like it's a "bug" in mktime() on FreeBSD: it doesn't seem to
> > do so well with invalid times that happen between daylight savings
> > time...  or is that a postgres thing for not kicking up an error
> > (out of bounds time)?  Or should 2am PST be converted to 3am?  -sc
>
> Here is the man page on Linux:
>
> The mktime() function converts a broken-down time structure,
> expressed as local time, to calendar time representation. The
> function ignores the specified contents of the structure members
> tm_wday and tm_yday and recomputes them from the other information
> in the broken-down time structure. If structure members are outside
> their legal interval, they will be normalized (so that, e.g., 40
> October is changed into 9 November). Calling mktime() also sets the
> external variable tzname with information about the current time
> zone. If the specified broken-down time cannot be represented as
> calendar time (seconds since the epoch), mktime() returns a value of
> (time_t)(-1) and does not alter the tm_wday and tm_yday members of
> the broken-down time structure.
>
>
> Does that look similar to FreeBSD?

Very familiar, from mktime(2):

     The functions mktime() and timegm() convert the broken-down time
     in the structure pointed to by tm into a time value with the same
     encoding as that of the values returned by the time(3) function
     (that is, seconds from the Epoch, UTC).  mktime() interprets the
     input structure according to the current timezone setting (see
     tzset(3)).  timegm() interprets the input structure as
     representing Universal Coordinated Time (UTC).

     The original values of the tm_wday and tm_yday components of the
     struc- ture are ignored, and the original values of the other
     components are not restricted to their normal ranges, and will be
     normalized if needed.  For example, October 40 is changed into
     November 9, a tm_hour of -1 means 1 hour before midnight, tm_mday
     of 0 means the day preceding the current month, and tm_mon of -2
     means 2 months before January of tm_year.  (A positive or zero
     value for tm_isdst causes mktime() to presume initially that
     summer time (for example, Daylight Saving Time) is or is not in
     effect for the specified time, respectively.  A negative value
     for tm_isdst causes the mktime() function to attempt to divine
     whether summer time is in effect for the specified time.  The
     tm_isdst and tm_gmtoff members are forced to zero by timegm().)

> I don't think that our code checks explicitly for a "-1" return,
> since the range is checked just before the call, but it would
> probably be a good idea if it did (assuming that other mktime()
> implementations had the same convention for an error return of
> course).

Just poked through how Ruby handles this and it looks like they go to
reasonable lengths to make sure that it "does the right thing."

http://www.ruby-lang.org/~knu/cgi-bin/cvsweb.cgi/ruby/time.c?rev=1.55&content-type=text/x-cvsweb-markup

irb(main):005:0> Time.local(2002,4,7,1)
Sun Apr 07 01:00:00 PST 2002
irb(main):006:0> Time.local(2002,4,7,3)
Sun Apr 07 03:00:00 PDT 2002
irb(main):007:0> Time.local(2002,4,7,2)
Sun Apr 07 03:00:00 PDT 2002
irb(main):008:0> Time.local(2002,4,7,2,20)
Sun Apr 07 03:20:00 PDT 2002

It's artistically licensed...  ::shrug:: Time.local is a thin wrapper
around mktime().  Check out make_time_t() in the link above.

> This is the first report I can remember in 6 years of this
> particular symptom, and I have the strong feeling that no matter
> what we end up doing there *is* a problem with the FreeBSD database
> of time zones or (possibly) in its implementation of mktime().

I hope so...  this bug hit me nasty like.  I was doing a time series
regression and thought it'd be a cute exercise to sum up the
components...  when I hit only ~99.7% and found out that part of my
data was in 2036 I... flipped, freaked out, debugged, cursed,
scratched head, cursed more... then I went for a Guinness and my world
was calm again.  :~)

> What do you see as the return value from mktime()?

Ehh... let me hack/check.  Looks like 11.  ??  In
lib/libc/stdtime/localtime.c, WRONG is defined as -1, not 11.

1490                    t = mktime(tmp);
(gdb)
1491                    fprintf(stderr, "%p\n", t);  /* GCC optimizes this
                                                        away if I don't do
                            something */
(gdb)
0x3c5e5ba0
(gdb) print t
$1 = 11

Doesn't make much sense to me where that'd come from...  ? -sc

--
Sean Chittenden

Sean Chittenden <sean@chittenden.org> writes:
> Ehh... let me hack/check.  Looks like 11.  ??  In
> lib/libc/stdtime/localtime.c, WRONG is defined as -1, not 11.

> 1490                    t = mktime(tmp);
> (gdb)
> 1491                    fprintf(stderr, "%p\n", t);  /* GCC optimizes this
>                                                         away if I don't do
>                             something */
> (gdb)
> 0x3c5e5ba0
> (gdb) print t
> $1 = 11

> Doesn't make much sense to me where that'd come from...  ? -sc

I'd be inclined to believe the 0x3c5e5ba0 (= Mon Feb 04 2002, 05:00:00
EST according to my local time code) and not the 11.  I think gdb is
dropping the ball here; most likely, failing to warn you that the
register that once held t wasn't preserved over the fprintf function
call.

            regards, tom lane

> > Ehh... let me hack/check.  Looks like 11.  ??  In
> > lib/libc/stdtime/localtime.c, WRONG is defined as -1, not 11.
>
> > 1490                    t = mktime(tmp);
> > (gdb)
> > 1491                    fprintf(stderr, "%p\n", t);  /* GCC optimizes this
> >                                                         away if I don't do
> >                             something */
> > (gdb)
> > 0x3c5e5ba0
> > (gdb) print t
> > $1 = 11
>
> > Doesn't make much sense to me where that'd come from...  ? -sc
>
> I'd be inclined to believe the 0x3c5e5ba0 (= Mon Feb 04 2002,
> 05:00:00 EST according to my local time code) and not the 11.  I
> think gdb is dropping the ball here; most likely, failing to warn
> you that the register that once held t wasn't preserved over the
> fprintf function call.

Ugh, I'm too tired to file a gdb report:

1490                    t = mktime(tmp);
(gdb)
1491                    fprintf(stderr, "%p\n", t);
(gdb) print t
$7 = -1

Good call Tom.  ...  I'm going to file a PR w/ FreeBSD.  I know the
attached patch is something of a hack, but it works.  I'm not totally
wild about altering the original time object, but I don't know that I
have a choice in this case.  Does anyone switch timezones and only
adjust their clocks by anything other than 60min?  I seem to recall
that happening in a few places, but the patch isn't any worse than
where we are now. ::shrug:: This look like an okay patch?

backend> delete from tt;
blank
         1: ctid        (typeid = 27, len = 6, typmod = -1, byval = f)
        ----
backend> insert into tt values ('2002-4-7 2:0:0.0');
blank
         1: tt  (typeid = 1184, len = 8, typmod = -1, byval = f)
        ----
backend> insert into tt values ('2002-4-7 2:45:0.0');
blank
         1: tt  (typeid = 1184, len = 8, typmod = -1, byval = f)
        ----
backend> insert into tt values ('2002-4-7 1:0:0.0');
blank
         1: tt  (typeid = 1184, len = 8, typmod = -1, byval = f)
        ----
backend> insert into tt values ('2002-4-7 3:0:0.0');
blank
         1: tt  (typeid = 1184, len = 8, typmod = -1, byval = f)
        ----
backend> select * from tt;
blank
         1: tt  (typeid = 1184, len = 8, typmod = -1, byval = f)
        ----
         1: tt = "2002-04-07 03:00:00-07"       (typeid = 1184, len = 8, typmod = -1, byval = f)
        ----
         1: tt = "2002-04-07 03:45:00-07"       (typeid = 1184, len = 8, typmod = -1, byval = f)
        ----
         1: tt = "2002-04-07 01:00:00-08"       (typeid = 1184, len = 8, typmod = -1, byval = f)
        ----
         1: tt = "2002-04-07 03:00:00-07"       (typeid = 1184, len = 8, typmod = -1, byval = f)
        ----

-sc

--
Sean Chittenden

...
> Good call Tom.  ...  I'm going to file a PR w/ FreeBSD.  I know the
> attached patch is something of a hack, but it works.  I'm not totally
> wild about altering the original time object, but I don't know that I
> have a choice in this case.  Does anyone switch timezones and only
> adjust their clocks by anything other than 60min?  I seem to recall
> that happening in a few places, but the patch isn't any worse than
> where we are now. ::shrug:: This look like an okay patch?

Yuck. You are trying the right things, but I'm not sure that we should
allow mktime() to fail for special cases like this. Falling back to GMT
(a potential offset of up to 12 hours from what was intended by the
user) is (perhaps) unacceptably ugly, particularly for recent/current
epochs which would be reasonably expected to behave correctly.

I'm inclined to test for an error return from mktime(), or to test it as
an ASSERT(), and then throw an error, rather than passing along
garbaged-up values.

Comments?

                        - Thomas

> > Good call Tom.  ...  I'm going to file a PR w/ FreeBSD.  I know
> > the attached patch is something of a hack, but it works.  I'm not
> > totally wild about altering the original time object, but I don't
> > know that I have a choice in this case.  Does anyone switch
> > timezones and only adjust their clocks by anything other than
> > 60min?  I seem to recall that happening in a few places, but the
> > patch isn't any worse than where we are now. ::shrug:: This look
> > like an okay patch?
>=20
> Yuck. You are trying the right things, but I'm not sure that we
> should allow mktime() to fail for special cases like this. Falling
> back to GMT (a potential offset of up to 12 hours from what was
> intended by the user) is (perhaps) unacceptably ugly, particularly
> for recent/current epochs which would be reasonably expected to
> behave correctly.
>=20
> I'm inclined to test for an error return from mktime(), or to test
> it as an ASSERT(), and then throw an error, rather than passing
> along garbaged-up values.
>=20
> Comments?

I thought about throwing an exception but noticed a few lines down
that the code was returning GMT on failure.  When in Rome, do as the
Romans do.  :~) I personally think that ASSERT should be called after
the 2nd mktime() fails but think that calling ASSERT would break
existing applications though maybe not: I've just had a 6hr crash
course in this code and don't quite have the incite to say one way or
another.

FWIW, I've lobbed something off to the FreeBSD crowd asking if
mktime() should be updated in the system libraries.... but don't think
that'll fix things "soon enough."  -sc

--=20
Sean Chittenden

Hi,

On Tue, 9 Apr 2002 at 19:43, Thomas Lockhart wrote:

> I don't think that our code checks explicitly for a "-1" return, since
> the range is checked just before the call, but it would probably be a
> good idea if it did

Indeed.

As I noticd yesterday, glibc's mktime() has in the current snapshot
been changed to return -1 for dates before the epoch. Our glibc guru
(Cc'ed) told me, this is according to the standards (C and POSIX)
which say, that time_t is undefined for dates prior the epoch, which
to me seems obvoius, because otherwise the error return couldn't be
distinguished from the time_t value "one second before the epoch").

This change causes some of the regression tests to fail ('abstime',
'tinterval', and 'horology'). All failures occur on dates that are
given in PST, lay between 1900 and 1970, and show a difference of 8
hour (regression.diffs attached).

I've added code to DetermineLocalTimeZone that elogs and ERROR if
mktime returns < 0, which showed, that this also happens in some other
tests, but without affecting the results there (maybe pure luck?).

cu
    Reinhard

> > I don't think that our code checks explicitly for a "-1" return, since
> > the range is checked just before the call, but it would probably be a
> > good idea if it did
> As I noticd yesterday, glibc's mktime() has in the current snapshot
> been changed to return -1 for dates before the epoch. Our glibc guru
> (Cc'ed) told me, this is according to the standards (C and POSIX)
> which say, that time_t is undefined for dates prior the epoch, which
> to me seems obvoius, because otherwise the error return couldn't be
> distinguished from the time_t value "one second before the epoch").

??!! I'm sorry that I don't remember the exact context here (didn't this
thread start on a FreeBSD amchine?), but are you saying that glibc
shipped with Linux will potentially stop supporting times and time zones
before 1970?

Standard or not, there is a *long* history of all decent implementations
supporting dates prior to 1970, and platforms which do not do so (AIX?)
have always been a source of scorn and derision. Really.

Ah, but this might explain why I've always seen on my Linux box a 1
second offset returned from mktime() for dates before 1970. Everything
is shifted to allow -1 to be a special value I'll bet...

> This change causes some of the regression tests to fail ('abstime',
> 'tinterval', and 'horology'). All failures occur on dates that are
> given in PST, lay between 1900 and 1970, and show a difference of 8
> hour (regression.diffs attached).

Sure.

> I've added code to DetermineLocalTimeZone that elogs and ERROR if
> mktime returns < 0, which showed, that this also happens in some other
> tests, but without affecting the results there (maybe pure luck?).

Yikes. That is not currently acceptable (most platforms deployed in the
world *do* handle dates and times before 1970), but if I'm understanding
things correctly we will need to somehow reimplement the entire time and
time zone support system within PostgreSQL. I'll start looking at the
FreeBSD code to see what is available. *sigh*

                        - Thomas

> Ugh, I'm too tired to file a gdb report:
>=20
> 1490                    t =3D mktime(tmp);
> (gdb)=20
> 1491                    fprintf(stderr, "%p\n", t);
> (gdb) print t
> $7 =3D -1
>=20
> Good call Tom.  ...  I'm going to file a PR w/ FreeBSD.

The FreeBSD folk are absolutely adamant about having mktime() no
compensate for deadzones between DST shifts and they insist that the
application handle this.  Someone's off looking at how other OS'es
handle this, but this could be an arduous battle on that front.  <:~)

> I know the attached patch is something of a hack, but it works.  I'm
> not totally wild about altering the original time object, but I
> don't know that I have a choice in this case.  Does anyone switch
> timezones and only adjust their clocks by anything other than 60min?
> I seem to recall that happening in a few places, but the patch isn't
> any worse than where we are now. ::shrug:: This look like an okay
> patch?

Are there any objections to the following?  Instead of returning 0 or
utc, I could have it raise an error.  Would that be acceptable?  -sc

> Index: src/backend/utils/adt/datetime.c
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
> retrieving revision 1.88
> diff -u -r1.88 datetime.c
> --- src/backend/utils/adt/datetime.c    2002/02/25 16:17:04    1.88
> +++ src/backend/utils/adt/datetime.c    2002/04/10 06:12:45
> @@ -1439,6 +1439,7 @@
>  DetermineLocalTimeZone(struct tm * tm)
>  {
>      int            tz;
> +    time_t            t;
>=20=20
>      if (HasCTZSet)
>          tz =3D CTimeZone;
> @@ -1463,7 +1464,23 @@
>          /* indicate timezone unknown */
>          tmp->tm_isdst =3D -1;
>=20=20
> -        mktime(tmp);
> +        t =3D mktime(tmp);
> +        if (t =3D=3D -1)
> +        {
> +            /* Bump time up by an hour to see if time was an
> +             * invalid time during a daylight savings switch */
> +            tmp->tm_hour +=3D 1;
> +            t =3D mktime(tmp);
> +
> +            /* Assume UTC if mktime() still fails.
> +             *
> +             * If mktime() was successful with the adjusted time,
> +             * adjust the real time object. */
> +            if (t =3D=3D -1)
> +                return 0;
> +            else
> +                tm->tm_hour +=3D 1;
> +        }
>=20=20
>          tm->tm_isdst =3D tmp->tm_isdst;
>=20=20

--=20
Sean Chittenden

Cuttign down the CC: list this time, apologies if I cut too much and
someone misses a copy of this....

Sean Chittenden wrote:

>
>The FreeBSD folk are absolutely adamant about having mktime() no
>compensate for deadzones between DST shifts and they insist that the
>application handle this.  Someone's off looking at how other OS'es
>handle this, but this could be an arduous battle on that front.  <:~)
>
Personally I'd like to see FreeBSD do away with this strange behaviour.
 It cause my grief because certaint hings *MUST* be done at 0200 every
day in our system, I was forced to do them manually recently, shifing
several hours of work into daytime which had to be paused and bulked
into the next days work.  I realise that this is getting off track but
it just points out that the FreeBSD behaviour is IMHO WRONG.  It causes
applications to fail in an unexpected and odd way.

I'm not objecting to pg patching for it (no choice at the moment) but I
hope the pg team 'officially' puts a little pressure on the BSD folk to
make this behave as expected.

I don't have any compliance docs at the moment, but this strikes me as
somewhat out of spec personally.

>>I know the attached patch is something of a hack, but it works.  I'm
>>not totally wild about altering the original time object, but I
>>don't know that I have a choice in this case.  Does anyone switch
>>timezones and only adjust their clocks by anything other than 60min?
>>I seem to recall that happening in a few places, but the patch isn't
>>any worse than where we are now. ::shrug:: This look like an okay
>>patch?
>>
>
>Are there any objections to the following?  Instead of returning 0 or
>utc, I could have it raise an error.  Would that be acceptable?  -sc
>

> >The FreeBSD folk are absolutely adamant about having mktime() no
> >compensate for deadzones between DST shifts and they insist that
> >the application handle this.  Someone's off looking at how other
> >OS'es handle this, but this could be an arduous battle on that
> >front.  <:~)
>
> Personally I'd like to see FreeBSD do away with this strange
> behaviour.  It cause my grief because certaint hings *MUST* be done
> at 0200 every day in our system, I was forced to do them manually
> recently, shifing several hours of work into daytime which had to be
> paused and bulked into the next days work.  I realise that this is
> getting off track but it just points out that the FreeBSD behaviour
> is IMHO WRONG.  It causes applications to fail in an unexpected and
> odd way.
>
> I'm not objecting to pg patching for it (no choice at the moment)
> but I hope the pg team 'officially' puts a little pressure on the
> BSD folk to make this behave as expected.

Feel free to read over their arguments (archive may not be 100% up to
date):

http://docs.freebsd.org/mail/archive/2002/freebsd-standards/20020414.freebsd-standards.html

> I don't have any compliance docs at the moment, but this strikes me
> as somewhat out of spec personally.

::shrug:: I've gotten enough push back to have an indifferent opinion:
I just want to see PG work w/ some of the bogus data I get every now
and then.  :~)  -sc

--
Sean Chittenden

Sean Chittenden wrote:

>>>The FreeBSD folk are absolutely adamant about having mktime() no
>>>compensate for deadzones between DST shifts and they insist that
>>>the application handle this.  Someone's off looking at how other
>>>OS'es handle this, but this could be an arduous battle on that
>>>front.  <:~)
>>>
>>Personally I'd like to see FreeBSD do away with this strange
>>behaviour.  It cause my grief because certaint hings *MUST* be done
>>at 0200 every day in our system, I was forced to do them manually
>>recently, shifing several hours of work into daytime which had to be
>>paused and bulked into the next days work.  I realise that this is
>>getting off track but it just points out that the FreeBSD behaviour
>>is IMHO WRONG.  It causes applications to fail in an unexpected and
>>odd way.
>>
>>I'm not objecting to pg patching for it (no choice at the moment)
>>but I hope the pg team 'officially' puts a little pressure on the
>>BSD folk to make this behave as expected.
>>
>
>Feel free to read over their arguments (archive may not be 100% up to
>date):
>
>http://docs.freebsd.org/mail/archive/2002/freebsd-standards/20020414.freebsd-standards.html
>
>>I don't have any compliance docs at the moment, but this strikes me
>>as somewhat out of spec personally.
>>
>
>::shrug:: I've gotten enough push back to have an indifferent opinion:
>I just want to see PG work w/ some of the bogus data I get every now
>and then.  :~)  -sc
>
Yes but not everyone changes over at 2AM on the specific day.  The rest
of the world for the most part doesn't in fact.  I don't know what
mktime() behaviour is in different locales (IE different TZs) but if its
the same (IE deadzone @ the same time when the TZ is something in say
the EU who follow different rules) then its broken.  I've got a FreeBSD
4.3 box here I do most of my serving on I'll see if I can get a little
time to do some testing with different TZs.  I don't think that the way
BSD handles it is correct

Also browsing the discussion archives it seems that mktime() atleast on
BSD is inconsistent with how it handles bogus dates anyway.  Looks like
the BSD guys are going to be doing a little navel-looking over this.

> > date/time storage problem: timestamp parsed incorrectly...
>
> > It looks like a bad parser or defaults for time values.  The
> > example code below explains the problem best.  I'm not sure why,
> > or where... but it took me about a day to track down (PostgreSQL
> > is never wrong!).  If I include a timezone, things seem to work.
> > For some reason, only dates from yesterday and today break
> > things... I think it's because -7 is the same as my timezone, PST
> > (now -7).
>
> Well, as long as you realize that PostgreSQL is always right you are
> on track ;)
>
> I'm guessing that you have a damaged timezone database on your
> system.  What time zone does your system think it is in? What system
> are you running on? I'm not seeing a problem on my Linux box running
> 7.2 (well, except for the jump at the time zone boundary):
>
> lockhart=# select timestamp '2002-4-7 2:0:0.0';
>       timestamptz
> ------------------------
>  2002-04-07 01:00:00-08
> (1 row)
>
> But that is not the 2036 result you are seeing, so I can only
> speculate on your specific problem...

ACK!  Hmm... fresh build of FreeBSD:

$ uname -a
FreeBSD ninja1.internal 4.5-STABLE FreeBSD 4.5-STABLE #0: Fri Apr  5 18:08:12 PST 2002
root@ninja1.internal:/opt/obj/opt/src/sys/NINJA i386 

$ psql
# SELECT timestamp '2002-4-7 2:0:0.0';
      timestamptz
------------------------
 2036-06-02 22:57:08-07
(1 row)

# SELECT version();
                          version
------------------------------------------------------------
 PostgreSQL 7.2 on i386--freebsd4.5, compiled by GCC 2.95.3
(1 row)

This isn't happy making.  What OS are you running?  Seems like a lower
level problem.  Do you know if it's a system call making the
formatting call?  -sc


--
Sean Chittenden