Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
Thomas Lockhart <[EMAIL PROTECTED]> writes: |> > > I don't think that our code checks explicitly for a "-1" return, since |> > > the range is checked just before the call, but it would probably be a |> > > good idea if it did |> > As I noticd yesterday, glibc's mktime() has in the current snapshot |> > been changed to return -1 for dates before the epoch. Our glibc guru |> > (Cc'ed) told me, this is according to the standards (C and POSIX) |> > which say, that time_t is undefined for dates prior the epoch, which |> > to me seems obvoius, because otherwise the error return couldn't be |> > distinguished from the time_t value "one second before the epoch"). |> |> ??!! I'm sorry that I don't remember the exact context here (didn't this |> thread start on a FreeBSD amchine?), but are you saying that glibc |> shipped with Linux will potentially stop supporting times and time zones |> before 1970? |> |> Standard or not, there is a *long* history of all decent implementations |> supporting dates prior to 1970, and platforms which do not do so (AIX?) |> have always been a source of scorn and derision. Really. This is the bug report against glibc that prompted the change: http://bugs.gnu.org/cgi-bin/gnatsweb.pl?cmd=view%20audit-trail&database=default&pr=2738 |> Ah, but this might explain why I've always seen on my Linux box a 1 |> second offset returned from mktime() for dates before 1970. Everything |> is shifted to allow -1 to be a special value I'll bet... This is a joke, isn't it? |> Yikes. That is not currently acceptable (most platforms deployed in the |> world *do* handle dates and times before 1970), but if I'm understanding |> things correctly we will need to somehow reimplement the entire time and |> time zone support system within PostgreSQL. I'll start looking at the |> FreeBSD code to see what is available. *sigh* Since POSIX says years before 1970 are undefined, it seems you are right. Andreas. -- Andreas Schwab, SuSE Labs, [EMAIL PROTECTED] SuSE GmbH, Deutschherrnstr. 15-19, D-90429 Nürnberg Key fingerprint = 58CA 54C7 6D53 942B 1756 01D3 44D5 214B 8276 4ED5 "And now for something completely different." ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
Thomas Lockhart <[EMAIL PROTECTED]> writes: |> > This is the bug report against glibc that prompted the change: |> > |http://bugs.gnu.org/cgi-bin/gnatsweb.pl?cmd=view%20audit-trail&database=default&pr=2738 |> > |> Ah, but this might explain why I've always seen on my Linux box a 1 |> > |> second offset returned from mktime() for dates before 1970. Everything |> > |> is shifted to allow -1 to be a special value I'll bet... |> > This is a joke, isn't it? |> |> Yes and no; the behavior is in localtime(), not mktime() -- sorry for my |> faulty memory. The case I am handling is in recovering local time given |> a time_t (in UTC of course). I have independently derived a broken-down |> time structure, so have both the original structure and the results of |> localtime() available in my code. Here is the relevant comment snippet: Do you have a testcase? Andreas. -- Andreas Schwab, SuSE Labs, [EMAIL PROTECTED] SuSE GmbH, Deutschherrnstr. 15-19, D-90429 Nürnberg Key fingerprint = 58CA 54C7 6D53 942B 1756 01D3 44D5 214B 8276 4ED5 "And now for something completely different." ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed incorrectly...
> > date/time storage problem: timestamp parsed incorrectly... > > > It looks like a bad parser or defaults for time values. The > > example code below explains the problem best. I'm not sure why, > > or where... but it took me about a day to track down (PostgreSQL > > is never wrong!). If I include a timezone, things seem to work. > > For some reason, only dates from yesterday and today break > > things... I think it's because -7 is the same as my timezone, PST > > (now -7). > > Well, as long as you realize that PostgreSQL is always right you are > on track ;) > > I'm guessing that you have a damaged timezone database on your > system. What time zone does your system think it is in? What system > are you running on? I'm not seeing a problem on my Linux box running > 7.2 (well, except for the jump at the time zone boundary): > > lockhart=# select timestamp '2002-4-7 2:0:0.0'; > timestamptz > > 2002-04-07 01:00:00-08 > (1 row) > > But that is not the 2036 result you are seeing, so I can only > speculate on your specific problem... ACK! Hmm... fresh build of FreeBSD: $ uname -a FreeBSD ninja1.internal 4.5-STABLE FreeBSD 4.5-STABLE #0: Fri Apr 5 18:08:12 PST 2002 [EMAIL PROTECTED]:/opt/obj/opt/src/sys/NINJA i386 $ psql # SELECT timestamp '2002-4-7 2:0:0.0'; timestamptz 2036-06-02 22:57:08-07 (1 row) # SELECT version(); version PostgreSQL 7.2 on i386--freebsd4.5, compiled by GCC 2.95.3 (1 row) This isn't happy making. What OS are you running? Seems like a lower level problem. Do you know if it's a system call making the formatting call? -sc -- Sean Chittenden ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
Sean Chittenden wrote: >>>The FreeBSD folk are absolutely adamant about having mktime() no >>>compensate for deadzones between DST shifts and they insist that >>>the application handle this. Someone's off looking at how other >>>OS'es handle this, but this could be an arduous battle on that >>>front. <:~) >>> >>Personally I'd like to see FreeBSD do away with this strange >>behaviour. It cause my grief because certaint hings *MUST* be done >>at 0200 every day in our system, I was forced to do them manually >>recently, shifing several hours of work into daytime which had to be >>paused and bulked into the next days work. I realise that this is >>getting off track but it just points out that the FreeBSD behaviour >>is IMHO WRONG. It causes applications to fail in an unexpected and >>odd way. >> >>I'm not objecting to pg patching for it (no choice at the moment) >>but I hope the pg team 'officially' puts a little pressure on the >>BSD folk to make this behave as expected. >> > >Feel free to read over their arguments (archive may not be 100% up to >date): > >http://docs.freebsd.org/mail/archive/2002/freebsd-standards/20020414.freebsd-standards.html > >>I don't have any compliance docs at the moment, but this strikes me >>as somewhat out of spec personally. >> > >::shrug:: I've gotten enough push back to have an indifferent opinion: >I just want to see PG work w/ some of the bogus data I get every now >and then. :~) -sc > Yes but not everyone changes over at 2AM on the specific day. The rest of the world for the most part doesn't in fact. I don't know what mktime() behaviour is in different locales (IE different TZs) but if its the same (IE deadzone @ the same time when the TZ is something in say the EU who follow different rules) then its broken. I've got a FreeBSD 4.3 box here I do most of my serving on I'll see if I can get a little time to do some testing with different TZs. I don't think that the way BSD handles it is correct Also browsing the discussion archives it seems that mktime() atleast on BSD is inconsistent with how it handles bogus dates anyway. Looks like the BSD guys are going to be doing a little navel-looking over this. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
> >The FreeBSD folk are absolutely adamant about having mktime() no > >compensate for deadzones between DST shifts and they insist that > >the application handle this. Someone's off looking at how other > >OS'es handle this, but this could be an arduous battle on that > >front. <:~) > > Personally I'd like to see FreeBSD do away with this strange > behaviour. It cause my grief because certaint hings *MUST* be done > at 0200 every day in our system, I was forced to do them manually > recently, shifing several hours of work into daytime which had to be > paused and bulked into the next days work. I realise that this is > getting off track but it just points out that the FreeBSD behaviour > is IMHO WRONG. It causes applications to fail in an unexpected and > odd way. > > I'm not objecting to pg patching for it (no choice at the moment) > but I hope the pg team 'officially' puts a little pressure on the > BSD folk to make this behave as expected. Feel free to read over their arguments (archive may not be 100% up to date): http://docs.freebsd.org/mail/archive/2002/freebsd-standards/20020414.freebsd-standards.html > I don't have any compliance docs at the moment, but this strikes me > as somewhat out of spec personally. ::shrug:: I've gotten enough push back to have an indifferent opinion: I just want to see PG work w/ some of the bogus data I get every now and then. :~) -sc -- Sean Chittenden ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
Cuttign down the CC: list this time, apologies if I cut too much and someone misses a copy of this Sean Chittenden wrote: > >The FreeBSD folk are absolutely adamant about having mktime() no >compensate for deadzones between DST shifts and they insist that the >application handle this. Someone's off looking at how other OS'es >handle this, but this could be an arduous battle on that front. <:~) > Personally I'd like to see FreeBSD do away with this strange behaviour. It cause my grief because certaint hings *MUST* be done at 0200 every day in our system, I was forced to do them manually recently, shifing several hours of work into daytime which had to be paused and bulked into the next days work. I realise that this is getting off track but it just points out that the FreeBSD behaviour is IMHO WRONG. It causes applications to fail in an unexpected and odd way. I'm not objecting to pg patching for it (no choice at the moment) but I hope the pg team 'officially' puts a little pressure on the BSD folk to make this behave as expected. I don't have any compliance docs at the moment, but this strikes me as somewhat out of spec personally. >>I know the attached patch is something of a hack, but it works. I'm >>not totally wild about altering the original time object, but I >>don't know that I have a choice in this case. Does anyone switch >>timezones and only adjust their clocks by anything other than 60min? >>I seem to recall that happening in a few places, but the patch isn't >>any worse than where we are now. ::shrug:: This look like an okay >>patch? >> > >Are there any objections to the following? Instead of returning 0 or >utc, I could have it raise an error. Would that be acceptable? -sc > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
> Ugh, I'm too tired to file a gdb report:
>
> 1490t = mktime(tmp);
> (gdb)
> 1491fprintf(stderr, "%p\n", t);
> (gdb) print t
> $7 = -1
>
> Good call Tom. ... I'm going to file a PR w/ FreeBSD.
The FreeBSD folk are absolutely adamant about having mktime() no
compensate for deadzones between DST shifts and they insist that the
application handle this. Someone's off looking at how other OS'es
handle this, but this could be an arduous battle on that front. <:~)
> I know the attached patch is something of a hack, but it works. I'm
> not totally wild about altering the original time object, but I
> don't know that I have a choice in this case. Does anyone switch
> timezones and only adjust their clocks by anything other than 60min?
> I seem to recall that happening in a few places, but the patch isn't
> any worse than where we are now. ::shrug:: This look like an okay
> patch?
Are there any objections to the following? Instead of returning 0 or
utc, I could have it raise an error. Would that be acceptable? -sc
> Index: src/backend/utils/adt/datetime.c
> ===
> RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
> retrieving revision 1.88
> diff -u -r1.88 datetime.c
> --- src/backend/utils/adt/datetime.c 2002/02/25 16:17:04 1.88
> +++ src/backend/utils/adt/datetime.c 2002/04/10 06:12:45
> @@ -1439,6 +1439,7 @@
> DetermineLocalTimeZone(struct tm * tm)
> {
> int tz;
> + time_t t;
>
> if (HasCTZSet)
> tz = CTimeZone;
> @@ -1463,7 +1464,23 @@
> /* indicate timezone unknown */
> tmp->tm_isdst = -1;
>
> - mktime(tmp);
> + t = mktime(tmp);
> + if (t == -1)
> + {
> + /* Bump time up by an hour to see if time was an
> + * invalid time during a daylight savings switch */
> + tmp->tm_hour += 1;
> + t = mktime(tmp);
> +
> + /* Assume UTC if mktime() still fails.
> + *
> + * If mktime() was successful with the adjusted time,
> + * adjust the real time object. */
> + if (t == -1)
> + return 0;
> + else
> + tm->tm_hour += 1;
> + }
>
> tm->tm_isdst = tmp->tm_isdst;
>
--
Sean Chittenden
msg03995/pgp0.pgp
Description: PGP signature
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
> This is the bug report against glibc that prompted the change: > >http://bugs.gnu.org/cgi-bin/gnatsweb.pl?cmd=view%20audit-trail&database=default&pr=2738 > |> Ah, but this might explain why I've always seen on my Linux box a 1 > |> second offset returned from mktime() for dates before 1970. Everything > |> is shifted to allow -1 to be a special value I'll bet... > This is a joke, isn't it? Yes and no; the behavior is in localtime(), not mktime() -- sorry for my faulty memory. The case I am handling is in recovering local time given a time_t (in UTC of course). I have independently derived a broken-down time structure, so have both the original structure and the results of localtime() available in my code. Here is the relevant comment snippet: /* XXX HACK * Argh! My Linux box puts in a 1 second offset for dates less than 1970 * but only if the seconds field was non-zero. So, don't copy the seconds * field and instead carry forward from the original - thomas 97/06/18 * Note that GNU/Linux uses the standard freeware zic package as do * many other platforms so this may not be GNU/Linux/ix86-specific. * Still shows a problem on my up to date Linux box - thomas 2001-01-17 */ Believe it or not, the workaround works :) > |> Yikes. That is not currently acceptable (most platforms deployed in the > |> world *do* handle dates and times before 1970), but if I'm understanding > |> things correctly we will need to somehow reimplement the entire time and > |> time zone support system within PostgreSQL. I'll start looking at the > |> FreeBSD code to see what is available. *sigh* > Since POSIX says years before 1970 are undefined, it seems you are right. Well, "undefined" does not mean "impossible" or "disallowed". Presumably the standard was written to accomodate AIX, which has apparently never supported times before 1970. Of the other 25 or so platforms PostgreSQL runs on, only a very few machines do not support times before 1970 though I am sorry that I can not tell you which ones. It will be a list of rather odd machines; hopefully Linux will not join them. You might look to Solaris as an (imho) exemplary implementation of date/time support. Their time zone databases are quite detailed and specific for variations during years prior to 1960, including a bunch of weird years here in the US during the 1940's. >From my PoV (which is strongly influenced by the large amount of work I have done with dates and times, both in PostgreSQL and in my other work in astronomical and satellite tracking) supporting dates and times before 1970 is important. I'm an old guy, relatively speaking; my computers should at least be able to handle times back to my birthday!! ;) I sympathize with the inclination to look to standards when trying to resolve a problem (we do this in PostgreSQL also). In fact, time zone handling was controversial enough for the SQL standards people that they gave up and defined only constant interval offsets and ignore DST and other features of the real-world. But I can only imagine how many applications will be broken if this change actually makes it into production. I would strongly encourage you and your team to reconsider this change to fundamental glibc date/time support. Regards. - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
> > I don't think that our code checks explicitly for a "-1" return, since
> > the range is checked just before the call, but it would probably be a
> > good idea if it did
> As I noticd yesterday, glibc's mktime() has in the current snapshot
> been changed to return -1 for dates before the epoch. Our glibc guru
> (Cc'ed) told me, this is according to the standards (C and POSIX)
> which say, that time_t is undefined for dates prior the epoch, which
> to me seems obvoius, because otherwise the error return couldn't be
> distinguished from the time_t value "one second before the epoch").
??!! I'm sorry that I don't remember the exact context here (didn't this
thread start on a FreeBSD amchine?), but are you saying that glibc
shipped with Linux will potentially stop supporting times and time zones
before 1970?
Standard or not, there is a *long* history of all decent implementations
supporting dates prior to 1970, and platforms which do not do so (AIX?)
have always been a source of scorn and derision. Really.
Ah, but this might explain why I've always seen on my Linux box a 1
second offset returned from mktime() for dates before 1970. Everything
is shifted to allow -1 to be a special value I'll bet...
> This change causes some of the regression tests to fail ('abstime',
> 'tinterval', and 'horology'). All failures occur on dates that are
> given in PST, lay between 1900 and 1970, and show a difference of 8
> hour (regression.diffs attached).
Sure.
> I've added code to DetermineLocalTimeZone that elogs and ERROR if
> mktime returns < 0, which showed, that this also happens in some other
> tests, but without affecting the results there (maybe pure luck?).
Yikes. That is not currently acceptable (most platforms deployed in the
world *do* handle dates and times before 1970), but if I'm understanding
things correctly we will need to somehow reimplement the entire time and
time zone support system within PostgreSQL. I'll start looking at the
FreeBSD code to see what is available. *sigh*
- Thomas
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
Hi,
On Tue, 9 Apr 2002 at 19:43, Thomas Lockhart wrote:
> I don't think that our code checks explicitly for a "-1" return, since
> the range is checked just before the call, but it would probably be a
> good idea if it did
Indeed.
As I noticd yesterday, glibc's mktime() has in the current snapshot
been changed to return -1 for dates before the epoch. Our glibc guru
(Cc'ed) told me, this is according to the standards (C and POSIX)
which say, that time_t is undefined for dates prior the epoch, which
to me seems obvoius, because otherwise the error return couldn't be
distinguished from the time_t value "one second before the epoch").
This change causes some of the regression tests to fail ('abstime',
'tinterval', and 'horology'). All failures occur on dates that are
given in PST, lay between 1900 and 1970, and show a difference of 8
hour (regression.diffs attached).
I've added code to DetermineLocalTimeZone that elogs and ERROR if
mktime returns < 0, which showed, that this also happens in some other
tests, but without affecting the results there (maybe pure luck?).
cu
Reinhard
*** ./expected/abstime.out Wed Nov 21 18:27:25 2001
--- ./results/abstime.out Thu Apr 11 12:38:51 2002
***
*** 44,50
| Wed Dec 31 16:00:00 1969 PST
| infinity
| -infinity
!| Sat May 10 23:59:12 1947 PST
| invalid
(7 rows)
--- 44,50
| Wed Dec 31 16:00:00 1969 PST
| infinity
| -infinity
!| Sat May 10 15:59:12 1947 PST
| invalid
(7 rows)
***
*** 56,62
| Mon May 01 00:30:30 1995 PDT
| Wed Dec 31 16:00:00 1969 PST
| -infinity
! | Sat May 10 23:59:12 1947 PST
(5 rows)
SELECT '' AS six, ABSTIME_TBL.*
--- 56,62
| Mon May 01 00:30:30 1995 PDT
| Wed Dec 31 16:00:00 1969 PST
| -infinity
! | Sat May 10 15:59:12 1947 PST
(5 rows)
SELECT '' AS six, ABSTIME_TBL.*
***
*** 67,73
| Mon May 01 00:30:30 1995 PDT
| Wed Dec 31 16:00:00 1969 PST
| infinity
! | Sat May 10 23:59:12 1947 PST
| invalid
(6 rows)
--- 67,73
| Mon May 01 00:30:30 1995 PDT
| Wed Dec 31 16:00:00 1969 PST
| infinity
! | Sat May 10 15:59:12 1947 PST
| invalid
(6 rows)
***
*** 89,95
---+--
| Wed Dec 31 16:00:00 1969 PST
| -infinity
!| Sat May 10 23:59:12 1947 PST
(3 rows)
SELECT '' AS four, ABSTIME_TBL.*
--- 89,95
---+--
| Wed Dec 31 16:00:00 1969 PST
| -infinity
!| Sat May 10 15:59:12 1947 PST
(3 rows)
SELECT '' AS four, ABSTIME_TBL.*
***
*** 99,105
| Sun Jan 14 03:14:21 1973 PST
| Wed Dec 31 16:00:00 1969 PST
| -infinity
! | Sat May 10 23:59:12 1947 PST
(4 rows)
SELECT '' AS four, ABSTIME_TBL.*
--- 99,105
| Sun Jan 14 03:14:21 1973 PST
| Wed Dec 31 16:00:00 1969 PST
| -infinity
! | Sat May 10 15:59:12 1947 PST
(4 rows)
SELECT '' AS four, ABSTIME_TBL.*
***
*** 121,127
ORDER BY abstime;
four | abstime| year | month | day | hour | minute | second
--+--+--+---+-+--++
! | Sat May 10 23:59:12 1947 PST | 1947 | 5 | 10 | 23 | 59 | 12
| Wed Dec 31 16:00:00 1969 PST | 1969 |12 | 31 | 16 | 0 | 0
| Sun Jan 14 03:14:21 1973 PST | 1973 | 1 | 14 |3 | 14 | 21
| Mon May 01 00:30:30 1995 PDT | 1995 | 5 | 1 |0 | 30 | 30
--- 121,127
ORDER BY abstime;
four | abstime| year | month | day | hour | minute | second
--+--+--+---+-+--++
! | Sat May 10 15:59:12 1947 PST | 1947 | 5 | 10 | 15 | 59 | 12
| Wed Dec 31 16:00:00 1969 PST | 1969 |12 | 31 | 16 | 0 | 0
| Sun Jan 14 03:14:21 1973 PST | 1973 | 1 | 14 |3 | 14 | 21
| Mon May 01 00:30:30 1995 PDT | 1995 | 5 | 1 |0 | 30 | 30
==
*** ./expected/tinterval.outFri Sep 28 07:59:53 2001
--- ./results/tinterval.out Thu Apr 11 12:38:51 2002
***
*** 26,32
five | f1
--+-
| ["-infinity" "infinity"]
! | ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
> > Good call Tom. ... I'm going to file a PR w/ FreeBSD. I know > > the attached patch is something of a hack, but it works. I'm not > > totally wild about altering the original time object, but I don't > > know that I have a choice in this case. Does anyone switch > > timezones and only adjust their clocks by anything other than > > 60min? I seem to recall that happening in a few places, but the > > patch isn't any worse than where we are now. ::shrug:: This look > > like an okay patch? > > Yuck. You are trying the right things, but I'm not sure that we > should allow mktime() to fail for special cases like this. Falling > back to GMT (a potential offset of up to 12 hours from what was > intended by the user) is (perhaps) unacceptably ugly, particularly > for recent/current epochs which would be reasonably expected to > behave correctly. > > I'm inclined to test for an error return from mktime(), or to test > it as an ASSERT(), and then throw an error, rather than passing > along garbaged-up values. > > Comments? I thought about throwing an exception but noticed a few lines down that the code was returning GMT on failure. When in Rome, do as the Romans do. :~) I personally think that ASSERT should be called after the 2nd mktime() fails but think that calling ASSERT would break existing applications though maybe not: I've just had a 6hr crash course in this code and don't quite have the incite to say one way or another. FWIW, I've lobbed something off to the FreeBSD crowd asking if mktime() should be updated in the system libraries but don't think that'll fix things "soon enough." -sc -- Sean Chittenden msg03973/pgp0.pgp Description: PGP signature
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
... > Good call Tom. ... I'm going to file a PR w/ FreeBSD. I know the > attached patch is something of a hack, but it works. I'm not totally > wild about altering the original time object, but I don't know that I > have a choice in this case. Does anyone switch timezones and only > adjust their clocks by anything other than 60min? I seem to recall > that happening in a few places, but the patch isn't any worse than > where we are now. ::shrug:: This look like an okay patch? Yuck. You are trying the right things, but I'm not sure that we should allow mktime() to fail for special cases like this. Falling back to GMT (a potential offset of up to 12 hours from what was intended by the user) is (perhaps) unacceptably ugly, particularly for recent/current epochs which would be reasonably expected to behave correctly. I'm inclined to test for an error return from mktime(), or to test it as an ASSERT(), and then throw an error, rather than passing along garbaged-up values. Comments? - Thomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
> > Ehh... let me hack/check. Looks like 11. ?? In
> > lib/libc/stdtime/localtime.c, WRONG is defined as -1, not 11.
>
> > 1490t = mktime(tmp);
> > (gdb)
> > 1491fprintf(stderr, "%p\n", t); /* GCC optimizes this
> > away if I don't do
> > something */
> > (gdb)
> > 0x3c5e5ba0
> > (gdb) print t
> > $1 = 11
>
> > Doesn't make much sense to me where that'd come from... ? -sc
>
> I'd be inclined to believe the 0x3c5e5ba0 (= Mon Feb 04 2002,
> 05:00:00 EST according to my local time code) and not the 11. I
> think gdb is dropping the ball here; most likely, failing to warn
> you that the register that once held t wasn't preserved over the
> fprintf function call.
Ugh, I'm too tired to file a gdb report:
1490t = mktime(tmp);
(gdb)
1491fprintf(stderr, "%p\n", t);
(gdb) print t
$7 = -1
Good call Tom. ... I'm going to file a PR w/ FreeBSD. I know the
attached patch is something of a hack, but it works. I'm not totally
wild about altering the original time object, but I don't know that I
have a choice in this case. Does anyone switch timezones and only
adjust their clocks by anything other than 60min? I seem to recall
that happening in a few places, but the patch isn't any worse than
where we are now. ::shrug:: This look like an okay patch?
backend> delete from tt;
blank
1: ctid(typeid = 27, len = 6, typmod = -1, byval = f)
backend> insert into tt values ('2002-4-7 2:0:0.0');
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
backend> insert into tt values ('2002-4-7 2:45:0.0');
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
backend> insert into tt values ('2002-4-7 1:0:0.0');
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
backend> insert into tt values ('2002-4-7 3:0:0.0');
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
backend> select * from tt;
blank
1: tt (typeid = 1184, len = 8, typmod = -1, byval = f)
1: tt = "2002-04-07 03:00:00-07" (typeid = 1184, len = 8, typmod = -1,
byval = f)
1: tt = "2002-04-07 03:45:00-07" (typeid = 1184, len = 8, typmod = -1,
byval = f)
1: tt = "2002-04-07 01:00:00-08" (typeid = 1184, len = 8, typmod = -1,
byval = f)
1: tt = "2002-04-07 03:00:00-07" (typeid = 1184, len = 8, typmod = -1,
byval = f)
-sc
--
Sean Chittenden
Index: src/backend/utils/adt/datetime.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.88
diff -u -r1.88 datetime.c
--- src/backend/utils/adt/datetime.c2002/02/25 16:17:04 1.88
+++ src/backend/utils/adt/datetime.c2002/04/10 06:12:45
@@ -1439,6 +1439,7 @@
DetermineLocalTimeZone(struct tm * tm)
{
int tz;
+ time_t t;
if (HasCTZSet)
tz = CTimeZone;
@@ -1463,7 +1464,23 @@
/* indicate timezone unknown */
tmp->tm_isdst = -1;
- mktime(tmp);
+ t = mktime(tmp);
+ if (t == -1)
+ {
+ /* Bump time up by an hour to see if time was an
+* invalid time during a daylight savings switch */
+ tmp->tm_hour += 1;
+ t = mktime(tmp);
+
+ /* Assume UTC if mktime() still fails.
+*
+* If mktime() was successful with the adjusted time,
+* adjust the real time object. */
+ if (t == -1)
+ return 0;
+ else
+ tm->tm_hour += 1;
+ }
tm->tm_isdst = tmp->tm_isdst;
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
Sean Chittenden <[EMAIL PROTECTED]> writes: > Ehh... let me hack/check. Looks like 11. ?? In > lib/libc/stdtime/localtime.c, WRONG is defined as -1, not 11. > 1490t = mktime(tmp); > (gdb) > 1491fprintf(stderr, "%p\n", t); /* GCC optimizes this > away if I don't do > something */ > (gdb) > 0x3c5e5ba0 > (gdb) print t > $1 = 11 > Doesn't make much sense to me where that'd come from... ? -sc I'd be inclined to believe the 0x3c5e5ba0 (= Mon Feb 04 2002, 05:00:00 EST according to my local time code) and not the 11. I think gdb is dropping the ball here; most likely, failing to warn you that the register that once held t wasn't preserved over the fprintf function call. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
> > Looks like it's a "bug" in mktime() on FreeBSD: it doesn't seem to > > do so well with invalid times that happen between daylight savings > > time... or is that a postgres thing for not kicking up an error > > (out of bounds time)? Or should 2am PST be converted to 3am? -sc > > Here is the man page on Linux: > > The mktime() function converts a broken-down time structure, > expressed as local time, to calendar time representation. The > function ignores the specified contents of the structure members > tm_wday and tm_yday and recomputes them from the other information > in the broken-down time structure. If structure members are outside > their legal interval, they will be normalized (so that, e.g., 40 > October is changed into 9 November). Calling mktime() also sets the > external variable tzname with information about the current time > zone. If the specified broken-down time cannot be represented as > calendar time (seconds since the epoch), mktime() returns a value of > (time_t)(-1) and does not alter the tm_wday and tm_yday members of > the broken-down time structure. > > > Does that look similar to FreeBSD? Very familiar, from mktime(2): The functions mktime() and timegm() convert the broken-down time in the structure pointed to by tm into a time value with the same encoding as that of the values returned by the time(3) function (that is, seconds from the Epoch, UTC). mktime() interprets the input structure according to the current timezone setting (see tzset(3)). timegm() interprets the input structure as representing Universal Coordinated Time (UTC). The original values of the tm_wday and tm_yday components of the struc- ture are ignored, and the original values of the other components are not restricted to their normal ranges, and will be normalized if needed. For example, October 40 is changed into November 9, a tm_hour of -1 means 1 hour before midnight, tm_mday of 0 means the day preceding the current month, and tm_mon of -2 means 2 months before January of tm_year. (A positive or zero value for tm_isdst causes mktime() to presume initially that summer time (for example, Daylight Saving Time) is or is not in effect for the specified time, respectively. A negative value for tm_isdst causes the mktime() function to attempt to divine whether summer time is in effect for the specified time. The tm_isdst and tm_gmtoff members are forced to zero by timegm().) > I don't think that our code checks explicitly for a "-1" return, > since the range is checked just before the call, but it would > probably be a good idea if it did (assuming that other mktime() > implementations had the same convention for an error return of > course). Just poked through how Ruby handles this and it looks like they go to reasonable lengths to make sure that it "does the right thing." http://www.ruby-lang.org/~knu/cgi-bin/cvsweb.cgi/ruby/time.c?rev=1.55&content-type=text/x-cvsweb-markup irb(main):005:0> Time.local(2002,4,7,1) Sun Apr 07 01:00:00 PST 2002 irb(main):006:0> Time.local(2002,4,7,3) Sun Apr 07 03:00:00 PDT 2002 irb(main):007:0> Time.local(2002,4,7,2) Sun Apr 07 03:00:00 PDT 2002 irb(main):008:0> Time.local(2002,4,7,2,20) Sun Apr 07 03:20:00 PDT 2002 It's artistically licensed... ::shrug:: Time.local is a thin wrapper around mktime(). Check out make_time_t() in the link above. > This is the first report I can remember in 6 years of this > particular symptom, and I have the strong feeling that no matter > what we end up doing there *is* a problem with the FreeBSD database > of time zones or (possibly) in its implementation of mktime(). I hope so... this bug hit me nasty like. I was doing a time series regression and thought it'd be a cute exercise to sum up the components... when I hit only ~99.7% and found out that part of my data was in 2036 I... flipped, freaked out, debugged, cursed, scratched head, cursed more... then I went for a Guinness and my world was calm again. :~) > What do you see as the return value from mktime()? Ehh... let me hack/check. Looks like 11. ?? In lib/libc/stdtime/localtime.c, WRONG is defined as -1, not 11. 1490t = mktime(tmp); (gdb) 1491fprintf(stderr, "%p\n", t); /* GCC optimizes this away if I don't do something */ (gdb) 0x3c5e5ba0 (gdb) print t $1 = 11 Doesn't make much sense to me where that'd come from... ? -sc -- Sean Chittenden ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
Sean Chittenden <[EMAIL PROTECTED]> writes: > Looks like it's a "bug" in mktime() on FreeBSD: it doesn't seem to do > so well with invalid times that happen between daylight savings > time... or is that a postgres thing for not kicking up an error (out > of bounds time)? Or should 2am PST be converted to 3am? -sc We've seen a *lot* of problems on various platforms with mktime misbehaving on corner cases ... although I have to admit that failures in PST/PDT zone are a new one on me (at least from an American perspective, that ain't exactly a corner case). The DetermineLocalTimeZone() routine in datetime.c is supposed to try to defend against the more common forms of mktime brain-damage. Perhaps you can suggest a way of improving it to work around this FreeBSD problem. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
On FreeBSD newsyslog shows the same interesting sort of problem witha
'time' value of @T02 on the day of the leap change, sudden;y it'll balk
saying the format of the line is wrong. Could be related on an outside
area as newsyslog uses mktime and some ISO time format.
Sean Chittenden wrote:
>Err... brain-o on my part (didn't know what I was looking for until I
>put in a date that does exist and followed it through):
>
>>(gdb) b DecodeDateTime
>>Breakpoint 1 at 0x811568d: file datetime.c, line 892.
>>(gdb) b DetermineLocalTimeZone
>>Breakpoint 2 at 0x81161a9: file datetime.c, line 1463.
>>(gdb) run foo
>>
>>backend> create table tt ( tt timestamp );
>>backend> insert into tt values ('2002-4-7 2:0:0.0');
>>
>
>If I use 3am on the 7th, I get the following:
>
>(gdb) print *tm
>$2 = {tm_sec = 0, tm_min = 0, tm_hour = 3, tm_mday = 7, tm_mon = 3,
> tm_year = 102, tm_wday = 0, tm_yday = 96, tm_isdst = 1,
> tm_gmtoff = -25200, tm_zone = 0x28420c78 "PDT"}
>
>Looks like it's a "bug" in mktime() on FreeBSD: it doesn't seem to do
>so well with invalid times that happen between daylight savings
>time... or is that a postgres thing for not kicking up an error (out
>of bounds time)? Or should 2am PST be converted to 3am? -sc
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
> Looks like it's a "bug" in mktime() on FreeBSD: it doesn't seem to do > so well with invalid times that happen between daylight savings > time... or is that a postgres thing for not kicking up an error (out > of bounds time)? Or should 2am PST be converted to 3am? -sc Here is the man page on Linux: The mktime() function converts a broken-down time structure, expressed as local time, to calendar time representation. The function ignores the specified contents of the structure members tm_wday and tm_yday and recomputes them from the other information in the broken-down time structure. If structure members are outside their legal interval, they will be normalized (so that, e.g., 40 October is changed into 9 November). Calling mktime() also sets the external variable tzname with information about the current time zone. If the specified broken-down time cannot be represented as calendar time (seconds since the epoch), mktime() returns a value of (time_t)(-1) and does not alter the tm_wday and tm_yday members of the broken-down time structure. Does that look similar to FreeBSD? I don't think that our code checks explicitly for a "-1" return, since the range is checked just before the call, but it would probably be a good idea if it did (assuming that other mktime() implementations had the same convention for an error return of course). This is the first report I can remember in 6 years of this particular symptom, and I have the strong feeling that no matter what we end up doing there *is* a problem with the FreeBSD database of time zones or (possibly) in its implementation of mktime(). What do you see as the return value from mktime()? - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
Err... brain-o on my part (didn't know what I was looking for until I
put in a date that does exist and followed it through):
> (gdb) b DecodeDateTime
> Breakpoint 1 at 0x811568d: file datetime.c, line 892.
> (gdb) b DetermineLocalTimeZone
> Breakpoint 2 at 0x81161a9: file datetime.c, line 1463.
> (gdb) run foo
>
> backend> create table tt ( tt timestamp );
> backend> insert into tt values ('2002-4-7 2:0:0.0');
If I use 3am on the 7th, I get the following:
(gdb) print *tm
$2 = {tm_sec = 0, tm_min = 0, tm_hour = 3, tm_mday = 7, tm_mon = 3,
tm_year = 102, tm_wday = 0, tm_yday = 96, tm_isdst = 1,
tm_gmtoff = -25200, tm_zone = 0x28420c78 "PDT"}
Looks like it's a "bug" in mktime() on FreeBSD: it doesn't seem to do
so well with invalid times that happen between daylight savings
time... or is that a postgres thing for not kicking up an error (out
of bounds time)? Or should 2am PST be converted to 3am? -sc
--
Sean Chittenden
msg03965/pgp0.pgp
Description: PGP signature
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
> > PostgreSQL 7.2 on i386--freebsd4.5, compiled by GCC 2.95.3 This
> > isn't happy making. What OS are you running? Seems like a lower
> > level problem. Do you know if it's a system call making the
> > formatting call?
>
> PostgreSQL uses system calls to get the current time zone if it is
> not specified in the input string.
I'm inclined to agree after having stepped through things.
> I'm running a fairly new Linux (Mandrake distro), which has the zinc
> package as part of glibc-2.2.4
The what package? <:~)
> Do you have another way to verify your time zone setup? Do you have
> the "zdump" command to look at your time zone info?
It appears to be correct:
$ date
Tue Apr 9 14:40:51 PDT 2002
$ zdump
$ zdump PST PSD GMT CST
PST Tue Apr 9 21:40:15 2002 GMT
PSD Tue Apr 9 21:40:15 2002 GMT
GMT Tue Apr 9 21:40:15 2002 GMT
CST Tue Apr 9 21:40:15 2002 GMT
$ zdump -v PST PSD GMT CST
PST Fri Dec 13 20:45:52 1901 UTC = Fri Dec 13 20:45:52 1901 GMT isdst=0 gmtoff=0
PST Sat Dec 14 20:45:52 1901 UTC = Sat Dec 14 20:45:52 1901 GMT isdst=0 gmtoff=0
PST Mon Jan 18 03:14:07 2038 UTC = Mon Jan 18 03:14:07 2038 GMT isdst=0 gmtoff=0
PST Tue Jan 19 03:14:07 2038 UTC = Tue Jan 19 03:14:07 2038 GMT isdst=0 gmtoff=0
PSD Fri Dec 13 20:45:52 1901 UTC = Fri Dec 13 20:45:52 1901 GMT isdst=0 gmtoff=0
PSD Sat Dec 14 20:45:52 1901 UTC = Sat Dec 14 20:45:52 1901 GMT isdst=0 gmtoff=0
PSD Mon Jan 18 03:14:07 2038 UTC = Mon Jan 18 03:14:07 2038 GMT isdst=0 gmtoff=0
PSD Tue Jan 19 03:14:07 2038 UTC = Tue Jan 19 03:14:07 2038 GMT isdst=0 gmtoff=0
GMT Fri Dec 13 20:45:52 1901 UTC = Fri Dec 13 20:45:52 1901 GMT isdst=0 gmtoff=0
GMT Sat Dec 14 20:45:52 1901 UTC = Sat Dec 14 20:45:52 1901 GMT isdst=0 gmtoff=0
GMT Mon Jan 18 03:14:07 2038 UTC = Mon Jan 18 03:14:07 2038 GMT isdst=0 gmtoff=0
GMT Tue Jan 19 03:14:07 2038 UTC = Tue Jan 19 03:14:07 2038 GMT isdst=0 gmtoff=0
CST Fri Dec 13 20:45:52 1901 UTC = Fri Dec 13 20:45:52 1901 GMT isdst=0 gmtoff=0
CST Sat Dec 14 20:45:52 1901 UTC = Sat Dec 14 20:45:52 1901 GMT isdst=0 gmtoff=0
CST Mon Jan 18 03:14:07 2038 UTC = Mon Jan 18 03:14:07 2038 GMT isdst=0 gmtoff=0
CST Tue Jan 19 03:14:07 2038 UTC = Tue Jan 19 03:14:07 2038 GMT isdst=0 gmtoff=0
> If you want to step through your PostgreSQL code, I could give you
> some suggestions on what to look for:
[snip]
(gdb) b DecodeDateTime
Breakpoint 1 at 0x811568d: file datetime.c, line 892.
(gdb) b DetermineLocalTimeZone
Breakpoint 2 at 0x81161a9: file datetime.c, line 1463.
(gdb) run foo
Starting program:
/opt/ports/databases/postgresql7/work/postgresql-7.2/src/backend/postgres foo
DEBUG: database system was shut down at 2002-04-09 14:42:06 PDT
DEBUG: checkpoint record is at 0/12B514
DEBUG: redo record is at 0/12B514; undo record is at 0/0; shutdown TRUE
DEBUG: next transaction id: 109; next oid: 32942
DEBUG: database system is ready
POSTGRES backend interactive interface
$Revision: 1.245 $ $Date: 2002/01/10 01:11:45 $
backend> create table tt ( tt timestamp );
backend> insert into tt values ('2002-4-7 2:0:0.0');
Breakpoint 1, DecodeDateTime (field=0xbfbff670, ftype=0xbfbff60c, nf=2,
dtype=0xbfbff5c4, tm=0xbfbff6d4, fsec=0xbfbff5c8, tzp=0xbfbff5d0)
at datetime.c:892
warning: Source file is more recent than executable.
892 {
(gdb) c
Continuing.
Breakpoint 2, DetermineLocalTimeZone (tm=0xbfbff6d4) at datetime.c:1463
1463{
(gdb) s
1466if (HasCTZSet)
(gdb) n
1468else if (IS_VALID_UTIME(tm->tm_year, tm->tm_mon, tm->tm_mday))
(gdb) print *tm
$1 = {tm_sec = 0, tm_min = 0, tm_hour = 2, tm_mday = 7, tm_mon = 4,
tm_year = 2002, tm_wday = -1077938292, tm_yday = 138716656, tm_isdst = -1,
tm_gmtoff = -1077938388, tm_zone = 0xbfbff72c "\214÷¿¿"}
[snip]
1515return tz;
(gdb) print tz
$2 = 1077938388
(gdb) print *tm
$3 = {tm_sec = 0, tm_min = 0, tm_hour = 2, tm_mday = 7, tm_mon = 4,
tm_year = 2002, tm_wday = -1077938292, tm_yday = 138716656, tm_isdst = 0,
tm_gmtoff = -1077938388, tm_zone = 0xbfbff72c "\214÷¿¿"}
(gdb) n
DecodeDateTime (field=0xbfbff670, ftype=0xbfbff60c, nf=2, dtype=0xbfbff5c4,
tm=0xbfbff6d4, fsec=0xbfbff5c8, tzp=0xbfbff5d0) at datetime.c:1448
1448return 0;
(gdb) print *tm
$4 = {tm_sec = 0, tm_min = 0, tm_hour = 2, tm_mday = 7, tm_mon = 4,
tm_year = 2002, tm_wday = -1077938292, tm_yday = 138716656, tm_isdst = 0,
tm_gmtoff = -1077938388, tm_zone = 0xbfbff72c "\214÷¿¿"}
It looks as though the data is getting parsed correctly. Could it be
that the data is getting written incorrectly?
[further down in the gdb session]
OidFunctionCall3 (functionId=1150, arg1=139024360, arg2=0, arg3=4294967295)
at fmgr.c:1193
[snip]
1197return result;
(gdb) n
0x80a40e3 in stringTypeDatum (tp=0x847ee00,
string=0x84957e8 "2002-4-7 2:0:0.0", atttypmod=-1) at parse_type.c:181
181 return OidFunctionCall3(op,
(gdb) n
coerce_type (pstate=0x8495288, node=0x8495430, inputTypeId=705,
targetTypeId=1184, atttypmod=-1)
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
> $ uname -a > FreeBSD ninja1.internal 4.5-STABLE FreeBSD 4.5-STABLE #0: Fri Apr 5 18:08:12 PST >2002 [EMAIL PROTECTED]:/opt/obj/opt/src/sys/NINJA i386 > $ psql > # SELECT timestamp '2002-4-7 2:0:0.0'; > timestamptz > > 2036-06-02 22:57:08-07 > # SELECT version(); > version > > PostgreSQL 7.2 on i386--freebsd4.5, compiled by GCC 2.95.3 > This isn't happy making. What OS are you running? Seems like a lower > level problem. Do you know if it's a system call making the > formatting call? PostgreSQL uses system calls to get the current time zone if it is not specified in the input string. I'm running a fairly new Linux (Mandrake distro), which has the zinc package as part of glibc-2.2.4 Do you have another way to verify your time zone setup? Do you have the "zdump" command to look at your time zone info? If you want to step through your PostgreSQL code, I could give you some suggestions on what to look for: 1) run the backend using "gdb postgres" 2) before telling gdb to "run", set a breakpoint on DecodeDateTime and DetermineLocalTimeZone. Use "b DecodeDateTime". 3) run the program. Use "run " where might be your user name. 4) at the prompt, type in the above query. 5) at first breakpoint, continue by typing "c". 6) at the DetermineLocalTimeZone breakpoint, start stepping through code with "s". Check values as you go along. hth - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed incorrectly...
> > date/time storage problem: timestamp parsed incorrectly... > > > It looks like a bad parser or defaults for time values. The > > example code below explains the problem best. I'm not sure why, > > or where... but it took me about a day to track down (PostgreSQL > > is never wrong!). If I include a timezone, things seem to work. > > For some reason, only dates from yesterday and today break > > things... I think it's because -7 is the same as my timezone, PST > > (now -7). > > Well, as long as you realize that PostgreSQL is always right you are > on track ;) > > I'm guessing that you have a damaged timezone database on your > system. What time zone does your system think it is in? What system > are you running on? I'm not seeing a problem on my Linux box running > 7.2 (well, except for the jump at the time zone boundary): > > lockhart=# select timestamp '2002-4-7 2:0:0.0'; > timestamptz > > 2002-04-07 01:00:00-08 > (1 row) > > But that is not the 2036 result you are seeing, so I can only > speculate on your specific problem... ACK! Hmm... fresh build of FreeBSD: $ uname -a FreeBSD ninja1.internal 4.5-STABLE FreeBSD 4.5-STABLE #0: Fri Apr 5 18:08:12 PST 2002 [EMAIL PROTECTED]:/opt/obj/opt/src/sys/NINJA i386 $ psql # SELECT timestamp '2002-4-7 2:0:0.0'; timestamptz 2036-06-02 22:57:08-07 (1 row) # SELECT version(); version PostgreSQL 7.2 on i386--freebsd4.5, compiled by GCC 2.95.3 (1 row) This isn't happy making. What OS are you running? Seems like a lower level problem. Do you know if it's a system call making the formatting call? -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Bug #630: date/time storage problem: timestamp parsed
> date/time storage problem: timestamp parsed incorrectly... > It looks like a bad parser or defaults for time values. The example code below >explains the problem best. I'm not sure why, or where... but it took me about a day >to track down (PostgreSQL is never wrong!). If I include a timezone, things seem to >work. For some reason, only dates from yesterday and today break things... I think >it's because -7 is the same as my timezone, PST (now -7). Well, as long as you realize that PostgreSQL is always right you are on track ;) I'm guessing that you have a damaged timezone database on your system. What time zone does your system think it is in? What system are you running on? I'm not seeing a problem on my Linux box running 7.2 (well, except for the jump at the time zone boundary): lockhart=# select timestamp '2002-4-7 2:0:0.0'; timestamptz 2002-04-07 01:00:00-08 (1 row) But that is not the 2036 result you are seeing, so I can only speculate on your specific problem... - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] Bug #630: date/time storage problem: timestamp parsed incorrectly...
Sean Chittenden ([EMAIL PROTECTED]) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
date/time storage problem: timestamp parsed incorrectly...
Long Description
It looks like a bad parser or defaults for time values. The example code below
explains the problem best. I'm not sure why, or where... but it took me about a day
to track down (PostgreSQL is never wrong!). If I include a timezone, things seem to
work. For some reason, only dates from yesterday and today break things... I think
it's because -7 is the same as my timezone, PST (now -7).
Sample Code
CREATE TABLE timestamp_test (
utc_timestamp TIMESTAMP NOT NULL
);
INSERT INTO timestamp_tmp VALUES ('2002-4-7 2:0:0.0');
SELECT * from timestamp_tmp;
utc_date
2036-06-02 22:55:24-07
(1 row)
INSERT INTO timestamp_tmp VALUES ('2002-4-7 -8 2:0:0.0');
SELECT * from timestamp_tmp;
utc_date
2036-06-02 22:55:24-07
2002-04-07 03:00:00-07
(2 rows)
No file was uploaded with this report
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
