Postgres Pro
Downloads
Home   >   mailing lists

Thread: cast time interval to seconds

cast time interval to seconds

From
rihad
Date:
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.

Re: cast time interval to seconds

From
Michael Glaesemann
Date:
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

Re: cast time interval to seconds

From
Martijn van Oosterhout
Date:
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

Re: cast time interval to seconds

From
"Pavel Stehule"
Date:
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