pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with intervals
5+ messages / 4 participants
[nested] [flat]
* Problem with intervals
@ 2003-12-02 05:09 Bob Smith <bsmith@h-e.com>
0 siblings, 3 replies; 5+ messages in thread
From: Bob Smith @ 2003-12-02 05:09 UTC (permalink / raw)
To: pgsql-sql
I'm getting an unexpected result using intervals in an expression:
select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
date
------------
2003-10-26
(1 row)
When I get rid of the date cast it becomes clear what is happening:
select '2003-10-26 0:00:00'::timestamp + '1 day'::interval;
?column?
------------------------
2003-10-26 23:00:00-08
(1 row)
I assumed '1 day' would always increment the date by 1, but it appears
that '1 day' just means '24 hours', and due to the daylight/standard
time shift, October 26 was 25 hours long this year.
Is this a Postgres bug, or is this correct SQL behavior? I'm running
Postgres 7.2.2.
Bob S.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with intervals
@ 2003-12-02 08:56 Richard Huxton <dev@archonet.com>
parent: Bob Smith <bsmith@h-e.com>
2 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2003-12-02 08:56 UTC (permalink / raw)
To: Bob Smith <bsmith@h-e.com>; pgsql-sql
On Tuesday 02 December 2003 05:09, Bob Smith wrote:
> I'm getting an unexpected result using intervals in an expression:
>
> select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
> date
> ------------
> 2003-10-26
> I assumed '1 day' would always increment the date by 1, but it appears
> that '1 day' just means '24 hours', and due to the daylight/standard
> time shift, October 26 was 25 hours long this year.
>
> Is this a Postgres bug, or is this correct SQL behavior? I'm running
> Postgres 7.2.2.
Expected, because you're acting on a timestamp. When you start looking at time
handling across timezones and daylight saving systems across the world it
does get a bit complicated.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with intervals
@ 2003-12-02 11:53 Alexander M. Pravking <fduch@antar.bryansk.ru>
parent: Bob Smith <bsmith@h-e.com>
2 siblings, 1 reply; 5+ messages in thread
From: Alexander M. Pravking @ 2003-12-02 11:53 UTC (permalink / raw)
To: Bob Smith <bsmith@h-e.com>; +Cc: pgsql-sql
On Mon, Dec 01, 2003 at 09:09:20PM -0800, Bob Smith wrote:
> I'm getting an unexpected result using intervals in an expression:
>
> select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
> date
> ------------
> 2003-10-26
> (1 row)
Try using '2003-10-26 0:00:00'::date + 1;
integers do not lie ;-)
> When I get rid of the date cast it becomes clear what is happening:
>
> select '2003-10-26 0:00:00'::timestamp + '1 day'::interval;
> ?column?
> ------------------------
> 2003-10-26 23:00:00-08
> (1 row)
>
> Is this a Postgres bug, or is this correct SQL behavior? I'm running
> Postgres 7.2.2.
It has been discussed several times, Tom Lane offered to add 'day' as
a separate interval unit (like 'second' and 'month' at this moment),
but noone took a shot at it, AFAIK.
Note also, that in 7.3 "timestamp" means "timestamp without time zone",
while in 7.2 it's "timestamp with time zone".
--
Fduch M. Pravking
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with intervals
@ 2003-12-02 14:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bob Smith <bsmith@h-e.com>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2003-12-02 14:51 UTC (permalink / raw)
To: Bob Smith <bsmith@h-e.com>; +Cc: pgsql-sql
Bob Smith <bsmith@h-e.com> writes:
> '1 day' just means '24 hours'
Yup.
> Is this a Postgres bug, or is this correct SQL behavior?
It's arguably a bug, but it's unfixable without a significant change in
the internal representation and handling of intervals. I don't know
when anyone will get annoyed enough to tackle it. In the meantime,
consider using date +/- integer arithmetic instead of timestamp + interval.
> I'm running Postgres 7.2.2.
You really should be running something newer ...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with intervals
@ 2003-12-02 20:55 Bob Smith <bsmith@h-e.com>
parent: Alexander M. Pravking <fduch@antar.bryansk.ru>
0 siblings, 0 replies; 5+ messages in thread
From: Bob Smith @ 2003-12-02 20:55 UTC (permalink / raw)
To: Alexander M. Pravking <fduch@antar.bryansk.ru>; +Cc: pgsql-sql
On Tuesday, Dec 2, 2003, at 03:53 US/Pacific, Alexander M. Pravking
wrote:
> On Mon, Dec 01, 2003 at 09:09:20PM -0800, Bob Smith wrote:
>> I'm getting an unexpected result using intervals in an expression:
>>
>> select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
>> date
>> ------------
>> 2003-10-26
>> (1 row)
>
> Try using '2003-10-26 0:00:00'::date + 1;
> integers do not lie ;-)
Aha! That solves my problem for now. I had also discovered that using
'25 hours'::interval works, but the integer approach is better.
> It has been discussed several times, Tom Lane offered to add 'day' as
> a separate interval unit (like 'second' and 'month' at this moment),
> but noone took a shot at it, AFAIK.
>
>
> Note also, that in 7.3 "timestamp" means "timestamp without time zone",
> while in 7.2 it's "timestamp with time zone".
Yet another reason to upgrade, I guess I'm gonna have to do it soon...
Thanks for your help!
Bob S.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-12-02 20:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-02 05:09 Problem with intervals Bob Smith <bsmith@h-e.com>
2003-12-02 08:56 ` Richard Huxton <dev@archonet.com>
2003-12-02 11:53 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
2003-12-02 20:55 ` Bob Smith <bsmith@h-e.com>
2003-12-02 14:51 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox