Re: Casting dates

Joe,

> Does anyone know if it's possible to calculate the number of days (or
> weeks, seconds, minutes, or what-have-you) in a given interval
> without
> casting to EPOCH and performing manipulations there?
>
> PostgreSQL usually returns intervals created by subtraction in days
> and smaller fragments, I would like to know if it's possible for it
> to
> return years. (Integer division of the number of days by 365 would
> produce an almost-correct result, but it's rather inelegant.)

Unfortunately, you've hit one of Postgres implementation limits.
Multiplying and dividing INTERVAL values, while covered by the SQL92
spec, has not been completely implemented in Postgres. That is, it
should be possible for you to:

SELECT INTERVAL '3 days 5 hours' / INTERVAL '1 minute'

However, implementing this has been a challenge for all SQL-DB
designers. I don't know of any DB that does handle INTERVAL division.

It's a surmountable challenge, though, but requires somebody to take
the lead in a) working out the logic, and b) writing the code to
implement it. A) is not a trivial task, either ... while 60 minutes /
1 second is obvious, how about 4 months / 4 days? Months are not
constant in length.

I've had some ideas for a spec for this myself (expanding on the rather
terse spec in SQL 92) but have not had time to write it up. Good luck.

-Josh Berkus