date -> text convertion error

Petter Reinholdtsen <pere@hungry.com> writes:

I have run into a strange type convertion bug in PostgreSQL. I have a
table with a 'birtdate' field with type 'date'. I convert this to
text to reformat it to a string 'DDMMYY'. A few dates are converted
wrong. The first example moves the birth date from 25. to 24.:

pere=> select birthdate, birthdate::text
from user_appl where fname = 'Antal';
birthdate|text
----------+----------------------
1965-04-25|1965-04-24 23:00:00+01
(1 row)

pere=> select birthdate, birthdate::text
from user_appl where fname = 'Petter';
birthdate|text
----------+----------------------
1973-07-11|1973-07-11 00:00:00+01
(1 row)

What is going on, and how can I stop this from happending?

Note the time of day. This is evidently a timezone-related bug.
My guess is that you inserted the data on a system that didn't
know that 1965-04-24 was a daylight-savings day, and are reading
it on a system that does (or vice versa?). Midnight on one day
may look like 11PM the day before, depending on whether you think
it's currently DST or not.

A lot of Unix systems have daylight savings tables that don't go back
further than 1970, so they default to assuming standard time for
all dates before that. That's probably what's bitten you. Postgres
just relies on the standard system library date-conversion functions,
so any timezone bugs in the underlying system will be reflected in
Postgres output...

This has been discussed before, see the pgsql-sql archives.

regards, tom lane

Note the time of day. This is evidently a timezone-related bug.

You almost make sense, but the real problem here is that the date is
converted to date + clock 0 when casted to datetime. A more
reasonable way to do it would be converting it to date + clock 12:00.
Then one would avoid the whole problem.

My guess is that you inserted the data on a system that didn't know
that 1965-04-24 was a daylight-savings day, and are reading it on a
system that does (or vice versa?).

It is inserted and read on the same system. It is crasy that
converting to text and back gives different result:

pere=> select fname from user_appl where birthdate != birthdate::text::date;
fname
-----
Antal
(1 row)

pere=>

GNU libc seems to have zoneinfo for Norway back to before 1916, so I
don't think your guess is correct.
--
##> Petter Reinholdtsen <## | pere@td.org.uit.no
O- <SCRIPT Language="Javascript">window.close()</SCRIPT>
http://www.hungry.com/~pere/ | Go Mozilla, go! Go!