Skip site navigation (1) Skip section navigation (2)

Re: Date_part & cast.

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: Christopher Sawtell <csawtell(at)xtra(dot)co(dot)nz>
Cc: Benoit Brodard <benoit(at)cyberdeck(dot)net>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Date_part & cast.
Date: 2000-04-23 13:47:48
Message-ID: Pine.LNX.4.21.0004230030200.349-100000@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-sql
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(at)postgresql(dot)org


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e(at)gmx(dot)net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


In response to

pgsql-sql by date

Next:From: Peter EisentrautDate: 2000-04-23 13:48:26
Subject: Re: TOAST (was: BLOB)
Previous:From: Tom LaneDate: 2000-04-23 00:06:07
Subject: Re: question on UPDATE rules

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group