pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedformatting intervals with to_char
3+ messages / 2 participants
[nested] [flat]
* formatting intervals with to_char
@ 2006-10-03 23:03 Graham Davis <gdavis@refractions.net>
0 siblings, 1 reply; 3+ messages in thread
From: Graham Davis @ 2006-10-03 23:03 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm trying to format the output of a time interval so that it displays
as HH:MM:SS no matter how many days it spans. So for instance, an
interval of 2 days 4 hours and 0 minutes would look something like
"52:00:00". The documentation for to_char states that:
"|to_char(interval)| formats HH and HH12 as hours in a single day, while
HH24 can output hours exceeding a single day, e.g. >24."
However I can not get it to work with time intervals that span more than
1 day. For instance, the following query returns this time interval:
Query:
select ('2006-09-15T23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp);
Result:
14 days 14:28:19
But when I run to_char on this with HH24, it doesn't take into effect
the number of days:
Query:
select to_char(('2006-09-15T23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp), 'HH24:MI:SS');
Result:
14:28:19
It just gives me the offset of hours, min, seconds on that 14th day.
The result I'm looking for is: 350:28:19
What am I doing wrong, or how can I get this desired output? Thanks,
--
Graham Davis
Refractions Research Inc.
gdavis@refractions.net
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: formatting intervals with to_char
@ 2006-10-04 18:03 Graham Davis <gdavis@refractions.net>
parent: Graham Davis <gdavis@refractions.net>
0 siblings, 1 reply; 3+ messages in thread
From: Graham Davis @ 2006-10-04 18:03 UTC (permalink / raw)
To: pgsql-sql
I haven't heard any replies from this, so in the meantime I've found a
hacky way to get the output I desire. I'm basically calculating the
hours on the fly and piecing together a formatted string with
concatenations like this:
SELECT
(((EXTRACT(day from time_idle)*24)+EXTRACT(hour from time_idle)) || ':' ||
EXTRACT(minute from time_idle) || ':' || EXTRACT(second from
time_idle))::interval AS myinterval
FROM
( SELECT ('2006-09-15T23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp) AS time_idle) FROM_TABLE;
If anyone knows a better/proper way to get this result, let me know.
Thanks,
Graham.
Graham Davis wrote:
> Hi,
>
> I'm trying to format the output of a time interval so that it displays
> as HH:MM:SS no matter how many days it spans. So for instance, an
> interval of 2 days 4 hours and 0 minutes would look something like
> "52:00:00". The documentation for to_char states that:
>
> "|to_char(interval)| formats HH and HH12 as hours in a single day,
> while HH24 can output hours exceeding a single day, e.g. >24."
>
> However I can not get it to work with time intervals that span more
> than 1 day. For instance, the following query returns this time
> interval:
>
> Query:
> select ('2006-09-15T23:59:00'::timestamp - '2006-09-01
> 09:30:41'::timestamp);
>
> Result:
> 14 days 14:28:19
>
> But when I run to_char on this with HH24, it doesn't take into effect
> the number of days:
>
> Query:
> select to_char(('2006-09-15T23:59:00'::timestamp - '2006-09-01
> 09:30:41'::timestamp), 'HH24:MI:SS');
>
> Result:
> 14:28:19
>
> It just gives me the offset of hours, min, seconds on that 14th day.
> The result I'm looking for is: 350:28:19
>
> What am I doing wrong, or how can I get this desired output? Thanks,
>
--
Graham Davis
Refractions Research Inc.
gdavis@refractions.net
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: formatting intervals with to_char
@ 2006-10-05 04:39 Aaron Bono <postgresql@aranya.com>
parent: Graham Davis <gdavis@refractions.net>
0 siblings, 0 replies; 3+ messages in thread
From: Aaron Bono @ 2006-10-05 04:39 UTC (permalink / raw)
To: gdavis@refractions.net; +Cc: pgsql-sql
On 10/4/06, Graham Davis <gdavis@refractions.net> wrote:
>
> I haven't heard any replies from this, so in the meantime I've found a
> hacky way to get the output I desire. I'm basically calculating the
> hours on the fly and piecing together a formatted string with
> concatenations like this:
>
> SELECT
> (((EXTRACT(day from time_idle)*24)+EXTRACT(hour from time_idle)) || ':' ||
> EXTRACT(minute from time_idle) || ':' || EXTRACT(second from
> time_idle))::interval AS myinterval
> FROM
> ( SELECT ('2006-09-15T23:59:00'::timestamp - '2006-09-01
> 09:30:41'::timestamp) AS time_idle) FROM_TABLE;
>
> If anyone knows a better/proper way to get this result, let me know.
That would be my approach though you could also replace:
EXTRACT(minute from time_idle) || ':' || EXTRACT(second from time_idle)
with:
to_char(time_idle, ':MM:SS')
If you really want to clean up your SQL you could create a function that
takes any timestamp and then make the function IMMUTABLE to tell postgresql
it doesn't need to rederive the results every time.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-10-05 04:39 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-03 23:03 formatting intervals with to_char Graham Davis <gdavis@refractions.net>
2006-10-04 18:03 ` Graham Davis <gdavis@refractions.net>
2006-10-05 04:39 ` Aaron Bono <postgresql@aranya.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