Thread: Date_part & cast.

Date_part & cast.

From
Benoit Brodard
Date:
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.





Re: Date_part & cast.

From
Christopher Sawtell
Date:
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 <<--



Re: Date_part & cast.

From
Tom Lane
Date:
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


Re: Date_part & cast.

From
Peter Eisentraut
Date:
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