possible time change issue - known problem?

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/

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&amp;idoc=0&amp;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 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/