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@magproductions.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 //
