pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDate_part & cast.
4+ messages / 4 participants
[nested] [flat]
* Date_part & cast.
@ 2000-04-21 07:42 Benoit Brodard <benoit@cyberdeck.net>
2000-04-21 23:48 ` Re: Date_part & cast. Christopher Sawtell <csawtell@xtra.co.nz>
0 siblings, 1 reply; 4+ messages in thread
From: Benoit Brodard @ 2000-04-21 07:42 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Date_part & cast.
2000-04-21 07:42 Date_part & cast. Benoit Brodard <benoit@cyberdeck.net>
@ 2000-04-21 23:48 ` Christopher Sawtell <csawtell@xtra.co.nz>
2000-04-22 23:47 ` Re: Date_part & cast. Tom Lane <tgl@sss.pgh.pa.us>
2000-04-23 13:47 ` Re: Date_part & cast. Peter Eisentraut <peter_e@gmx.net>
0 siblings, 2 replies; 4+ messages in thread
From: Christopher Sawtell @ 2000-04-21 23:48 UTC (permalink / raw)
To: Benoit Brodard <benoit@cyberdeck.net>; pgsql-sql
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 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)
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 4451
ICQ UIN 45863470
EMAIL chris @ iopen . co . nz, csawtell @ xtra . co . nz
WWW http://www.iopen.co.nz
CNOTES 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 <<--
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Date_part & cast.
2000-04-21 07:42 Date_part & cast. Benoit Brodard <benoit@cyberdeck.net>
2000-04-21 23:48 ` Re: Date_part & cast. Christopher Sawtell <csawtell@xtra.co.nz>
@ 2000-04-22 23:47 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2000-04-22 23:47 UTC (permalink / raw)
To: Christopher Sawtell <csawtell@xtra.co.nz>; +Cc: Benoit Brodard <benoit@cyberdeck.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Date_part & cast.
2000-04-21 07:42 Date_part & cast. Benoit Brodard <benoit@cyberdeck.net>
2000-04-21 23:48 ` Re: Date_part & cast. Christopher Sawtell <csawtell@xtra.co.nz>
@ 2000-04-23 13:47 ` Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 4+ messages in thread
From: Peter Eisentraut @ 2000-04-23 13:47 UTC (permalink / raw)
To: Christopher Sawtell <csawtell@xtra.co.nz>; +Cc: Benoit Brodard <benoit@cyberdeck.net>; 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@postgresql.org
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2000-04-23 13:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-21 07:42 Date_part & cast. Benoit Brodard <benoit@cyberdeck.net>
2000-04-21 23:48 ` Christopher Sawtell <csawtell@xtra.co.nz>
2000-04-22 23:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-04-23 13:47 ` Peter Eisentraut <peter_e@gmx.net>
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