Обсуждение: Date difference in seconds

Hi ,
         Please consider the following schema and the query given below and
let me know how to get the results I want.

CREATE TABLE  temp (
       id                   INTEGER  NOT NULL,
       start_time       TIMESTAMP,
       end_time        TIMESTAMP,
       PRIMARY KEY (id)
);

Now I want the difference between start time and end time in seconds.
        Select date_part('seconds',start_time - end_time) from temp;  gives
me only the difference between the seconds value in both timestamps. It
ignores all the days, hrs, minutes etc.

My problem is that I want the actual difference between the two timestamps
expressed in seconds.

Please advice me how to write the SQL Query.

With Regards,
Raghunath T
Senior Software Engg,
MindTree Consulting Private Ltd,
Santa Clara, CA, USA

"Raghunath T" <raghunatht@mindtree.com> writes:
> Now I want the difference between start time and end time in seconds.
>         Select date_part('seconds',start_time - end_time) from temp;  gives
> me only the difference between the seconds value in both timestamps. It
> ignores all the days, hrs, minutes etc.

Try  "date_part('epoch',start_time - end_time)".  See
http://www.postgresql.org/devel-corner/docs/postgres/functions-datetime.html

            regards, tom lane

On Mon, 19 Feb 2001, Raghunath T wrote:

> Hi ,
>          Please consider the following schema and the query given below and
> let me know how to get the results I want.
>
> CREATE TABLE  temp (
>        id                   INTEGER  NOT NULL,
>        start_time       TIMESTAMP,
>        end_time        TIMESTAMP,
>        PRIMARY KEY (id)
> );
>
> Now I want the difference between start time and end time in seconds.
>         Select date_part('seconds',start_time - end_time) from temp;  gives
> me only the difference between the seconds value in both timestamps. It
> ignores all the days, hrs, minutes etc.
>
> My problem is that I want the actual difference between the two timestamps
> expressed in seconds.

You should be able to get that with date_part('epoch', start_time-end_time)
(or more likely end_time-start_time unless you want a negative number)