Re: Bug #630: date/time storage problem: timestamp parsed - Mailing list pgsql-bugs

> This is the bug report against glibc that prompted the change:
> http://bugs.gnu.org/cgi-bin/gnatsweb.pl?cmd=view%20audit-trail&database=default&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