Thread: possible time change issue - known problem?
I suspect this may be a known problem, but just in case...
This problem occurs under 7.2.3 but not 7.3.2.
Today I found a problem with a stats function which is run every day
at midnight. For what it's worth, the time changed on Sunday
morning. The symptom is this error message:
Cannot insert a duplicate key into unique index
daily_stats_data_unique
The index involves a date field.
Looking at the code, I suspect the problem is related to this problem
which I can duplicate on my current system: PostgreSQL 7.2.3 on i386-
portbld-freebsd4.6, compiled by GCC 2.95.4
# select current_date, (current_date - interval '1 day')::date;
date | date
------------+------------
2003-04-07 | 2003-04-05
I expect the answer to be 2003-04-06 (i.e. yesterday's date).
But this seems to work:
select now(), (now() - interval '1 day')::date;
now | date
-------------------------------+------------
2003-04-07 08:08:08.360088-04 | 2003-04-06
Why would this suddenly stop working?
The problem does not occur on a 7.3.2 system:
# select current_date, (current_date - interval '1 day')::date;
date | date
------------+------------
2003-04-07 | 2003-04-06
(1 row)
# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.3.2 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
(1 row)
--
Dan Langille : http://www.langille.org/
On Mon, Apr 07, 2003 at 08:19:57AM -0400, Dan Langille wrote:
> Looking at the code, I suspect the problem is related to this problem
> which I can duplicate on my current system: PostgreSQL 7.2.3 on i386-
> portbld-freebsd4.6, compiled by GCC 2.95.4
>
> # select current_date, (current_date - interval '1 day')::date;
> date | date
> ------------+------------
> 2003-04-07 | 2003-04-05
>
> I expect the answer to be 2003-04-06 (i.e. yesterday's date).
Out of curiosity, would this weekend be the day you switched to/from
daylight savings time? Then there were only 23 hours in the day, so 1 day
ago was actually the 5th.
Anyway, why not just:
select current_date, current_date-1;
date | ?column?
------------+------------
2003-04-07 | 2003-04-06
(1 row)
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington
Attachment
On 7 Apr 2003 at 22:43, Martijn van Oosterhout wrote: > On Mon, Apr 07, 2003 at 08:19:57AM -0400, Dan Langille wrote: > > Looking at the code, I suspect the problem is related to this problem > > which I can duplicate on my current system: PostgreSQL 7.2.3 on i386- > > portbld-freebsd4.6, compiled by GCC 2.95.4 > > > > # select current_date, (current_date - interval '1 day')::date; > > date | date > > ------------+------------ > > 2003-04-07 | 2003-04-05 > > > > I expect the answer to be 2003-04-06 (i.e. yesterday's date). > > Out of curiosity, would this weekend be the day you switched to/from > daylight savings time? Then there were only 23 hours in the day, so 1 day > ago was actually the 5th. Yes, as hinted in the message subject. Hmmm, so that's how it's doing the math. I would think '24 hours' would give a different answer to '1 day' since '1 day' is not necessarily == '24 hours'. > Anyway, why not just: > > select current_date, current_date-1; > date | ?column? > ------------+------------ > 2003-04-07 | 2003-04-06 > (1 row) Nice. Thanks. -- Dan Langille : http://www.langille.org/
"Dan Langille" <dan@langille.org> writes:
> Yes, as hinted in the message subject. Hmmm, so that's how it's
> doing the math. I would think '24 hours' would give a different
> answer to '1 day' since '1 day' is not necessarily == '24 hours'.
Type INTERVAL knows about months and seconds, nothing else. I've
opined in the past that it should be months, days, and seconds,
but no one seems excited enough about the issue to do the nontrivial
work involved ...
regards, tom lane
On 7 Apr 2003 at 10:32, Tom Lane wrote: > "Dan Langille" <dan@langille.org> writes: > > Yes, as hinted in the message subject. Hmmm, so that's how it's > > doing the math. I would think '24 hours' would give a different > > answer to '1 day' since '1 day' is not necessarily == '24 hours'. > > Type INTERVAL knows about months and seconds, nothing else. Hmmm, months and seconds only. Then is the documentation wrong? 5.5.1.4. Intervals interval values can be written with the following syntax: Quantity Unit [Quantity Unit...] [Direction] @ Quantity Unit [Quantity Unit...] [Direction] where: Quantity is a number (possibly signed), Unit is second, minute, hour, day, week, month, year, decade, century, millennium, or abbreviations or plurals of these units; Direction can be ago or empty. The at sign (@) is optional noise. The amounts of different units are implicitly added up with appropriate sign accounting. As found at: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=dataty pe-datetime.html > I've opined in the past that it should be months, days, and seconds, > but no one seems excited enough about the issue to do the nontrivial > work involved ... If it truly is that trivial, please point me at the file I need to hack. -- Dan Langille : http://www.langille.org/
"Dan Langille" <dan@langille.org> writes:
> On 7 Apr 2003 at 10:32, Tom Lane wrote:
>> Type INTERVAL knows about months and seconds, nothing else.
> Hmmm, months and seconds only. Then is the documentation wrong?
No, the docs are right, but the physical storage is months and seconds.
For instance, '1 year' becomes '12 months' (okay) and '1 week' becomes
'604800 seconds' (not so okay). Adding 'day' as a basic element would
allow correct handling of cross-DST-boundary cases.
>> I've opined in the past that it should be months, days, and seconds,
>> but no one seems excited enough about the issue to do the nontrivial
>> work involved ...
> If it truly is that trivial, please point me at the file I need to
> hack.
Didn't I just say that I think it's *not* trivial? But anyway, the
interval-related functions are in some subset of
src/backend/utils/adt/date.c
src/backend/utils/adt/datetime.c
src/backend/utils/adt/nabstime.c
src/backend/utils/adt/timestamp.c
and see also their header files
src/include/utils/date.h
src/include/utils/datetime.h
src/include/utils/nabstime.h
src/include/utils/timestamp.h
There are several datatypes implemented in those files, but their
interconnections are spaghetti-ish enough that you'll probably have
to look through all the code before you start hacking.
regards, tom lane
On 7 Apr 2003 at 10:50, Tom Lane wrote: > "Dan Langille" <dan@langille.org> writes: > > On 7 Apr 2003 at 10:32, Tom Lane wrote: > >> Type INTERVAL knows about months and seconds, nothing else. > > > Hmmm, months and seconds only. Then is the documentation wrong? > > No, the docs are right, but the physical storage is months and seconds. > For instance, '1 year' becomes '12 months' (okay) and '1 week' becomes > '604800 seconds' (not so okay). Adding 'day' as a basic element would > allow correct handling of cross-DST-boundary cases. Ahhhh, OK, I understand. > >> I've opined in the past that it should be months, days, and seconds, > >> but no one seems excited enough about the issue to do the nontrivial > >> work involved ... > > > If it truly is that trivial, please point me at the file I need to > > hack. > > Didn't I just say that I think it's *not* trivial? But anyway, the > interval-related functions are in some subset of *cough* yes, sorry, I misunderstood. So much for my spending the rest of today fixing it... -- Dan Langille : http://www.langille.org/
On 7 Apr 2003 at 10:32, Tom Lane wrote: > "Dan Langille" <dan@langille.org> writes: > > Yes, as hinted in the message subject. Hmmm, so that's how it's > > doing the math. I would think '24 hours' would give a different > > answer to '1 day' since '1 day' is not necessarily == '24 hours'. > > Type INTERVAL knows about months and seconds, nothing else. I've > opined in the past that it should be months, days, and seconds, > but no one seems excited enough about the issue to do the nontrivial > work involved ... I forgot to ask: Does this explain why the problem occurs under 7.2.3 but not 7.3.2? Is the Right Thing To Do(tm) this: select current_date, current_date-1; instead of this: select current_date, (current_date - interval '1 day')::date; Thanks. -- Dan Langille : http://www.langille.org/
"Dan Langille" <dan@langille.org> writes:
> I forgot to ask: Does this explain why the problem occurs under 7.2.3
> but not 7.3.2?
Say again? AFAIR this issue has been with us from the beginning.
Certainly the "regression tests fail on every DST transition day"
syndrome has been around for as long as I've been using Postgres.
> Is the Right Thing To Do(tm) this:
> select current_date, current_date-1;
> instead of this:
> select current_date, (current_date - interval '1 day')::date;
The "current_date-1" locution is no doubt more efficient as well
as more likely to do what you want. Still, it'd be nice if the
other way worked.
regards, tom lane
would this problem have happened using timestamptz? Martijn van Oosterhout wrote: > On Mon, Apr 07, 2003 at 08:19:57AM -0400, Dan Langille wrote: > >>Looking at the code, I suspect the problem is related to this problem >>which I can duplicate on my current system: PostgreSQL 7.2.3 on i386- >>portbld-freebsd4.6, compiled by GCC 2.95.4 >> >># select current_date, (current_date - interval '1 day')::date; >> date | date >>------------+------------ >> 2003-04-07 | 2003-04-05 >> >>I expect the answer to be 2003-04-06 (i.e. yesterday's date). > > > Out of curiosity, would this weekend be the day you switched to/from > daylight savings time? Then there were only 23 hours in the day, so 1 day > ago was actually the 5th. > > Anyway, why not just: > > select current_date, current_date-1; > date | ?column? > ------------+------------ > 2003-04-07 | 2003-04-06 > (1 row) >
On 7 Apr 2003 at 11:37, Tom Lane wrote:
> "Dan Langille" <dan@langille.org> writes:
> > I forgot to ask: Does this explain why the problem occurs under 7.2.3
> > but not 7.3.2?
>
> Say again? AFAIR this issue has been with us from the beginning.
> Certainly the "regression tests fail on every DST transition day"
> syndrome has been around for as long as I've been using Postgres.
OK, I'll say again. The problem was not present on my 7.3.2 box but
was on the 7.2.3 box.
# select current_date, (current_date - 1)::date;
date | date
------------+------------
2003-04-07 | 2003-04-06
(1 row)
# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.3.2 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
(1 row)
working-copy.freshports.org=#
But it WAS present on 7.2.3 earlier today. I suspect it's no longer
present, because we're now later in the day.
# select current_date, (current_date - interval '24 hours')::date;
date | date
------------+------------
2003-04-07 | 2003-04-05
(1 row)
# select current_date, (current_date - 1)::date;
date | date
------------+------------
2003-04-07 | 2003-04-06
(1 row)
# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.2.3 on i386-portbld-freebsd4.6, compiled by GCC 2.95.4
(1 row)
> > Is the Right Thing To Do(tm) this:
> > select current_date, current_date-1;
> > instead of this:
> > select current_date, (current_date - interval '1 day')::date;
>
> The "current_date-1" locution is no doubt more efficient as well
> as more likely to do what you want. Still, it'd be nice if the
> other way worked.
Yes, it would be, but I'll go with what will always work. Thanks.
--
Dan Langille : http://www.langille.org/
"Dan Langille" <dan@langille.org> writes:
> OK, I'll say again. The problem was not present on my 7.3.2 box but
> was on the 7.2.3 box.
Oh, I see: you're not testing the right thing. In 7.3, 'date - interval'
promotes to 'timestamp without time zone - interval':
regression=# select current_date, (current_date - interval '24 hours');
date | ?column?
------------+---------------------
2003-04-07 | 2003-04-06 00:00:00
(1 row)
which is a crude but effective way of sidestepping the issue. But if
you do the same calculation 7.2 did:
regression=# select current_date, (current_date::timestamptz - interval '24 hours');
date | ?column?
------------+------------------------
2003-04-07 | 2003-04-05 23:00:00-05
(1 row)
you'll still get the same unwanted result.
regards, tom lane
On Mon, Apr 07, 2003 at 12:05:13PM -0400, Tom Lane wrote: > "Dan Langille" <dan@langille.org> writes: > > OK, I'll say again. The problem was not present on my 7.3.2 box but > > was on the 7.2.3 box. > > Oh, I see: you're not testing the right thing. In 7.3, 'date - interval' > promotes to 'timestamp without time zone - interval': > > regression=# select current_date, (current_date - interval '24 hours'); > date | ?column? > ------------+--------------------- > 2003-04-07 | 2003-04-06 00:00:00 > (1 row) > > which is a crude but effective way of sidestepping the issue. But if > you do the same calculation 7.2 did: > > regression=# select current_date, (current_date::timestamptz - interval '24 hours'); > date | ?column? > ------------+------------------------ > 2003-04-07 | 2003-04-05 23:00:00-05 > (1 row) > > you'll still get the same unwanted result. In case this is related, I get the following regression.diffs with today's cvs: *** ./expected/horology.out Mon Apr 7 16:56:14 2003 --- ./results/horology.out Mon Apr 7 20:21:10 2003 *************** *** 571,577 **** SELECT (timestamp with time zone 'today' = (timestamp with time zone 'yesterday' + interval '1 day')) as "True"; True ------ ! t (1 row) SELECT (timestamp with time zone 'today' = (timestamp with time zone 'tomorrow' - interval '1 day')) as "True"; --- 571,577 ---- SELECT (timestamp with time zone 'today' = (timestamp with time zone 'yesterday' + interval '1 day')) as "True"; True ------ ! f (1 row) SELECT (timestamp with time zone 'today' = (timestamp with time zone 'tomorrow' - interval '1 day')) as "True"; *************** *** 583,589 **** SELECT (timestamp with time zone 'tomorrow' = (timestamp with time zone 'yesterday' + interval '2 days')) as "True"; True ------ ! t (1 row) SELECT (timestamp with time zone 'tomorrow' > 'now') as "True"; --- 583,589 ---- SELECT (timestamp with time zone 'tomorrow' = (timestamp with time zone 'yesterday' + interval '2 days')) as "True"; True ------ ! f (1 row) SELECT (timestamp with time zone 'tomorrow' > 'now') as "True"; ====================================================================== Cheers, Patrick (NetBSD-1.6Q/i386 gcc 2.95.3nb4)
On 7 Apr 2003 at 20:42, Patrick Welche wrote: > On Mon, Apr 07, 2003 at 12:05:13PM -0400, Tom Lane wrote: > > "Dan Langille" <dan@langille.org> writes: > > > OK, I'll say again. The problem was not present on my 7.3.2 box > > > but was on the 7.2.3 box. > > > > Oh, I see: you're not testing the right thing. In 7.3, 'date - > > interval' promotes to 'timestamp without time zone - interval': > > > > regression=# select current_date, (current_date - interval '24 > > hours'); > > date | ?column? > > ------------+--------------------- > > 2003-04-07 | 2003-04-06 00:00:00 > > (1 row) > > > > which is a crude but effective way of sidestepping the issue. But > > if you do the same calculation 7.2 did: > > > > regression=# select current_date, (current_date::timestamptz - > > interval '24 hours'); > > date | ?column? > > ------------+------------------------ > > 2003-04-07 | 2003-04-05 23:00:00-05 > > (1 row) > > > > you'll still get the same unwanted result. > > In case this is related, I get the following regression.diffs with > today's cvs: > > > *** ./expected/horology.out Mon Apr 7 16:56:14 2003 > --- ./results/horology.out Mon Apr 7 20:21:10 2003 > *************** > *** 571,577 **** > SELECT (timestamp with time zone 'today' = (timestamp with time zone > 'yesterday' + interval '1 day')) as "True"; > True > ------ > ! t > (1 row) > > SELECT (timestamp with time zone 'today' = (timestamp with time zone > 'tomorrow' - interval '1 day')) as "True"; > --- 571,577 ---- > SELECT (timestamp with time zone 'today' = (timestamp with time zone > 'yesterday' + interval '1 day')) as "True"; > True > ------ > ! f > (1 row) > > SELECT (timestamp with time zone 'today' = (timestamp with time zone > 'tomorrow' - interval '1 day')) as "True"; > *************** > *** 583,589 **** > SELECT (timestamp with time zone 'tomorrow' = (timestamp with time > zone 'yesterday' + interval '2 days')) as "True"; > True > ------ > ! t > (1 row) > > SELECT (timestamp with time zone 'tomorrow' > 'now') as "True"; --- > 583,589 ---- > SELECT (timestamp with time zone 'tomorrow' = (timestamp with time > zone 'yesterday' + interval '2 days')) as "True"; > True > ------ > ! f > (1 row) > > SELECT (timestamp with time zone 'tomorrow' > 'now') as "True"; Just in case it's relevent, the problem occur earlier in the day.. later in the day, the problem could not be reproduced. -- Dan Langille : http://www.langille.org/
On Mon, Apr 07, 2003 at 08:42:56PM +0100, Patrick Welche wrote: > > *** ./expected/horology.out Mon Apr 7 16:56:14 2003 > --- ./results/horology.out Mon Apr 7 20:21:10 2003 > *************** > *** 571,577 **** > SELECT (timestamp with time zone 'today' = (timestamp with time zone 'yesterday' + interval '1 day')) as "True"; > True > ------ > ! t > (1 row) Now for a mystery: I actually installed said postgres even though it failed the regression test, and template1=# SELECT (timestamp with time zone 'today' = (timestamp with time template1(# zone 'tomorrow' - interval '1 day')) as "True"; True ------ t (1 row) gmake runcheck still fails horology(!) Got it! We (Britain) changed time last week => it passes when I run the newly installed database, whereas the regression test happens in PST8PDT, and I believe you change time this weekend! Sorry, Patrick
On 7 Apr 2003 at 22:11, Patrick Welche wrote: > Got it! We (Britain) changed time last week => it passes when I run > the newly installed database, whereas the regression test happens in > PST8PDT, and I believe you change time this weekend! Yes, that's correct, our time changed on this past Sunday. -- Dan Langille : http://www.langille.org/