X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6417AD1B19A for ; Mon, 14 Jun 2004 15:48:30 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 66005-09 for ; Mon, 14 Jun 2004 18:48:12 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id A5A49D1B16F for ; Mon, 14 Jun 2004 15:47:27 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5EIlF8E005211; Mon, 14 Jun 2004 14:47:16 -0400 (EDT) To: "Eric Lemes" Cc: pgsql-sql@postgresql.org Subject: Re: RES: Datetime problem In-reply-to: References: Comments: In-reply-to "Eric Lemes" message dated "Mon, 14 Jun 2004 13:20:14 -0300" Date: Mon, 14 Jun 2004 14:47:15 -0400 Message-ID: <5210.1087238835@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/118 X-Sequence-Number: 18063 "Eric Lemes" writes: > - PostgreSQL 7.3.2 on i386-redhat-linux GCC 3.2.2 > - Timezone: Brazil (GMT-3, I think). Ah, and 2004-10-10 is a daylight savings transition day where you live, right? (Or at least the obsolete timezone file you have thinks so...) So local midnight on that day doesn't really exist: you go from 11:59:59 standard time to 01:00:00 summer time. PG 7.3 and before got this boundary condition wrong in some cases, but I think 7.4 gets it right. The rule we use now is to interpret invalid or ambiguous times as local standard time (hence, this input means midnight standard time), which is what to_timestamp is doing. However, on reverse conversion you'll get the normalized form of the time, which is 01:00:00 summer time. I think what is happening in 7.3 is that the input is interpreted as midnight summer time, which would reverse-convert as 11:00 PM standard time... regards, tom lane