Re: One more question about intervals

On 4 Nov 2001, Konstantinos Agouros wrote:
>after some work with intervals I got a little further. Now I have two
>questions:
>a) Is there a datestyle where > 24 hours is not represented as a 1 but as
> 24 hours (or 48 or whatever)?

Look into SQL92 extract() syntax, or PostgreSQL's date_part(). It's sort
of a hack, but you could extract the days and multiply by 24, and then add
the hours field, e.g.:

lx=# SELECT sum(i) FROM my_intervals;
sum
--------------
3 days 03:00
(1 row)

lx=# SELECT extract(DAYS FROM sum(i)) * 24 +
lx-# extract(HOURS FROM sum(i)) AS cumulative_hours
lx-# FROM my_intervals;
cumulative_hours
------------------
75
(1 row)

Depending on how large your intervals got, you might have to start
extracting week, month or year fields as well. Does anyone know a better,
more general solution than this? Something like extract(CUMULATIVE_HOURS),
or something? ;)

>b) Can Postgres do calculations like one hour does cost 100 Euro, how many
> Euro were worked for?

Couldn't you just multiply your cumulative hours by the cost? E.g.:

lx=# SELECT extract(DAYS FROM sum(i)) * 24 +
lx-# extract(HOURS FROM sum(i)) * 100 || ' Euros' AS cost
lx-# FROM my_intervals;
cost
-----------
372 Euros
(1 row)

Regards,
Jw.
--
jlx(at)commandprompt(dot)com
by way of pgsql-general(at)commandprompt(dot)com