Thread

Inconsistant DOW...

W.B.Hill <bill@hillzaa1.miniserver.com> — 2004-06-28T16:23:25Z

Hiya!

What's happening, where've I goofed?

CREATE TABLE test (d date);

INSERT INTO test(d) VALUES('11-Apr-2004');
INSERT INTO test(d) VALUES('27-Mar-2005');
INSERT INTO test(d) VALUES('16-Apr-2006');
INSERT INTO test(d) VALUES('08-Apr-2007');
INSERT INTO test(d) VALUES('23-Mar-2008');
INSERT INTO test(d) VALUES('12-Apr-2009');
INSERT INTO test(d) VALUES('04-Apr-2010');

SELECT * FROM test;
     d
------------
 2004-04-11
 2005-03-27
 2006-04-16
 2007-04-08
 2008-03-23
 2009-04-12
 2010-04-04
(7 rows)

SELECT date_part('dow',d) FROM test;
 date_part
-----------
         0
         0
         0
         0
         0
         0
         0
(7 rows)

This is good - every Easter's on a Sunday so far...

SELECT d+'45 days ago'::interval FROM test;
        ?column?
------------------------
 2004-02-25 23:00:00+00
 2005-02-10 00:00:00+00
 2006-03-01 23:00:00+00
 2007-02-21 23:00:00+00
 2008-02-07 00:00:00+00
 2009-02-25 23:00:00+00
 2010-02-17 23:00:00+00
(7 rows)

Why the different times??? Why the times???

SELECT date_part('dow',d+'45 days ago'::interval) FROM test;
 date_part
-----------
         3
         4
         3
         3
         4
         3
         3
(7 rows)

So, Ash Wednesday's on a Thursday iff Easter's in March???
Hmmm.

Re: Inconsistant DOW...

Richard Huxton <dev@archonet.com> — 2004-06-28T17:01:47Z

W.B.Hill wrote:
> Hiya!
> 
> What's happening, where've I goofed?
> 
> CREATE TABLE test (d date);
[snip]
> 
> This is good - every Easter's on a Sunday so far...
> 
> SELECT d+'45 days ago'::interval FROM test;
>         ?column?
> ------------------------
>  2004-02-25 23:00:00+00
>  2005-02-10 00:00:00+00
>  2006-03-01 23:00:00+00
>  2007-02-21 23:00:00+00
>  2008-02-07 00:00:00+00
>  2009-02-25 23:00:00+00
>  2010-02-17 23:00:00+00
> (7 rows)
> 
> Why the different times??? Why the times???

At a guess, the date is being converted into a timestamp with timezone 
so you can add the interval to it. At another guess, the 1hr difference 
is due to the effects of daylight-savings-time (or whatever it's called 
in your locale).

I've got a feeling '45 days'==45*'24 hours' which, going over a DST 
boundary isn't true for one day. Simplest solution would probably be to 
add a few hours to the date before doing your calculations and then 
casting back to a date.

--
   Richard Huxton
   Archonet Ltd

Re: Inconsistant DOW...

Patrick Welche <prlw1@newn.cam.ac.uk> — 2004-06-28T17:02:01Z

On Mon, Jun 28, 2004 at 04:23:25PM +0000, W.B.Hill wrote:
> Hiya!
> 
> What's happening, where've I goofed?
> 
> CREATE TABLE test (d date);

Try using

  CREATE TABLE test (d timestamp without timezone);

instead..

> SELECT d+'45 days ago'::interval FROM test;
>         ?column?
> ------------------------
>  2004-02-25 23:00:00+00
>  2005-02-10 00:00:00+00
>  2006-03-01 23:00:00+00
>  2007-02-21 23:00:00+00
>  2008-02-07 00:00:00+00
>  2009-02-25 23:00:00+00
>  2010-02-17 23:00:00+00
> (7 rows)
> 
> Why the different times??? Why the times???

Different times because of summer time starting before or after Ash
Wednesday, times because of implicit type casting - what is the
type of date + interval ?

> SELECT date_part('dow',d+'45 days ago'::interval) FROM test;
>  date_part
> -----------
>          3
>          4
>          3
>          3
>          4
>          3
>          3
> (7 rows)
> 
> So, Ash Wednesday's on a Thursday iff Easter's in March???
> Hmmm.

I'd try adding 46 days ago ;)

Cheers,

Patrick

Re: Inconsistant DOW...

Tom Lane <tgl@sss.pgh.pa.us> — 2004-06-28T18:04:59Z

Richard Huxton <dev@archonet.com> writes:
> W.B.Hill wrote:
>> SELECT d+'45 days ago'::interval FROM test;
>> 
>> Why the different times??? Why the times???

> At a guess, the date is being converted into a timestamp with timezone 
> so you can add the interval to it.

Yeah, I think that will be the preferred conversion (and the fact that
the output shows a timezone offset is a dead giveaway ;-))

However there is also a timestamp-without-timezone plus interval
operator, so one good solution is to explicitly cast the date to
timestamp without tz and then add the interval.

Another and probably even better solution for this problem is to forget
timestamps and intervals, and use the date plus/minus integer operators
(ie, write "d - 45").  If you don't care about sub-day resolution there
is no reason to get into timestamps at all.

BTW, since 7.3 there has been a date-plus-interval operator yielding
timestamp without time zone, which I believe Lockhart added specifically
to avoid the unwanted promotion to timestamptz in this scenario.
So the third answer is to update to something less ancient than PG 7.2.

			regards, tom lane

Re: Inconsistant DOW...

W.B.Hill <bill@hillzaa1.miniserver.com> — 2004-06-29T10:48:01Z

On Mon, 28 Jun 2004, Tom Lane wrote:

> Richard Huxton <dev@archonet.com> writes:
> > W.B.Hill wrote:
> >> SELECT d+'45 days ago'::interval FROM test;

Yep - it's the clock's going forward that does it. Doh! The quick'n'dirty
work around '45.3 days ago' was what I used but just a straight -45 is
better.

> BTW, since 7.3 there has been a date-plus-interval operator yielding
> timestamp without time zone, which I believe Lockhart added specifically
> to avoid the unwanted promotion to timestamptz in this scenario.
> So the third answer is to update to something less ancient than PG 7.2.

Debian Stable. Sigh. IT support policies. Whimper!