Thread: Date_part & cast.
Hi,
We use PostgreSQL 6.5.3 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3, and we get the following results with date_part
:
db=> select date_part( 'dow', date '20000421' );
date_part
--------- 5
(1 row)
db=> select date_part( 'dow', date ( '20000421' ) );
date_part
--------- 6
(1 row)
Is this correct ? If yes, I could not find any explanation for the second result which also differ from "select
date_part('dow', date (20000421) );"
Thanks for any help,
-Benoit Brodard.
On Fri, 21 Apr 2000, Benoit Brodard wrote: > Hi, > > We use PostgreSQL 6.5.3 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3, and we get the following results with date_part: > > db=> select date_part( 'dow', date '20000421' ); > date_part > --------- > 5 > (1 row) > > db=> select date_part( 'dow', date ( '20000421' ) ); > date_part > --------- > 6 > (1 row) > > Is this correct ? No it is not. > If yes, I could not find any explanation for the second result which also > differ from "select date_part( 'dow', date (20000421) );" This, together with "hundreds" of other problems, has been fixed in postgresql-7.0RC1. btw, the days of the week start with Sunday = 1 so your queries now say:- template1=# select date_part( 'dow', date '20000421' );date_part ----------- 6 (1 row) template1=# select date_part( 'dow', date ( '20000421' ) );date_part ----------- 6 (1 row) However I think this demonstrates a bug in the date functions. chris@berty:~ > cal 11 1927 # In which I trust. November 1927 Su Mo Tu We Th Fr Sa 1 2 3 4 56 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 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? Using postgresql-7.0RC1. If somebody could direct me to the general area in the source tree, I might be able to come up with a patch & btw, to whom should I send it? -- Sincerely etc., NAME Christopher Sawtell - iOpen Technologies Ltd.CELL PHONE 021 257 4451ICQ UIN 45863470EMAIL chris @ iopen. co . nz, csawtell @ xtra . co . nzWWW http://www.iopen.co.nzCNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
Christopher Sawtell <csawtell@xtra.co.nz> writes:
> btw, the days of the week start with Sunday = 1
Actually, date_part() seems to think Sunday = 0 ...
> However I think this demonstrates a bug in the date functions.
> chris@berty:~ > cal 11 1927 # In which I trust.
> November 1927
> Su Mo Tu We Th Fr Sa
> 1 2 3 4 5
> 6 7 8 9 10 11 12
> 13 14 15 16 17 18 19
> 20 21 22 23 24 25 26
> 27 28 29 30
> 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)
Odd. I get 4,5,6,0, just like it should be. I am thinking you may be
seeing some problem associated with a daylight-savings transition or
some such info that Postgres gets from the local operating system.
A lot of variants of Unix have pretty unreliable timezone tables for
dates before 1970. Might want to check what your local tztab has for
1927.
I'm running on HPUX 10.20 ... and am in EST5EDT time zone ...
what about you?
regards, tom lane
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