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@commandprompt.com
by way of pgsql-general@commandprompt.com