Skip site navigation (1) Skip section navigation (2)

Re: [BUGS] date -> text convertion error

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Petter Reinholdtsen <pere(at)hungry(dot)com>
Cc: pgsql-bugs(at)postgreSQL(dot)org
Subject: Re: [BUGS] date -> text convertion error
Date: 1999-08-20 16:40:46
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
Petter Reinholdtsen <pere(at)hungry(dot)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

pgsql-bugs by date

Next:From: Petter ReinholdtsenDate: 1999-08-20 17:12:32
Subject: Re: [BUGS] date -> text convertion error
Previous:From: Petter ReinholdtsenDate: 1999-08-20 12:31:18
Subject: date -> text convertion error

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group