Thread: cast time interval to seconds
Hi, I have two columns start_time & stop_time declared as "TIME". I'd like to compute the difference between the two times in seconds, all in db: SELECT (CAST(stop_time AS SECONDS) + 86400 - CAST(start_time AS SECONDS)) % 86400; Unfortunately AS SECONDS causes parse error. Any hints? Thanks.
On Sep 10, 2007, at 10:44 , rihad wrote: > SELECT > (CAST(stop_time AS SECONDS) + 86400 - CAST(start_time AS SECONDS)) > % 86400; "seconds" isn't a datatype. Try extract(epoch from (stop_time - start_time)) The manual is quite extensive: http://www.postgresql.org/docs/8.2/interactive/functions- datetime.html#FUNCTIONS-DATETIME-EXTRACT Michael Glaesemann grzm seespotcode net
On Mon, Sep 10, 2007 at 08:44:07PM +0500, rihad wrote: > Hi, I have two columns start_time & stop_time declared as "TIME". I'd > like to compute the difference between the two times in seconds, all in db: > > SELECT > (CAST(stop_time AS SECONDS) + 86400 - CAST(start_time AS SECONDS)) > % 86400; At a guess I'd say you should simply subtract the two (ie stop_time - start_time) and then use extract() to pull the seconds out. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
2007/9/10, Martijn van Oosterhout <kleptog@svana.org>: > On Mon, Sep 10, 2007 at 08:44:07PM +0500, rihad wrote: > > Hi, I have two columns start_time & stop_time declared as "TIME". I'd > > like to compute the difference between the two times in seconds, all in db: > > > > SELECT > > (CAST(stop_time AS SECONDS) + 86400 - CAST(start_time AS SECONDS)) > > % 86400; > > At a guess I'd say you should simply subtract the two (ie stop_time - > start_time) and then use extract() to pull the seconds out. > > Have a nice day, > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > > From each according to his ability. To each according to his ability to litigate. > sample:postgres=# select extract(epoch from time '10:01:30' - time '10:00:00'); date_part ----------- 90 (1 row) Regards Pavel Stehule