pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFormatting an Interval
7+ messages / 6 participants
[nested] [flat]
* Formatting an Interval
@ 2004-12-31 16:37 Jamison Roberts <jamisonroberts@gmail.com>
2004-12-31 19:41 ` Re: Formatting an Interval Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 7+ messages in thread
From: Jamison Roberts @ 2004-12-31 16:37 UTC (permalink / raw)
To: pgsql-sql
All of the functions that i've looked at seem to only extract parts
from Intervals. What I need to do is to format the interval. For
instance, I have a Interval with the value 1 day 07:57:52. I would
like that in HH:MM:SS. So in the example the output would be
31:57:52.
How can this be done?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Formatting an Interval
2004-12-31 16:37 Formatting an Interval Jamison Roberts <jamisonroberts@gmail.com>
@ 2004-12-31 19:41 ` Michael Fuhr <mike@fuhr.org>
2005-01-02 16:19 ` Re: Formatting an Interval Karel Zak <zakkr@zf.jcu.cz>
2005-01-03 23:02 ` Re: Formatting an Interval Edmund Bacon <ebacon@onesystem.com>
0 siblings, 2 replies; 7+ messages in thread
From: Michael Fuhr @ 2004-12-31 19:41 UTC (permalink / raw)
To: Jamison Roberts <jamisonroberts@gmail.com>; +Cc: pgsql-sql
On Fri, Dec 31, 2004 at 11:37:32AM -0500, Jamison Roberts wrote:
> All of the functions that i've looked at seem to only extract parts
> from Intervals. What I need to do is to format the interval. For
> instance, I have a Interval with the value 1 day 07:57:52. I would
> like that in HH:MM:SS. So in the example the output would be
> 31:57:52.
I'm not aware of a built-in way to get such a format; somebody
please correct me if I'm mistaken.
You could write a function to format the interval. For example,
with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to
convert the interval to a number of seconds; convert that to hours,
minutes, and seconds; and use TO_CHAR to format the return value.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Formatting an Interval
2004-12-31 16:37 Formatting an Interval Jamison Roberts <jamisonroberts@gmail.com>
2004-12-31 19:41 ` Re: Formatting an Interval Michael Fuhr <mike@fuhr.org>
@ 2005-01-02 16:19 ` Karel Zak <zakkr@zf.jcu.cz>
2005-01-02 16:39 ` Re: Formatting an Interval Michael Fuhr <mike@fuhr.org>
2005-01-02 16:46 ` Re: Formatting an Interval Kretschmer Andreas <andreas_kretschmer@despammed.com>
1 sibling, 2 replies; 7+ messages in thread
From: Karel Zak @ 2005-01-02 16:19 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Jamison Roberts <jamisonroberts@gmail.com>; pgsql-sql
On Fri, 2004-12-31 at 12:41 -0700, Michael Fuhr wrote:
> On Fri, Dec 31, 2004 at 11:37:32AM -0500, Jamison Roberts wrote:
>
> > All of the functions that i've looked at seem to only extract parts
> > from Intervals. What I need to do is to format the interval. For
> > instance, I have a Interval with the value 1 day 07:57:52. I would
> > like that in HH:MM:SS. So in the example the output would be
> > 31:57:52.
>
> I'm not aware of a built-in way to get such a format; somebody
> please correct me if I'm mistaken.
>
> You could write a function to format the interval. For example,
> with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to
> convert the interval to a number of seconds; convert that to hours,
> minutes, and seconds; and use TO_CHAR to format the return value.
to_char() works with standard date/time ranges, for example 1-24 -- so
there is no way how convert to anything like "31:57:52".
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Formatting an Interval
2004-12-31 16:37 Formatting an Interval Jamison Roberts <jamisonroberts@gmail.com>
2004-12-31 19:41 ` Re: Formatting an Interval Michael Fuhr <mike@fuhr.org>
2005-01-02 16:19 ` Re: Formatting an Interval Karel Zak <zakkr@zf.jcu.cz>
@ 2005-01-02 16:39 ` Michael Fuhr <mike@fuhr.org>
2005-01-02 17:09 ` Re: Formatting an Interval Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
1 sibling, 1 reply; 7+ messages in thread
From: Michael Fuhr @ 2005-01-02 16:39 UTC (permalink / raw)
To: Karel Zak <zakkr@zf.jcu.cz>; +Cc: Jamison Roberts <jamisonroberts@gmail.com>; pgsql-sql
On Sun, Jan 02, 2005 at 05:19:23PM +0100, Karel Zak wrote:
> On Fri, 2004-12-31 at 12:41 -0700, Michael Fuhr wrote:
>
> > You could write a function to format the interval. For example,
> > with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to
> > convert the interval to a number of seconds; convert that to hours,
> > minutes, and seconds; and use TO_CHAR to format the return value.
>
> to_char() works with standard date/time ranges, for example 1-24 -- so
> there is no way how convert to anything like "31:57:52".
TO_CHAR() works with several types, including integer, numeric, and
double precision. If you've broken the interval into three variables,
one containing hours, one containing minutes, and one containing
seconds, then you can use TO_CHAR() to add leading zeroes where
needed.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Formatting an Interval
2004-12-31 16:37 Formatting an Interval Jamison Roberts <jamisonroberts@gmail.com>
2004-12-31 19:41 ` Re: Formatting an Interval Michael Fuhr <mike@fuhr.org>
2005-01-02 16:19 ` Re: Formatting an Interval Karel Zak <zakkr@zf.jcu.cz>
2005-01-02 16:39 ` Re: Formatting an Interval Michael Fuhr <mike@fuhr.org>
@ 2005-01-02 17:09 ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
0 siblings, 0 replies; 7+ messages in thread
From: Pierre-Frédéric Caillaud @ 2005-01-02 17:09 UTC (permalink / raw)
To: pgsql-sql
Note that there will be a loss of precision as an interval of 1 month, for
instance, does not mean any specific number of days, as :
1 february + 1 month = 1 march (1 month = 28 or 29 days)
1 december + 1 month = 1 january (1 month = 31 days)
Same for years etc.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Formatting an Interval
2004-12-31 16:37 Formatting an Interval Jamison Roberts <jamisonroberts@gmail.com>
2004-12-31 19:41 ` Re: Formatting an Interval Michael Fuhr <mike@fuhr.org>
2005-01-02 16:19 ` Re: Formatting an Interval Karel Zak <zakkr@zf.jcu.cz>
@ 2005-01-02 16:46 ` Kretschmer Andreas <andreas_kretschmer@despammed.com>
1 sibling, 0 replies; 7+ messages in thread
From: Kretschmer Andreas @ 2005-01-02 16:46 UTC (permalink / raw)
To: pgsql-sql
am Sun, dem 02.01.2005, um 17:19:23 +0100 mailte Karel Zak folgendes:
> > You could write a function to format the interval. For example,
> > with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to
> > convert the interval to a number of seconds; convert that to hours,
> > minutes, and seconds; and use TO_CHAR to format the return value.
>
> to_char() works with standard date/time ranges, for example 1-24 -- so
> there is no way how convert to anything like "31:57:52".
Read again.
Extract the seconds and calculate the hours, minutes and seconds.
test_db=# select extract (epoch from '1 day 07:57:52'::interval);
date_part
-----------
115072
(1 Zeile)
Okay, 115072 Seconds.
test_db=# select extract (day from '1 day 07:57:52'::interval);
date_part
-----------
1
(1 Zeile)
Okay, this is 1 day, 24 hours.
test_db=# select extract (epoch from '1 day 07:57:52'::interval) - 60*60*24*(extract (day from '1 day 07:57:52'::interval));
?column?
----------
28672
(1 Zeile)
Okay, 24 hours and 28672 seconds, and you know, this is less then 1 day.
Now calculate, how many hours in 28672 seconds:
test_db=# select 28672 / 3600;
?column?
----------
7
(1 Zeile)
Now you can add 24 hours and 7 hours, the remainder is
test_db=# select 28672 - 3600*7;
?column?
----------
3472
(1 Zeile)
seconds. Now you can calculate the minutes and, finaly, the seconds.
Is the way now okay? Write a function for this job.
Regards, Andreas
--
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Formatting an Interval
2004-12-31 16:37 Formatting an Interval Jamison Roberts <jamisonroberts@gmail.com>
2004-12-31 19:41 ` Re: Formatting an Interval Michael Fuhr <mike@fuhr.org>
@ 2005-01-03 23:02 ` Edmund Bacon <ebacon@onesystem.com>
1 sibling, 0 replies; 7+ messages in thread
From: Edmund Bacon @ 2005-01-03 23:02 UTC (permalink / raw)
To: pgsql-sql; +Cc: Jamison Roberts <jamisonroberts@gmail.com>
Michael Fuhr wrote:
> On Fri, Dec 31, 2004 at 11:37:32AM -0500, Jamison Roberts wrote:
>
>
>>All of the functions that i've looked at seem to only extract parts
>>from Intervals. What I need to do is to format the interval. For
>>instance, I have a Interval with the value 1 day 07:57:52. I would
>>like that in HH:MM:SS. So in the example the output would be
>>31:57:52.
>
>
> I'm not aware of a built-in way to get such a format; somebody
> please correct me if I'm mistaken.
>
> You could write a function to format the interval. For example,
> with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to
> convert the interval to a number of seconds; convert that to hours,
> minutes, and seconds; and use TO_CHAR to format the return value.
>
Something like this, perhaps:
create or replace function interval_to_hms(interval)
returns text
language 'plpgsql'
as 'declare
interval_sec integer;
sec integer;
min integer;
hr integer;
begin
select into interval_sec extract (epoch from $1);
sec := interval_sec % 60;
min := (interval_sec/60)%60;
hr := (interval_sec/3600);
return hr || '':'' || to_char(min, ''FM00'') || '':'' ||
to_char(sec, ''FM00'');
end;';
--
Edmund Bacon <ebacon@onesystem.com>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-01-03 23:02 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-31 16:37 Formatting an Interval Jamison Roberts <jamisonroberts@gmail.com>
2004-12-31 19:41 ` Michael Fuhr <mike@fuhr.org>
2005-01-02 16:19 ` Karel Zak <zakkr@zf.jcu.cz>
2005-01-02 16:39 ` Michael Fuhr <mike@fuhr.org>
2005-01-02 17:09 ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
2005-01-02 16:46 ` Kretschmer Andreas <andreas_kretschmer@despammed.com>
2005-01-03 23:02 ` Edmund Bacon <ebacon@onesystem.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