Thread
-
Problem with Dates
Glen and Rosanne Eustace <agree@godzone.net.nz> — 2001-01-24T19:49:27Z
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
-
Re: Problem with Dates
Mike Castle <dalgoda@ix.netcom.com> — 2001-01-24T20:55:52Z
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 -
Veering OT opinions please XQL versus XML-QL
clayton cottingham <drfrog@smartt.com> — 2001-01-24T22:01:30Z
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
-
Re: Problem with Dates
Glen and Rosanne Eustace <agree@godzone.net.nz> — 2001-01-25T02:06:38Z
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. -
Re: Problem with Dates
Mike Castle <dalgoda@ix.netcom.com> — 2001-01-25T02:17:31Z
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 -
Re: Re: Problem with Dates
Brett W. McCoy <bmccoy@chapelperilous.net> — 2001-01-25T02:59:36Z
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. -
Re: Re: Problem with Dates
Brett W. McCoy <bmccoy@chapelperilous.net> — 2001-01-25T12:11:06Z
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. -
Re: Re: Problem with Dates
Brett W. McCoy <bmccoy@chapelperilous.net> — 2001-01-25T12:28:57Z
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. -
crypt and decrypt
Marcos Aurélio S. da Silva <aurelio@cpatc.embrapa.br> — 2001-01-25T17:54:19Z
How can i crypt and decrypt data when insert or selecting in a database?
-
Re: Problem with Dates
Glen and Rosanne Eustace <agree@godzone.net.nz> — 2001-01-25T23:46:21Z
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. -
Re: Re: Problem with Dates
Christopher Sawtell <csawtell@xtra.co.nz> — 2001-01-26T08:22:08Z
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 Sawtell CELL PHONE 021 257 4451 ICQ UIN 45863470 EMAIL csawtell @ xtra . 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 <<--
-
Re: Re: Problem with Dates
Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-26T15:05:15Z
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
-
Re: Re: Problem with Dates
Glen and Rosanne Eustace <agree@godzone.net.nz> — 2001-01-26T17:44:26Z
> 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=# -
Re: Re: Problem with Dates
Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-26T18:00:17Z
"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 -
Re: Re: Problem with Dates
Glen and Rosanne Eustace <agree@godzone.net.nz> — 2001-01-26T18:18:51Z
> 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.
-
Re: Re: Problem with Dates
Glen and Rosanne Eustace <agree@godzone.net.nz> — 2001-01-26T19:31:26Z
> 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.
-
Re: Re: Problem with Dates
Christopher Sawtell <csawtell@xtra.co.nz> — 2001-01-26T22:56:01Z
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 Sawtell CELL PHONE 021 257 4451 ICQ UIN 45863470 EMAIL csawtell @ xtra . 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 <<--
-
Re: Re: Problem with Dates
Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-26T23:49:34Z
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
-
Re: Re: Problem with Dates
Christopher Sawtell <csawtell@xtra.co.nz> — 2001-01-28T10:47:18Z
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 Sawtell CELL PHONE 021 257 4451 ICQ UIN 45863470 EMAIL csawtell @ xtra . 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 <<--