Re: Intervals (was: DeadLocks..., DeadLocks...)

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 //