Thread: time interval format srting
Hello, I've been searching the docs on a simple way to convert a time _duration_ in seconds to the format dd:hh:mm:ss, but I can't find it. 90061 --> 1d 1h 1m 1s (90061=24*3600+3600+60+1) any ideas ? I've been using to_char and to_timestamp to format dates/timestamps... but this is diferent... I want to format time intervals, durations.. cheers Joao
On Tue, Nov 4, 2008 at 10:06 AM, Joao Ferreira gmail <joao.miguel.c.ferreira@gmail.com> wrote: > Hello, > > I've been searching the docs on a simple way to convert a time > _duration_ in seconds to the format dd:hh:mm:ss, but I can't find it. > > 90061 --> 1d 1h 1m 1s > > (90061=24*3600+3600+60+1) select number*interval '1 sec';
On Tue, Nov 4, 2008 at 10:53 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Tue, Nov 4, 2008 at 10:06 AM, Joao Ferreira gmail > <joao.miguel.c.ferreira@gmail.com> wrote: >> Hello, >> >> I've been searching the docs on a simple way to convert a time >> _duration_ in seconds to the format dd:hh:mm:ss, but I can't find it. >> >> 90061 --> 1d 1h 1m 1s >> >> (90061=24*3600+3600+60+1) > > select number*interval '1 sec'; OK, that just gives hours:minutes:seconds. You can add and subtract the same timestamp to get something like an interval select ((9084000*interval '1 sec')+timestamp '2008-01-01')-timestamp '2008-01-01';
On Tue, Nov 04, 2008 at 05:06:37PM +0000, Joao Ferreira gmail wrote: > I've been searching the docs on a simple way to convert a time > _duration_ in seconds to the format dd:hh:mm:ss, but I can't find it. > > 90061 --> 1d 1h 1m 1s > > (90061=24*3600+3600+60+1) > > any ideas ? > > I've been using to_char and to_timestamp to format dates/timestamps... > but this is diferent... I want to format time intervals, durations.. How about doing: SELECT justify_interval(90061 * '1 second'::INTERVAL); The reason PG makes it a bit difficult is because of things like daylight savings means that a day can be longer, or shorter, than 24 hours. Sam
am Tue, dem 04.11.2008, um 17:06:37 +0000 mailte Joao Ferreira gmail folgendes:
> Hello,
>
> I've been searching the docs on a simple way to convert a time
> _duration_ in seconds to the format dd:hh:mm:ss, but I can't find it.
>
> 90061 --> 1d 1h 1m 1s
>
> (90061=24*3600+3600+60+1)
>
> any ideas ?
Something like this?
test=*# select (90061 / (24*3600))::text || ' days ' ||
to_char('3661'::interval, 'hh h mi m ss s')::text;
?column?
-----------------------
1 days 01 h 01 m 01 s
(1 Zeile)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Sam Mason <sam@samason.me.uk> schrieb: > On Tue, Nov 04, 2008 at 05:06:37PM +0000, Joao Ferreira gmail wrote: > > I've been searching the docs on a simple way to convert a time > > _duration_ in seconds to the format dd:hh:mm:ss, but I can't find it. > > > > 90061 --> 1d 1h 1m 1s > > > > (90061=24*3600+3600+60+1) > > > > any ideas ? > > > > I've been using to_char and to_timestamp to format dates/timestamps... > > but this is diferent... I want to format time intervals, durations.. > > How about doing: > > SELECT justify_interval(90061 * '1 second'::INTERVAL); Nice, didn't know this function. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Andreas Kretschmer replied: > I wrote: > > How about doing: > > > > SELECT justify_interval(90061 * '1 second'::INTERVAL); > > Nice, didn't know this function. Yup, PG does everything! Not sure when I discovered it; also not sure if I've ever had to use it in anger before. I am, however, slightly embarrassed that I used the ugly form of interval literals, it looks prettier to me as: SELECT justify_interval(90061 * INTERVAL '1 second'); Ah well! Sam