Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA91834 for ; Sun, 23 Apr 2000 09:44:15 -0400 (EDT) (envelope-from peter@localhost.its.uu.se) Received: from regulus.student.UU.SE ([130.238.5.2]:34021 "EHLO regulus.its.uu.se") by merganser.its.uu.se with ESMTP id ; Sun, 23 Apr 2000 15:43:53 +0200 Received: from peter (helo=localhost) by regulus.its.uu.se with local-esmtp (Exim 3.02 #2) id 12jMjk-00009S-00; Sun, 23 Apr 2000 15:47:48 +0200 Date: Sun, 23 Apr 2000 15:47:48 +0200 (CEST) From: Peter Eisentraut To: Christopher Sawtell cc: Benoit Brodard , pgsql-sql@postgresql.org Subject: Re: Date_part & cast. In-Reply-To: <00042212582300.29813@berty> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-1 Content-Transfer-Encoding: 8BIT X-Archive-Number: 200004/261 Christopher Sawtell writes: > > db=> select date_part( 'dow', date '20000421' ); > > date_part > > --------- > > 5 > > (1 row) > > > > db=> select date_part( 'dow', date ( '20000421' ) ); > > date_part > > --------- > > 6 > > (1 row) This appears to work now. > > If yes, I could not find any explanation for the second result which also > > differ from "select date_part( 'dow', date (20000421) );" This is the reason: peter=# select date (20000421); date ------------ 1970-08-20 (1 row) IMO, that's anywhere from non-obvious to violation of standard to dangerous, but of course those who stick to the official, SQL approved, PostgreSQL endorsed date input format DATE '2000-04-21' shouldn't have problems like this. > btw, the days of the week start with Sunday = 1 No, Sunday is 0. > template1=# select date_part( 'dow', date '19271124' ); > date_part > ----------- > 4 > (1 row) > > template1=# select date_part( 'dow', date '19271125' ); > date_part > ----------- > 5 > (1 row) > > template1=# select date_part( 'dow', date '19271126' ); > date_part > ----------- > 0 > (1 row) > > template1=# select date_part( 'dow', date '19271127' ); > date_part > ----------- > 1 > (1 row) > > There seems to be a discontinuity here doesn't there? Hmm, these work perfectly fine for me. On some platforms you cannot trust date calculations before 1970; perhaps that's the case here. > If somebody could direct me to the general area in the source tree, I Somewhere in backend/utils/adt/{datetime|timestamp}.c no doubt. > might be able to come up with a patch & btw, to whom should I send it? pgsql-patches@postgresql.org -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden