time interval format srting

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