pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedinterval as hours or minutes ?
8+ messages / 6 participants
[nested] [flat]
* interval as hours or minutes ?
@ 2007-02-07 17:03 Aarni Ruuhimäki <aarni@kymi.com>
2007-02-07 17:33 ` Re: interval as hours or minutes ? Bricklen Anderson <banderson@presinet.com>
2007-02-07 17:44 ` Re: interval as hours or minutes ? A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-02-07 21:25 ` Re: interval as hours or minutes ? Bruno Wolff III <bruno@wolff.to>
2007-02-07 22:09 ` Re: interval as hours or minutes ? Jan Muszynski <postgres@jancm.org>
0 siblings, 4 replies; 8+ messages in thread
From: Aarni Ruuhimäki @ 2007-02-07 17:03 UTC (permalink / raw)
To: pgsql-sql
Hi all,
Could anyone please tell an easy way to get total hours or minutes from an
interval ?
SELECT SUM(stop_date_time - start_date_time) AS tot_time FROM work_times WHERE
user_id = 1;
tot_time
-----------------
2 days 14:08:44
I'd like to have this like ... AS tot_hours ...
tot_hours
-----------------
62
and ... AS tot_minutes ...
tot_minutes
-----------------
3728
Maybe even ... AS tot_hours_minutes_seconds
tot_hours_minutes_seconds
-----------------
62:08:44
start_date_time and stop_date_time are stored as timestamp without time zone,
using Pg 8.1.5 on CentOs 4.4
???
Thanks,
--
Aarni Ruuhimäki
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: interval as hours or minutes ?
2007-02-07 17:03 interval as hours or minutes ? Aarni Ruuhimäki <aarni@kymi.com>
@ 2007-02-07 17:33 ` Bricklen Anderson <banderson@presinet.com>
3 siblings, 0 replies; 8+ messages in thread
From: Bricklen Anderson @ 2007-02-07 17:33 UTC (permalink / raw)
To: aarni@kymi.com; +Cc: pgsql-sql
Aarni Ruuhimäki wrote:
> Hi all,
>
> Could anyone please tell an easy way to get total hours or minutes from an
> interval ?
>
> SELECT SUM(stop_date_time - start_date_time) AS tot_time FROM work_times WHERE
> user_id = 1;
> tot_time
> -----------------
> 2 days 14:08:44
>
> I'd like to have this like ... AS tot_hours ...
> tot_hours
> -----------------
> 62
>
> and ... AS tot_minutes ...
> tot_minutes
> -----------------
> 3728
>
> Maybe even ... AS tot_hours_minutes_seconds
> tot_hours_minutes_seconds
> -----------------
> 62:08:44
>
>
> start_date_time and stop_date_time are stored as timestamp without time zone,
> using Pg 8.1.5 on CentOs 4.4
>
> ???
>
> Thanks,
>
I have been using the following function (watch for line wrap)
CREATE OR REPLACE function convert_interval(interval,text) returns text
as $$
declare
retval TEXT;
my_interval INTERVAL := $1;
my_type TEXT := $2;
qry TEXT;
begin
if my_type ~* 'hour' then
select into retval extract(epoch from
my_interval::interval)/3600 || ' hours';
elsif my_type ~* 'min' then
select into retval extract(epoch from my_interval::interval)/60
|| ' minutes';
elsif my_type ~* 'day' then
select into retval extract(epoch from
my_interval::interval)/86400 || ' days';
elsif my_type ~* 'sec' then
select into retval extract(epoch from my_interval::interval) ||
' seconds';
end if;
RETURN retval;
end;
$$ language plpgsql strict immutable;
pqsl=# select convert_interval(now() - (now()-interval '1 day 4 hours 6
minutes'),'minutes') as minutes;
minutes
--------------
1686 minutes
There may be something built-in now, but I haven't looked recently.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: interval as hours or minutes ?
2007-02-07 17:03 interval as hours or minutes ? Aarni Ruuhimäki <aarni@kymi.com>
@ 2007-02-07 17:44 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
3 siblings, 0 replies; 8+ messages in thread
From: A. Kretschmer @ 2007-02-07 17:44 UTC (permalink / raw)
To: pgsql-sql
am Wed, dem 07.02.2007, um 19:03:35 +0200 mailte Aarni Ruuhimäki folgendes:
> Hi all,
>
> Could anyone please tell an easy way to get total hours or minutes from an
> interval ?
>
> SELECT SUM(stop_date_time - start_date_time) AS tot_time FROM work_times WHERE
> user_id = 1;
Perhaps. You can, for instance, with extract(epoch from stop_date_time)
retrieve the number of seconds and work with this.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: interval as hours or minutes ?
2007-02-07 17:03 interval as hours or minutes ? Aarni Ruuhimäki <aarni@kymi.com>
@ 2007-02-07 21:25 ` Bruno Wolff III <bruno@wolff.to>
3 siblings, 0 replies; 8+ messages in thread
From: Bruno Wolff III @ 2007-02-07 21:25 UTC (permalink / raw)
To: Aarni Ruuhimäki <aarni@kymi.com>; +Cc: pgsql-sql
On Wed, Feb 07, 2007 at 19:03:35 +0200,
Aarni Ruuhimäki <aarni@kymi.com> wrote:
>
> Could anyone please tell an easy way to get total hours or minutes from an
> interval ?
Extract the epoch from the interval and divide by the number of seconds
in the period of time that applies and apply appropiate rounding.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: interval as hours or minutes ?
2007-02-07 17:03 interval as hours or minutes ? Aarni Ruuhimäki <aarni@kymi.com>
@ 2007-02-07 22:09 ` Jan Muszynski <postgres@jancm.org>
2007-02-08 10:01 ` Re: interval as hours or minutes ? Aarni Ruuhimäki <aarni@kymi.com>
3 siblings, 1 reply; 8+ messages in thread
From: Jan Muszynski @ 2007-02-07 22:09 UTC (permalink / raw)
To: aarni@kymi.com; +Cc: pgsql-sql
On 7 Feb 2007 at 19:03, Aarni Ruuhimäki wrote:
> Hi all,
>
> Could anyone please tell an easy way to get total hours or minutes from an
> interval ?
>
> SELECT SUM(stop_date_time - start_date_time) AS tot_time FROM work_times WHERE
> user_id = 1;
> tot_time
> -----------------
> 2 days 14:08:44
>
> I'd like to have this like ... AS tot_hours ...
> tot_hours
> -----------------
> 62
>
> and ... AS tot_minutes ...
> tot_minutes
> -----------------
> 3728
>
> Maybe even ... AS tot_hours_minutes_seconds
> tot_hours_minutes_seconds
> -----------------
> 62:08:44
>
>
> start_date_time and stop_date_time are stored as timestamp without time zone,
> using Pg 8.1.5 on CentOs 4.4
select extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05
13:00:00')) as num_seconds;
num_seconds
------------
185040
(1 row)
select (extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05
13:00:00')) * interval '1 second') as hours_minutes_seconds;
hours_minutes_seconds
-----------------------
51:24:00
(1 row)
select extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05
13:00:01'))/60 as minutes;
minutes
------------------
3083.98333333333
(1 row)
select round(extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05
13:00:01'))/60) as minutes;
minutes
---------
3084
(1 row)
select round(extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05
13:00:01'))/360) as hours;
hours
-------
514
(1 row)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: interval as hours or minutes ?
2007-02-07 17:03 interval as hours or minutes ? Aarni Ruuhimäki <aarni@kymi.com>
2007-02-07 22:09 ` Re: interval as hours or minutes ? Jan Muszynski <postgres@jancm.org>
@ 2007-02-08 10:01 ` Aarni Ruuhimäki <aarni@kymi.com>
2007-02-08 10:06 ` Re: interval as hours or minutes ? Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 1 reply; 8+ messages in thread
From: Aarni Ruuhimäki @ 2007-02-08 10:01 UTC (permalink / raw)
To: Jan Muszynski <postgres@jancm.org>; +Cc: pgsql-sql
On Thursday 08 February 2007 00:09, you wrote:
> select extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp
> '2007-02-05 13:00:01'))/60 as minutes;
>
> minutes
> ------------------
> 3083.98333333333
> (1 row)
>
> select round(extract(epoch from (timestamp '2007-02-07 16:24:00' -
> timestamp '2007-02-05 13:00:01'))/60) as minutes;
>
> minutes
> ---------
> 3084
> (1 row)
Hi Guys,
Charming !
Furher still, I would only want full minutes.
select extract(epoch from (SELECT SUM(stop_date_time - start_date_time) FROM
work_times WHERE user_id = 10 AND start_date_time >= '2007-01-01' AND
stop_date_time <= '2007-02-28')/60) as mins;
mins
-------------
3728.733333
(1 row)
select convert_interval((SELECT SUM(stop_date_time - start_date_time) FROM
work_times WHERE user_id = 10 AND start_date_time >= '2007-01-01' AND
stop_date_time <= '2007-02-28'),'minutes') as minutes;
minutes
--------------------------
3728.73333333333 minutes
(1 row)
select round(extract(epoch from (SELECT SUM(stop_date_time - start_date_time)
FROM work_times WHERE user_id = 10))/60) as mins;
mins
------
3729
(1 row)
So instead of rounding up to 3729 the result would have to be 'stripped' to
3728 ?
Thanks,
--
Aarni Ruuhimäki
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: interval as hours or minutes ?
2007-02-07 17:03 interval as hours or minutes ? Aarni Ruuhimäki <aarni@kymi.com>
2007-02-07 22:09 ` Re: interval as hours or minutes ? Jan Muszynski <postgres@jancm.org>
2007-02-08 10:01 ` Re: interval as hours or minutes ? Aarni Ruuhimäki <aarni@kymi.com>
@ 2007-02-08 10:06 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-02-08 11:17 ` Re: interval as hours or minutes ? Aarni Ruuhimäki <aarni@kymi.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bart Degryse @ 2007-02-08 10:06 UTC (permalink / raw)
To: pgsql-sql
Use trunc instead of round.
Also take a look at ceil and floor functions
>>> Aarni Ruuhimäki <aarni@kymi.com> 2007-02-08 11:01 >>>
On Thursday 08 February 2007 00:09, you wrote:
> select extract(epoch from (timestamp '2007-02-07 16:24:00' -
timestamp
> '2007-02-05 13:00:01'))/60 as minutes;
>
> minutes
> ------------------
> 3083.98333333333
> (1 row)
>
> select round(extract(epoch from (timestamp '2007-02-07 16:24:00' -
> timestamp '2007-02-05 13:00:01'))/60) as minutes;
>
> minutes
> ---------
> 3084
> (1 row)
Hi Guys,
Charming !
Furher still, I would only want full minutes.
select extract(epoch from (SELECT SUM(stop_date_time - start_date_time)
FROM
work_times WHERE user_id = 10 AND start_date_time >= '2007-01-01' AND
stop_date_time <= '2007-02-28')/60) as mins;
mins
-------------
3728.733333
(1 row)
select convert_interval((SELECT SUM(stop_date_time - start_date_time)
FROM
work_times WHERE user_id = 10 AND start_date_time >= '2007-01-01' AND
stop_date_time <= '2007-02-28'),'minutes') as minutes;
minutes
--------------------------
3728.73333333333 minutes
(1 row)
select round(extract(epoch from (SELECT SUM(stop_date_time -
start_date_time)
FROM work_times WHERE user_id = 10))/60) as mins;
mins
------
3729
(1 row)
So instead of rounding up to 3729 the result would have to be
'stripped' to
3728 ?
Thanks,
--
Aarni Ruuhimäki
---------------------------(end of
broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: interval as hours or minutes ?
2007-02-07 17:03 interval as hours or minutes ? Aarni Ruuhimäki <aarni@kymi.com>
2007-02-07 22:09 ` Re: interval as hours or minutes ? Jan Muszynski <postgres@jancm.org>
2007-02-08 10:01 ` Re: interval as hours or minutes ? Aarni Ruuhimäki <aarni@kymi.com>
2007-02-08 10:06 ` Re: interval as hours or minutes ? Bart Degryse <Bart.Degryse@indicator.be>
@ 2007-02-08 11:17 ` Aarni Ruuhimäki <aarni@kymi.com>
0 siblings, 0 replies; 8+ messages in thread
From: Aarni Ruuhimäki @ 2007-02-08 11:17 UTC (permalink / raw)
To: pgsql-sql
Ahh,
Forgot about trunc() in the midst of all this ...
Thank you guys again !
Aarni
On Thursday 08 February 2007 12:06, Bart Degryse wrote:
> Use trunc instead of round.
> Also take a look at ceil and floor functions
>
> >>> Aarni Ruuhimäki <aarni@kymi.com> 2007-02-08 11:01 >>>
>
> On Thursday 08 February 2007 00:09, you wrote:
> > select extract(epoch from (timestamp '2007-02-07 16:24:00' -
>
> timestamp
>
> > '2007-02-05 13:00:01'))/60 as minutes;
> >
> > minutes
> > ------------------
> > 3083.98333333333
> > (1 row)
> >
> > select round(extract(epoch from (timestamp '2007-02-07 16:24:00' -
> > timestamp '2007-02-05 13:00:01'))/60) as minutes;
> >
> > minutes
> > ---------
> > 3084
> > (1 row)
>
> Hi Guys,
>
> Charming !
>
> Furher still, I would only want full minutes.
>
> select extract(epoch from (SELECT SUM(stop_date_time - start_date_time)
> FROM
> work_times WHERE user_id = 10 AND start_date_time >= '2007-01-01' AND
> stop_date_time <= '2007-02-28')/60) as mins;
> mins
> -------------
> 3728.733333
> (1 row)
>
> select convert_interval((SELECT SUM(stop_date_time - start_date_time)
> FROM
> work_times WHERE user_id = 10 AND start_date_time >= '2007-01-01' AND
> stop_date_time <= '2007-02-28'),'minutes') as minutes;
> minutes
> --------------------------
> 3728.73333333333 minutes
> (1 row)
>
> select round(extract(epoch from (SELECT SUM(stop_date_time -
> start_date_time)
> FROM work_times WHERE user_id = 10))/60) as mins;
> mins
> ------
> 3729
> (1 row)
>
> So instead of rounding up to 3729 the result would have to be
> 'stripped' to
> 3728 ?
>
> Thanks,
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-02-08 11:17 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-07 17:03 interval as hours or minutes ? Aarni Ruuhimäki <aarni@kymi.com>
2007-02-07 17:33 ` Bricklen Anderson <banderson@presinet.com>
2007-02-07 17:44 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-02-07 21:25 ` Bruno Wolff III <bruno@wolff.to>
2007-02-07 22:09 ` Jan Muszynski <postgres@jancm.org>
2007-02-08 10:01 ` Aarni Ruuhimäki <aarni@kymi.com>
2007-02-08 10:06 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-02-08 11:17 ` Aarni Ruuhimäki <aarni@kymi.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox