Skip site navigation (1) Skip section navigation (2)

Re: Inconsistant DOW...

From: Richard Huxton <dev(at)archonet(dot)com>
To: "W(dot)B(dot)Hill" <bill(at)hillzaa1(dot)miniserver(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Inconsistant DOW...
Date: 2004-06-28 17:01:47
Message-ID: 40E04EFB.2050407@archonet.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Patrick WelcheDate: 2004-06-28 17:02:01
Subject: Re: Inconsistant DOW...
Previous:From: Adam RuthDate: 2004-06-28 16:37:11
Subject: Re: Group by - Case insensitivity

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group