Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154])
	by hub.org (8.10.1/8.10.1) with ESMTP id e8T6OPp13654
	for <pgsql-sql@postgresql.org>; Fri, 29 Sep 2000 02:24:25 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.11.0/8.11.0) with ESMTP id e8T6ONB16322;
	Fri, 29 Sep 2000 02:24:23 -0400 (EDT)
To: josh@agliodbs.com
cc: sqllist <pgsql-sql@postgresql.org>
Subject: Re: Date problem 
In-reply-to: <39D42B7B.800F2870@agliodbs.com> 
References: <200009290207.e8T27Bp59335@hub.org>
	<39D42B7B.800F2870@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
	message dated "Thu, 28 Sep 2000 22:41:15 -0700"
Date: Fri, 29 Sep 2000 02:24:23 -0400
Message-ID: <16319.970208663@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Archive-Number: 200009/233

>> Ok. Let's work. I posted a mail before explaining a strange
>> cituation if my Postgresql: when I use date_part() function to split
>> day, month and year of a date type column, it returns one day before.
>> In other words, '2000-01-01' returns day: 31, month:12, year: 1999.

> No problem here.  Or on PG-ACCESS.  

There's a known (and fixed for 7.1) bug of this sort that occurs on
spring daylight-savings-time transition days: the date-to-timestamp
conversion function picks the wrong timezone offset to interpret
"midnight" in, yielding a timestamp for 11PM of the prior date.
On the fall DST date, you get a timestamp for 1AM.  For example,
in USA zones:

play=> select '2000-04-02'::date::timestamp;
        ?column?
------------------------
 2000-04-01 23:00:00-05
(1 row)

play=> select '2000-10-29'::date::timestamp;
        ?column?
------------------------
 2000-10-29 01:00:00-04
(1 row)

Not sure how this would apply to 2000-01-01, though.  What timezone
are you in, anyway?

			regards, tom lane