Обсуждение: 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