Bug #630: date/time storage problem: timestamp parsed incorrectly...

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...

- 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

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?

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
$ 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 = Fri Dec 13 20:45:52 1901 GMT isdst=0 gmtoff=0
PST Sat Dec 14 20:45:52 1901 UTC = Sat Dec 14 20:45:52 1901 GMT isdst=0 gmtoff=0
PST Mon Jan 18 03:14:07 2038 UTC = Mon Jan 18 03:14:07 2038 GMT isdst=0 gmtoff=0
PST Tue Jan 19 03:14:07 2038 UTC = Tue Jan 19 03:14:07 2038 GMT isdst=0 gmtoff=0
PSD Fri Dec 13 20:45:52 1901 UTC = Fri Dec 13 20:45:52 1901 GMT isdst=0 gmtoff=0
PSD Sat Dec 14 20:45:52 1901 UTC = Sat Dec 14 20:45:52 1901 GMT isdst=0 gmtoff=0
PSD Mon Jan 18 03:14:07 2038 UTC = Mon Jan 18 03:14:07 2038 GMT isdst=0 gmtoff=0
PSD Tue Jan 19 03:14:07 2038 UTC = Tue Jan 19 03:14:07 2038 GMT isdst=0 gmtoff=0
GMT Fri Dec 13 20:45:52 1901 UTC = Fri Dec 13 20:45:52 1901 GMT isdst=0 gmtoff=0
GMT Sat Dec 14 20:45:52 1901 UTC = Sat Dec 14 20:45:52 1901 GMT isdst=0 gmtoff=0
GMT Mon Jan 18 03:14:07 2038 UTC = Mon Jan 18 03:14:07 2038 GMT isdst=0 gmtoff=0
GMT Tue Jan 19 03:14:07 2038 UTC = Tue Jan 19 03:14:07 2038 GMT isdst=0 gmtoff=0
CST Fri Dec 13 20:45:52 1901 UTC = Fri Dec 13 20:45:52 1901 GMT isdst=0 gmtoff=0
CST Sat Dec 14 20:45:52 1901 UTC = Sat Dec 14 20:45:52 1901 GMT isdst=0 gmtoff=0
CST Mon Jan 18 03:14:07 2038 UTC = Mon Jan 18 03:14:07 2038 GMT isdst=0 gmtoff=0
CST Tue Jan 19 03:14:07 2038 UTC = Tue Jan 19 03:14:07 2038 GMT isdst=0 gmtoff=0

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
$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=0xbfbff670, ftype=0xbfbff60c, nf=2,
dtype=0xbfbff5c4, tm=0xbfbff6d4, fsec=0xbfbff5c8, tzp=0xbfbff5d0)
at datetime.c:892
warning: Source file is more recent than executable.

892 {
(gdb) c
Continuing.

Breakpoint 2, DetermineLocalTimeZone (tm=0xbfbff6d4) 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_mday))
(gdb) print *tm
$1 = {tm_sec = 0, tm_min = 0, tm_hour = 2, tm_mday = 7, tm_mon = 4,
tm_year = 2002, tm_wday = -1077938292, tm_yday = 138716656, tm_isdst = -1,
tm_gmtoff = -1077938388, tm_zone = 0xbfbff72c "\214÷¿¿"}
[snip]
1515 return tz;
(gdb) print tz
$2 = 1077938388
(gdb) print *tm
$3 = {tm_sec = 0, tm_min = 0, tm_hour = 2, tm_mday = 7, tm_mon = 4,
tm_year = 2002, tm_wday = -1077938292, tm_yday = 138716656, tm_isdst = 0,
tm_gmtoff = -1077938388, tm_zone = 0xbfbff72c "\214÷¿¿"}
(gdb) n
DecodeDateTime (field=0xbfbff670, ftype=0xbfbff60c, nf=2, dtype=0xbfbff5c4,
tm=0xbfbff6d4, fsec=0xbfbff5c8, tzp=0xbfbff5d0) at datetime.c:1448
1448 return 0;
(gdb) print *tm
$4 = {tm_sec = 0, tm_min = 0, tm_hour = 2, tm_mday = 7, tm_mon = 4,
tm_year = 2002, tm_wday = -1077938292, tm_yday = 138716656, tm_isdst = 0,
tm_gmtoff = -1077938388, tm_zone = 0xbfbff72c "\214÷¿¿"}

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=1150, arg1=139024360, arg2=0, arg3=4294967295)
at fmgr.c:1193
[snip]
1197 return result;
(gdb) n
0x80a40e3 in stringTypeDatum (tp=0x847ee00,
string=0x84957e8 "2002-4-7 2:0:0.0", atttypmod=-1) at parse_type.c:181
181 return OidFunctionCall3(op,
(gdb) n
coerce_type (pstate=0x8495288, node=0x8495430, inputTypeId=705,
targetTypeId=1184, atttypmod=-1) at parse_coerce.c:83

83 pfree(val);
(gdb) print *pstate
$9 = {parentParseState = 0x0, p_rtable = 0x8495708, p_joinlist = 0x0,
p_namespace = 0x0, p_last_resno = 2, p_forUpdate = 0x0,
p_hasAggs = 0 '\000', p_hasSubLinks = 0 '\000', p_is_insert = 1 '\001',
p_is_update = 0 '\000', p_target_relation = 0x847fba0,
p_target_rangetblentry = 0x84953a0}
[snip]
backend> select * from tt;
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
----
1: tt = "2036-06-02 22:19:48-07" (typeid = 1184, len = 8, typmod = -1, byval = 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 = 1184, len = 8, typmod = -1, byval = f)
----
backend> insert into tt values ('2002-4-9 2: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 = "2036-06-02 22:19:48-07" (typeid = 1184, len = 8, typmod = -1, byval = f)
----
1: tt = "2002-04-08 02:00:00-07" (typeid = 1184, len = 8, typmod = -1, byval = f)
----
1: tt = "2002-04-09 02:00:00-07" (typeid = 1184, len = 8, typmod = -1, byval = 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 = 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 = "2036-06-02 22:19:48-07" (typeid = 1184, len = 8, typmod = -1, byval = f)
----
1: tt = "2036-06-02 22:49:48-07" (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)
----

Ideas where to look? -sc

--
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

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

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:

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&amp;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?

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?

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

--
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

Thomas Lockhart <lockhart@fourpalms.org> writes:

|> > > 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.

This is the bug report against glibc that prompted the change:

http://bugs.gnu.org/cgi-bin/gnatsweb.pl?cmd=view%20audit-trail&amp;database=default&amp;pr=2738

|> 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 is a joke, isn't it?

|> 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*

Since POSIX says years before 1970 are undefined, it seems you are right.

Andreas.

--
Andreas Schwab, SuSE Labs, schwab@suse.de
SuSE GmbH, Deutschherrnstr. 15-19, D-90429 N�rnberg
Key fingerprint = 58CA 54C7 6D53 942B 1756 01D3 44D5 214B 8276 4ED5
"And now for something completely different."

This is the bug report against glibc that prompted the change:
http://bugs.gnu.org/cgi-bin/gnatsweb.pl?cmd=view%20audit-trail&amp;database=default&amp;pr=2738
|> 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 is a joke, isn't it?

Yes and no; the behavior is in localtime(), not mktime() -- sorry for my
faulty memory. The case I am handling is in recovering local time given
a time_t (in UTC of course). I have independently derived a broken-down
time structure, so have both the original structure and the results of
localtime() available in my code. Here is the relevant comment snippet:

/* XXX HACK
* Argh! My Linux box puts in a 1 second offset for dates less than 1970
* but only if the seconds field was non-zero. So, don't copy the
seconds
* field and instead carry forward from the original - thomas
97/06/18
* Note that GNU/Linux uses the standard freeware zic package as do
* many other platforms so this may not be GNU/Linux/ix86-specific.
* Still shows a problem on my up to date Linux box - thomas 2001-01-17
*/

Believe it or not, the workaround works :)

|> 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*
Since POSIX says years before 1970 are undefined, it seems you are right.

Well, "undefined" does not mean "impossible" or "disallowed". Presumably
the standard was written to accomodate AIX, which has apparently never
supported times before 1970. Of the other 25 or so platforms PostgreSQL
runs on, only a very few machines do not support times before 1970
though I am sorry that I can not tell you which ones. It will be a list
of rather odd machines; hopefully Linux will not join them.

You might look to Solaris as an (imho) exemplary implementation of
date/time support. Their time zone databases are quite detailed and
specific for variations during years prior to 1960, including a bunch of
weird years here in the US during the 1940's.

From my PoV (which is strongly influenced by the large amount of work I
have done with dates and times, both in PostgreSQL and in my other work
in astronomical and satellite tracking) supporting dates and times
before 1970 is important. I'm an old guy, relatively speaking; my
computers should at least be able to handle times back to my birthday!!
;)

I sympathize with the inclination to look to standards when trying to
resolve a problem (we do this in PostgreSQL also). In fact, time zone
handling was controversial enough for the SQL standards people that they
gave up and defined only constant interval offsets and ignore DST and
other features of the real-world.

But I can only imagine how many applications will be broken if this
change actually makes it into production. I would strongly encourage you
and your team to reconsider this change to fundamental glibc date/time
support.

Regards.

- Thomas

Thomas Lockhart <lockhart@fourpalms.org> writes:

|> > This is the bug report against glibc that prompted the change:
|> > http://bugs.gnu.org/cgi-bin/gnatsweb.pl?cmd=view%20audit-trail&amp;database=default&amp;pr=2738
|> > |> 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 is a joke, isn't it?
|>
|> Yes and no; the behavior is in localtime(), not mktime() -- sorry for my
|> faulty memory. The case I am handling is in recovering local time given
|> a time_t (in UTC of course). I have independently derived a broken-down
|> time structure, so have both the original structure and the results of
|> localtime() available in my code. Here is the relevant comment snippet:

Do you have a testcase?

Andreas.

--
Andreas Schwab, SuSE Labs, schwab@suse.de
SuSE GmbH, Deutschherrnstr. 15-19, D-90429 N�rnberg
Key fingerprint = 58CA 54C7 6D53 942B 1756 01D3 44D5 214B 8276 4ED5
"And now for something completely different."