Thread: Problem with Dates
I am using 7.0.3, I have a column tstamp defined to be 'date'; With a current value of '31-12-2000', if I update tstamp=tstamp+'1 year'::timespan I get '1-1-2002' Is this what is supposed to occur. If this isn't the right way to do this, how should it be done ? Thanks -- Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand Ph: +64 6 357 8168, Mobile: +64 21 424 015
On Thu, Jan 25, 2001 at 08:49:27AM +1300, Glen and Rosanne Eustace wrote: > I am using 7.0.3, > I have a column tstamp defined to be 'date'; > > With a current value of '31-12-2000', > if I update tstamp=tstamp+'1 year'::timespan > I get '1-1-2002' This almost sounds like it takes the year 2000, figures out it needs 366 days, and uses that for "1 year". However, I don't see that error myself: template1=> select '2000-12-31'::timestamp+'1 year'; ?column? ------------------------2001-12-31 00:00:00-08 (1 row) template1=> select '2000-12-31'::timestamp+'1 year'::timespan; ?column? ------------------------2001-12-31 00:00:00-08 (1 row) mrc -- Mike Castle Life is like a clock: You can work constantly dalgoda@ix.netcom.com and be right all the time,or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
just wondering which of these two formats seems best pros and cons of each i know that supposedly xql is simpler in style than XML-QL but XML-QL has some nice sql like syntax the perl modules seem to work nicer too
pressie# select '31/12/2000'::date + '1 year'::timespan; ?column? -----------------------------01/01/2002 00:00:00.00 NZDT (1 row) pressie=# Well I do :-( I vaguely remember someone else having the same problem and it was something to do with daylight saving. I don't recall the solution though, if there was one. Glen.
On Thu, Jan 25, 2001 at 03:06:38PM +1300, Glen and Rosanne Eustace wrote: > pressie# select '31/12/2000'::date + '1 year'::timespan; > ?column? > ----------------------------- > 01/01/2002 00:00:00.00 NZDT > (1 row) What are the outputs of select '31/12/2000'::date; select '31/12/2000'::date + '365 days'::timespan; and 364, 363, etc. Not sure if gets you anywhere. But data points. There is an email archive on the postgresql.org website you could search if you think it's been answered before. mrc -- Mike Castle Life is like a clock: You can work constantly dalgoda@ix.netcom.com and be right all the time,or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
On Thu, 25 Jan 2001, Glen and Rosanne Eustace wrote: > pressie# select '31/12/2000'::date + '1 year'::timespan; > ?column? > ----------------------------- > 01/01/2002 00:00:00.00 NZDT > (1 row) > > pressie=# > > Well I do :-( > > I vaguely remember someone else having the same problem and it was > something to do with daylight saving. I don't recall the solution > though, if there was one. It might also have something to do with your timezone. I did the exact same query on my server and got the correct result: cp=> select '31/12/2000'::date + '1 year'::timespan; ?column? ------------------------2001-12-31 00:00:00-05 (1 row) cp=> What happens if instead you add the days? -- Brett http://www.chapelperilous.net/~bmccoy/ --------------------------------------------------------------------------- We are what we are.
How can i crypt and decrypt data when insert or selecting in a database?
template1=# select '31/12/2000'::date; ?column? ------------2000-12-31 (1 row) template1=# select '31/12/2000'::date + '365 days'::timespan; ?column? ------------------------2002-01-01 00:00:00+13 <<<<<<<<<<< Wrong (1 row) template1=# select '31/12/2000'::date + '364 days'::timespan; ?column? ------------------------2001-12-31 00:00:00+13 (1 row) template1=# select '31/12/2000'::date + '363 days'::timespan; ?column? ------------------------2001-12-30 00:00:00+13 (1 row) > Not sure if gets you anywhere. But data points. > > There is an email archive on the postgresql.org website you could search if > you think it's been answered before. I have tried looking here but it is pretty hard to know what to look for. Glen.
On Fri, 26 Jan 2001 12:46, Glen and Rosanne Eustace wrote: [ ... ] > template1=# select '31/12/2000'::date + '365 days'::timespan; > ?column? > ------------------------ > 2002-01-01 00:00:00+13 <<<<<<<<<<< Wrong > (1 row) [ ... ] I get the same result. This business of crooked dates in NZ summertime is frequently because the rest of the world can't twig on to the notion that it is possible to have GMT + 13, and do not take account of it in date/time calculations. -- Sincerely etc., NAME Christopher SawtellCELL PHONE 021 257 4451ICQ UIN 45863470EMAIL csawtell @ xtra . 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:
> On Fri, 26 Jan 2001 12:46, Glen and Rosanne Eustace wrote:
> [ ... ]
>> template1=# select '31/12/2000'::date + '365 days'::timespan;
>> ?column?
>> ------------------------
>> 2002-01-01 00:00:00+13 <<<<<<<<<<< Wrong
>> (1 row)
> [ ... ]
> I get the same result. This business of crooked dates in NZ summertime
> is frequently because the rest of the world can't twig on to the notion that
> it is possible to have GMT + 13, and do not take account of it in date/time
> calculations.
Could one of you try it in 7.1 (beta3 or later)? We've changed some
details of the way daylight-savings transitions are handled in
date-to-timestamp conversions, so I think this might be fixed now.
It's worth checking anyway.
Also, what do you get from '31/12/2000'::date::timestamp?
regards, tom lane
> Could one of you try it in 7.1 (beta3 or later)? We've changed some > details of the way daylight-savings transitions are handled in > date-to-timestamp conversions, so I think this might be fixed now. > It's worth checking anyway. I would prefer not to upgrade right at the moment, as my development system is in the middle of a migration exercise ( hence the discovery of this bug ). If some one else is running 7.1 already and can just change their timezone to New Zealand DT and report the results it would be great. > Also, what do you get from '31/12/2000'::date::timestamp? template1=# select '31/12/2000'::date::timestamp template1-# ; ?column? ------------------------2001-01-01 00:00:00+13 (1 row) template1=#
"Glen and Rosanne Eustace" <agree@godzone.net.nz> writes:
> If some one else is running 7.1 already and can just change their
> timezone to New Zealand DT and report the results it would be great.
Well, with TZ set to NZST-12NZDT I get
regression=# select '31/12/2000'::date::timestamp; ?column?
------------------------2000-12-31 00:00:00+13
(1 row)
regression=# select '31/12/2000'::date + '365 days'::timespan; ?column?
------------------------2001-12-31 00:00:00+13
(1 row)
This looks promising but I wouldn't call it conclusive, particularly
since you're probably using a different OS than I am (I'm on HPUX
10.20). It would be good to bang on it some more with NZ daylight
times --- as Christopher says, GMT+13 is a tad unusual.
regards, tom lane
On Sat, 27 Jan 2001, Glen and Rosanne Eustace wrote: > If some one else is running 7.1 already and can just change their > timezone to New Zealand DT and report the results it would be great. Here ya are: cp=> set time zone 'NZ'; SET VARIABLE cp=> select '12/31/2000'::date + '1 year'::interval; ?column? ------------------------2001-12-31 00:00:00+13 (1 row) cp=> -- Brett http://www.chapelperilous.net/~bmccoy/ --------------------------------------------------------------------------- It's a good thing we don't get all the government we pay for.
> regression=# select '31/12/2000'::date + '365 days'::timespan; > ?column? > ------------------------ > 2001-12-31 00:00:00+13 > (1 row) > > This looks promising but I wouldn't call it conclusive, particularly > since you're probably using a different OS than I am (I'm on HPUX > 10.20). It would be good to bang on it some more with NZ daylight > times --- as Christopher says, GMT+13 is a tad unusual. Is 7.0.3 to 7.1B? simply a reinstall or do I need to unload/reload the database. PS: GMT+13 isn't unusual for us Kiwis, that's how its always been since some twit decided daylight saving was a good idea. The jury is still out on this one. Glen.
On Sat, 27 Jan 2001, Glen and Rosanne Eustace wrote: > Is 7.0.3 to 7.1B? simply a reinstall or do I need to unload/reload the > database. Yep, you need to do whole shebang of dumping and reloading. -- Brett http://www.chapelperilous.net/~bmccoy/ --------------------------------------------------------------------------- It's a good thing we don't get all the government we pay for.
> Yep, you need to do whole shebang of dumping and reloading. Hmmm. I think I'll just cheat for a while and add 364 days, hopefully before the end of NZ daylight savings, the production release of 7.1 will be out. I really don't want to upgrade twice. Glen.
On Sat, 27 Jan 2001 06:44, Glen and Rosanne Eustace wrote: > > Could one of you try it in 7.1 (beta3 or later)? ok, I'll have a go at upgrading. I'll try to get it done either this evening or tomorrow. Hopefully ready on Monday. I built from sources of v-7.0.0 are there some patches somewhere, or do I have to d/l the whole thing via cvs? btw, what's the name of the branch? While we are fiddling with time and zone etc. I have the problem here that the relationship between the date and the day of the week is out by one day too. There should be something about this in the mail archives about 6 to 9 months ago iirc. -- Sincerely etc., NAME Christopher SawtellCELL PHONE 021 257 4451ICQ UIN 45863470EMAIL csawtell @ xtra . 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:
> I built from sources of v-7.0.0 are there some patches somewhere, or do I
> have to d/l the whole thing via cvs?
There's no patch (if there were, it'd be enormous). Either pull from
CVS or use a beta or nightly-snapshot tarball (see pub/dev on our ftp
server).
> btw, what's the name of the branch?
No branch, it's the tip...
> While we are fiddling with time and zone etc. I have the problem here that
> the relationship between the date and the day of the week is out by one day
> too. There should be something about this in the mail archives about 6 to 9
> months ago iirc.
Hm. Please re-submit details if this is still there.
regards, tom lane
On Fri, 26 Jan 2001 12:46, Glen and Rosanne Eustace wrote:
> template1=# select '31/12/2000'::date + '365 days'::timespan;
> ?column?
> ------------------------
> 2002-01-01 00:00:00+13 <<<<<<<<<<< Wrong
> (1 row)
This appears to be fixed in the current sources by CVSup :-
23:16:03 chris@berty:~ $ psql --version
psql (PostgreSQL) 7.1beta3
contains readline, history support
Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
Portions Copyright (c) 1996 Regents of the University of California
Read the file COPYRIGHT or use the command \copyright to see the
usage and distribution terms.
Winter Time:
template1=# select '30/06/2000:10:30'::timestamp as date; date
------------------------2000-06-30 10:30:00+12
(1 row)
template1=# select '30/06/2000:10:30'::timestamp + '1 year'::interval as
date; date
------------------------2001-06-30 10:30:00+12
(1 row)
Correct!!
Summer Time:
template1=# select '30/12/2000:10:30'::timestamp as date; date
------------------------2000-12-30 10:30:00+13
(1 row)
template1=# select '30/12/2000:10:30'::timestamp + '1 year'::interval as
date; date
------------------------2001-12-30 10:30:00+13
(1 row)
Correct!!
Now lets try with '365 days' instead of '1 year'
template1=# select '30/12/2000:10:30'::timestamp + '365 days'::interval as
date; date
------------------------2001-12-30 10:30:00+13
(1 row)
Correct!!
Now lets try across a leap year:
template1=# select '30/01/2004:10:30'::timestamp as date; date
------------------------2004-01-30 10:30:00+13
(1 row)
template1=# select '30/01/2004:10:30'::timestamp + '1 year'::interval as date; date
------------------------2005-01-30 10:30:00+13
(1 row)
template1=# select '30/01/2004:10:30'::timestamp + '365 days'::interval as
date; date
------------------------2005-01-29 10:30:00+13
(1 row)
Correct!!
school=# select * from day;number | name
--------+----------- 0 | Sunday 1 | Monday 2 | Tuesday 3 | Wednesday 4 | Thursday 5 | Friday
6 | Saturday
(7 rows)
school=# select name from day where number= \
(select date_part('dow','now'::datetime) as day); name
--------Sunday
(1 row)
Correct!!
The PostgreSQL Team is to be congratulated.
--
Sincerely etc.,
NAME Christopher SawtellCELL PHONE 021 257 4451ICQ UIN 45863470EMAIL csawtell @ xtra . 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 <<--