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