From: | Graham Davis <gdavis(at)refractions(dot)net> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: formatting intervals with to_char |
Date: | 2006-10-04 18:03:16 |
Message-ID: | 4523F764.80705@refractions.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers 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(at)refractions(dot)net
From | Date | Subject | |
---|---|---|---|
Next Message | Zdenek Kotala | 2006-10-04 18:21:50 | Updated version of FAQ_Solaris |
Previous Message | Zdenek Kotala | 2006-10-04 17:57:16 | Re: [HACKERS] DOC: catalog.sgml |
From | Date | Subject | |
---|---|---|---|
Next Message | Hector Villarreal | 2006-10-04 18:04:56 | Re: Assigning a timestamp without timezone to a timestamp |
Previous Message | Aaron Bono | 2006-10-04 18:02:38 | Re: i have table |