From: Peter Eisentraut <peter_e@gmx.net>
To: Christopher Sawtell <csawtell@xtra.co.nz>
Cc: Benoit Brodard <benoit@cyberdeck.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Date_part & cast.
Date: Sun, 23 Apr 2000 15:47:48 +0200 (CEST)
Message-ID: <Pine.LNX.4.21.0004230030200.349-100000@localhost.localdomain> (raw)
In-Reply-To: <00042212582300.29813@berty>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: peter_e@gmx.net, csawtell@xtra.co.nz, benoit@cyberdeck.net
Subject: Re: Date_part & cast.
In-Reply-To: <Pine.LNX.4.21.0004230030200.349-100000@localhost.localdomain>
References: <00042212582300.29813@berty> <Pine.LNX.4.21.0004230030200.349-100000@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox