Обсуждение: date -> text convertion error

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)

  pere=>

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

System Configuration
---------------------
  Architecture (example: Intel Pentium)         : Intel Pentium II
  Operating System (example: Linux 2.0.26 ELF)  : RedHat Linux 5.2 / 6.0
  PostgreSQL version (example: PostgreSQL-6.5)  : PostgreSQL-6.5
  Compiler used (example:  gcc 2.8.0)           : gcc 2.7.2.3
--
##>  Petter Reinholdtsen <##    | pere@td.org.uit.no
 O-  <SCRIPT Language="Javascript">window.close()</SCRIPT>
http://www.hungry.com/~pere/    | Go Mozilla, go! Go!

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