formatting intervals with to_char
От | Graham Davis |
---|---|
Тема | formatting intervals with to_char |
Дата | |
Msg-id | 4522EC42.1080500@refractions.net обсуждение исходный текст |
Ответы |
Re: formatting intervals with to_char
timestamp subtraction (was Re: formatting intervals with to_char) |
Список | 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
В списке pgsql-sql по дате отправления: