Thread: One more question about intervals
Hi, 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)? b) Can Postgres do calculations like one hour does cost 100 Euro, how many Euro were worked for? Cheers, Konstantin -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 ---------------------------------------------------------------------------- "Captain, this ship will not survive the forming of the cosmos." B'Elana Torres
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
On Sun, 4 Nov 2001, Command Prompt, Inc. wrote:
>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)
Whoops! I just noticed I messed up my operator precedence there in my
example. ;)
It should've read like this:
lx=# SELECT (extract(DAYS FROM sum(i)) * 24 +
lx(# extract(HOURS FROM sum(i))) * 100 || ' Euros' AS cost
lx-# FROM my_intervals;
cost
------------
7500 Euros
(1 row)
The point's the same, just make sure you group the time units in
parentheses before multiplying against a currency rate (unless you want to
severely undercharge). ;)
Regards,
Jw.
--
jlx@commandprompt.com
by way of pgsql-general@commandprompt.com
On Sun, Nov 04, 2001 at 11:24:10AM -0800, Command Prompt, Inc. wrote: > On Sun, 4 Nov 2001, Command Prompt, Inc. wrote: > >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) > > Whoops! I just noticed I messed up my operator precedence there in my > example. ;) Actually what I would need is date_part(EPOCH) since then I am sure that it is the seconds and I have to multiply with EURO/3600. The extract-stuff would ignore minutes or I would have to add these also. The problem is I have to reconstruct the hours in the format by hand and it would be nice to have the database do this by itself \:) Konstantin > > It should've read like this: > > lx=# SELECT (extract(DAYS FROM sum(i)) * 24 + > lx(# extract(HOURS FROM sum(i))) * 100 || ' Euros' AS cost > lx-# FROM my_intervals; > cost > ------------ > 7500 Euros > (1 row) > > The point's the same, just make sure you group the time units in > parentheses before multiplying against a currency rate (unless you want to > severely undercharge). ;) > > > Regards, > Jw. > -- > jlx@commandprompt.com > by way of pgsql-general@commandprompt.com > -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 ---------------------------------------------------------------------------- "Captain, this ship will not survive the forming of the cosmos." B'Elana Torres
On Sun, 4 Nov 2001, Konstantinos Agouros wrote:
>Actually what I would need is date_part(EPOCH) since then I am sure that it is
>the seconds and I have to multiply with EURO/3600. The extract-stuff would
>ignore minutes or I would have to add these also. The problem is I have to
>reconstruct the hours in the format by hand and it would be nice to have the
>database do this by itself \:)
Well, once the minutes field goes over 60, it gets added to the hours.
Intervals appears to behave in such a way as to describe a length of time
in the largest units possible first, with each unit's field being a
discrete value; it looks like the EPOCH field is the only one guaranteed
to give you a combined length of time for the entire duration of the
interval, so you're definitely on the right track with that.
You could create a function like this to make your life a little easier:
CREATE FUNCTION get_hours (interval)
RETURNS int4
AS 'SELECT round(extract(EPOCH FROM $1) / 3600.0)::integer'
LANGUAGE 'sql';
Depending on whether or not you wanted to round up, down, or to the
nearest hour, you'd use ceil(), floor(), or round() respectively, on the
result of the division.
Then you'd just have to do:
lx=# SELECT get_hours(sum(i)) * 100 || ' Euros' AS cost
lx-# FROM my_intervals;
cost
------------
7500 Euros
(1 row)
Regards,
Jw.
--
jlx@commandprompt.com
by way of pgsql-general@commandprompt.com
On Sun, 4 Nov 2001, Command Prompt, Inc. wrote:
>You could create a function like this to make your life a little easier:
>
>CREATE FUNCTION get_hours (interval)
> RETURNS int4
> AS 'SELECT round(extract(EPOCH FROM $1) / 3600.0)::integer'
> LANGUAGE 'sql';
>
>Depending on whether or not you wanted to round up, down, or to the
>nearest hour, you'd use ceil(), floor(), or round() respectively, on the
>result of the division.
And actually, if you *really* wanted to make your life easier, you could:
-- Build a function which combines a passed integer with a derived hour:
CREATE FUNCTION get_hours (int4, interval) RETURNS int4
AS 'SELECT round(extract(EPOCH FROM $2) / 3600.0)::integer + $1'
LANGUAGE 'sql';
-- Get the sum of hours on an interval, with an aggregate:
CREATE AGGREGATE sum_hours (BASETYPE = interval, SFUNC = get_hours,
STYPE = int4, INITCOND = 0);
-- Make a little cost-formatter, which takes the hours billed, and
-- the euro/hour rate.
CREATE FUNCTION get_euros(int4, int4) RETURNS text
AS 'SELECT $1 * $2 || '' Euros''' LANGUAGE 'sql';
And voila:
lx=# SELECT get_euros(sum_hours(i), 100) AS euros FROM my_intervals;
euros
------------
7500 Euros
(1 row)
I think I'm maybe feeling overhelpful today. ;)
Regards,
Jw.
--
jlx@commandprompt.com
by way of pgsql-general@commandprompt.com
"Command Prompt, Inc." <pgsql-general@commandprompt.com> writes:
> 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.:
> 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? ;)
date_part('EPOCH', foo) gives the total number of seconds in an
interval. Or you can write extract(epoch from foo) if you want
to pretend this is SQL92-compatible. Unfortunately it didn't
occur to the SQL authors to provide such a function...
regards, tom lane
In <Pine.LNX.4.30.0111041143020.19169-100000@commandprompt.com> pgsql-general@commandprompt.com ("Command Prompt,
Inc.")writes:
>On Sun, 4 Nov 2001, Konstantinos Agouros wrote:
>in the largest units possible first, with each unit's field being a
>discrete value; it looks like the EPOCH field is the only one guaranteed
>to give you a combined length of time for the entire duration of the
>interval, so you're definitely on the right track with that.
>You could create a function like this to make your life a little easier:
>CREATE FUNCTION get_hours (interval)
> RETURNS int4
> AS 'SELECT round(extract(EPOCH FROM $1) / 3600.0)::integer'
> LANGUAGE 'sql';
>Depending on whether or not you wanted to round up, down, or to the
>nearest hour, you'd use ceil(), floor(), or round() respectively, on the
>result of the division.
extract epoch from was what I needed to get this where I can work with it.
A time-formatter will come later \:)
Thanks,
Konstantin
>Then you'd just have to do:
>lx=# SELECT get_hours(sum(i)) * 100 || ' Euros' AS cost
>lx-# FROM my_intervals;
> cost
>------------
> 7500 Euros
>(1 row)
>Regards,
>Jw.
>--
>jlx@commandprompt.com
>by way of pgsql-general@commandprompt.com
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de
Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
----------------------------------------------------------------------------
"Captain, this ship will not survive the forming of the cosmos." B'Elana Torres