Stumbled upon a time bug...
Is PostgreSQL broken? Or is it FreeBSD?
--
http://www.freebsd.org/cgi/query-pr.cgi?pr=ports/36954
PostgreSQL does not currently check the results of mktime(). On a
border condition, mktime() fails and would populate invalid data
into the database.
Most other *NIX's seem to automatically account for this and
automatically adjust the value when it gets passed to mktime(&tm).
Should FreeBSD have it's mktime() in libc updated?
CREATE TABEL tt ( tt TIMESTAMP );
INSERT INTO tt VALUES ('2002-4-7 2:0:0.0');
--
Rod Taylor
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. You cannot imagine why you ever felt
otherwise.
Is PostgreSQL broken? Or is it FreeBSD?
Both at most. FreeBSD at least ;)
The Posix definition for mktime() insists that the function return "-1"
if it has an error. Which also happens to correspond to 1 second earlier
than 1970-01-01, causing trouble for supporting *any* times before 1970.
PostgreSQL relies on a side-effect of mktime(), that the time zone
information pointed to in the tm structure *input* to mktime() gets
updated for output. I don't actually care about the function result of
time_t, and don't care what it is set to as long as the time zone info
gets filled in.
That happens on most every platform I know about, with the exception of
AIX (confirming for me its reputation as a strange relative of Unix best
left chained in the cellar).
Apparently glibc (and hence Linux) is at risk of getting this behavior
too, although I *hope* that the mods to glibc will be to return the "-1"
(if necessary) but still using the time zone database to fill in the
time zone information, even for dates before 1970.
I'm not sure I still have the info to include the glibc contact in this
thread. In any case, there is no excuse for refusing to return valid
info for a DST boundary time imho. Even if it requires an arbitrary
convention on how to jump the time forward or backward...
- Thomas