CODEWITHSUNDEEP
sqloracle-database
Jiakai Dong
Jiakai Dong

Reputation: 49

Timestamp difference in SQL

I want to know what is the time difference between two timestamp:

CREATE TABLE HELLO(
    START_ID TIMESTAMP,
    END_ID TIMESTAMP
    );
    
INSERT INTO HELLO (START_ID,END_ID) VALUES (TIMESTAMP '2020-01-01 00:00:01',TIMESTAMP '2020-02-01 10:10:10');

I supposed to use:

SELECT TIMESTAMPDIFF (SECOND , START_ID , END_ID)
FROM HELLO

But Oracle gives me error: ORA-00904: "TIMESTAMPDIFF": invalid identifier. What should I do?

Upvotes: 0

Views: 1380

Answers (2)

MT0
MT0

Reputation: 167972

Since your data types are TIMESTAMP and you could have fractional seconds then you can either:

Subtract one timestamp from another to give an INTERVAL DAY TO SECOND difference (and will account for differences in time zones, if your timestamps have them) and extract the component parts:

SELECT   EXTRACT( DAY    FROM difference ) * 24 * 60 * 60
       + EXTRACT( HOUR   FROM difference ) *      60 * 60
       + EXTRACT( MINUTE FROM difference ) *           60
       + EXTRACT( SECOND FROM difference )
           AS difference
FROM   (
  SELECT end_id - start_id as difference
  FROM   hello
);

or, convert the values to DATE data types by truncating to the previous minute (which would discard any time zone information, if there was any) and then add the difference in seconds extracted from the timestamps:

SELECT ( TRUNC( end_id, 'MI' ) - TRUNC( start_id, 'MI' ) ) * 24 * 60 * 60
       + EXTRACT( SECOND FROM end_id )
       - EXTRACT( SECOND FROM start_id ) as difference
FROM   hello;

(Note: if you naively use CAST to convert to a DATE then you will lose the fractional seconds on both timestamps and may get the wrong answer.)

Which, for the sample data:

CREATE TABLE HELLO(
    START_ID TIMESTAMP,
    END_ID TIMESTAMP
);

INSERT INTO HELLO (
  START_ID,END_ID
) VALUES (
  TIMESTAMP '2020-01-01 00:00:01',TIMESTAMP '2020-02-01 10:10:10'
);
INSERT INTO HELLO (
  START_ID,END_ID
) VALUES (
  TIMESTAMP '2020-01-01 01:23:45.99999',TIMESTAMP '2020-01-01 01:23:46.00001'
);

Outputs:

| DIFFERENCE |
| ---------: |
|    2715009 |
|     .00002 |

db<>fiddle here

Upvotes: 2

Popeye
Popeye

Reputation: 35900

If you subtract one timestamp from another then you will find the INTERVAL and not the number.

If you really want the number of seconds then you can cast timestamp to date and then subtract it from another as follows:

SQL> SELECT (CAST(END_ID AS DATE) - CAST(START_ID AS DATE))*24*60*60 AS NUM_OF_SECONDS
  2    FROM HELLO;

NUM_OF_SECONDS
--------------
       2715009

SQL>

Upvotes: 0

Related Questions