From: | Alban Hertroys <alban(at)magproductions(dot)nl> |
---|---|
To: | Tom Allison <tom(at)tacocat(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Intervals (was: DeadLocks..., DeadLocks...) |
Date: | 2007-06-18 15:22:41 |
Message-ID: | 4676A341.6070308@magproductions.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Tom Allison wrote:
> I have a question though.
> I noticed a particular format for identifying dates like:
> now()-'3 days'::interval;
>
> What's '::interval' and why should I use it?
Intervals are convenient, simply said. They are a special type dealing
with date calculations relative to a given date. Basically they move
calculation of relative dates to the database server instead of the
programmer (always a good thing IMO).
Next to that, they're much more readable compared to the alternative
(which is in fact an implicit interval type measured in days, I suppose).
Compare:
SELECT now() + INTERVAL '1 month';
SELECT now() + CASE WHEN extract('month' from now()) IN (1, 3, 5, 7, 8,
10, 12) THEN 31 WHEN ...etc... END
or:
SELECT now() + INTERVAL '3 weeks - 5 days'
SELECT now() + 16;
The only drawback I know is that various query engines (ie. PHP's pg_
functions) don't know how to handle intervals. Suffice to say, I'm a big
fan of the interval type.
--
Alban Hertroys
alban(at)magproductions(dot)nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2007-06-18 15:43:17 | Re: Apparent Wraparound? |
Previous Message | Michael Glaesemann | 2007-06-18 15:17:45 | Re: Setting Variable - (Correct) |