[SQL] Seconds To Time
Hi all, I'm a newby to PGSQL, I allways use MySQL, but now we use PGSQL, so i had to convert all the MySQL-Query's to PGSQL. In MySQL we have the function Sec_to_time(seconds). This will result a time-value like '40:30:00'. With this Query SELECT TIMESTAMP WITH TIME ZONE 'epoch' + sum(elapsed)/1000 * interval '1 second' from Table1 GROUP BY ... The result is a timestamp from the unixtime + the seconds elapsed. How can I convert this to a time-value? Thanks in advance ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] sum ( time) problem
"Loeke" <[EMAIL PROTECTED]> wrote in Dan heb je nog de 24-uurs notatie, en krijg je niet een result boven de 24-uurs notatie. news:[EMAIL PROTECTED]: > kijk eens bij functions and operators van de > sql help bij pgadmin > > "Willem de Jong" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] >> Hello, >> >> If i do a sum(time) the result is like this '1 day 18:00:00'. But i'd >> like to get a result like this '42:00:00'. >> >> How can i realise is by a query? >> >> thanks in advance, >> >> Willem de Jong. > > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] sum ( time) problem
I'm afraid it is the only solution... Thanks for reply [EMAIL PROTECTED] (Bruno Wolff III) wrote in news:20040601140334.GA24188 @wolff.to: > On Thu, May 27, 2004 at 11:14:58 +0000, > Willem de Jong <[EMAIL PROTECTED]> wrote: >> >> >> >> If i do a sum(time) the result is like this '1 day 18:00:00'. But i'd >> >> like to get a result like this '42:00:00'. >> >> >> >> How can i realise is by a query? > > You can do something like the following: (not completely tested) > select extract(epoch from sum(time))/3600 || ':' || > extract(minutes from sum(time)) || ':' || > extract(seconds from sum(time)); > > I am not sure if the above will work right for negative intervals. > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
