Thread: How to find number of seconds between 2 timestamps

I am trying to calculate a rate per second and am having trouble getting the
number of seconds between the two timestamps to use in the formula.

overview=> select extract(epoch from interval '1 day'::interval); date_part
-----------    86400
(1 row)
overview=> select extract(epoch from interval ('2009-02-16
22:15:28.034567-06'::timestamp with time zone - '2009-02-15
22:15:28.034567-06'::timestamp with time zone));
ERROR:  syntax error at or near "'2009-02-16 22:15:28.034567-06'"
LINE 1: select extract(epoch from interval ('2009-02-16 22:15:28.034...

Any suggestions would be helpful,

Thanks,
Woody

----------------------------------------
iGLASS Networks
3300 Green Level Rd. West
Cary NC 27519
(919) 387-3550 x813
www.iglass.net

Woody Woodring wrote:
> I am trying to calculate a rate per second and am having trouble getting the
> number of seconds between the two timestamps to use in the formula.
> 
> overview=> select extract(epoch from interval '1 day'::interval);
>   date_part
> -----------
>      86400
> (1 row)
>  
> overview=> select extract(epoch from interval ('2009-02-16
> 22:15:28.034567-06'::timestamp with time zone - '2009-02-15
> 22:15:28.034567-06'::timestamp with time zone));
> ERROR:  syntax error at or near "'2009-02-16 22:15:28.034567-06'"

The problem is with "interval (...)" - you can say "interval 'constant'"
or "(expression)::interval".

In your case, since you are subtracting two timestamptz types you will
automatically get an interval type. So, you don't need to do anything:

select extract(epoch from ('2009-02-16 22:15:28.034567-06'::timestamp
with time zone - '2009-02-15 22:15:28.034567-06'::timestamp with time
zone));date_part
-----------    86400
(1 row)

HTH

--  Richard Huxton Archonet Ltd