Timestamp difference in SQL

Question

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?

Does this answer your question? [Extracting the total number of seconds from an interval data-type](https://stackoverflow.com/questions/10092032/extracting-the-total-number-of-seconds-from-an-interval-data-type) — Marmite Bomber, Dec 07 '20 at 12:15

MT0 · Answer 1 · 2020-12-07T12:00:15.473

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

score 0 · Answer 2 · answered Dec 07 '20 at 11:40

0

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>

answered Dec 07 '20 at 11:40

Popeye

35,427
4
10
31

Yes, make sense. Suppose the start_id and the end_id is in hours, I mean, the start_id will be mainly 00:00:00 and the end_id something like 02:10:10 (Is a movie that lasts more or less 2 hours), how can I create a table with that and make a select with the difference in hours for example? – Jiakai Dong Dec 07 '20 at 11:51
I am unsure what you are asking but Oracle dates consist of date and time. If you want a number of hours between two dates then you can multiply 24 with a difference between those two dates. – Popeye Dec 07 '20 at 11:54
This gives the wrong answer when the timestamps have fractional seconds. I.e. `START_ID` and `END_ID` of `2020-01-01 01:23:45.9` and `2020-01-01 01:23:46.1` then the difference should be `0.2` but this outputs `1`. – MT0 Dec 07 '20 at 11:56
Yes, It will not consider the fractional part. I agree @MT0 – Popeye Dec 07 '20 at 11:57
I'm not sure if I have to use date, because I want to know how long has the user been watching the movie. For example, if the user started at 00:00:00 of the movie and the user leaves at 01:10:00, I want to know that the user has watched 1 hour and 10 minutes of the movie. – Jiakai Dong Dec 07 '20 at 11:58
@JiakaiDong In Oracle a `DATE` data type always has year, month, day, hour, minute and second components. A `TIMESTAMP` data type always has the same components plus can have fractional seconds (or can have a time zone). – MT0 Dec 07 '20 at 11:59
@JiakaiDong I can see your requirement can be fulfilled with the smallest using as the second. I would suggest going for the `DATE` data type as operations on the `date` is much simpler and faster than the `TIMESTAMP` as can be seen from the MT)'s answer. – Popeye Dec 07 '20 at 12:08
Depends on the requirement; if the OP wants to track start and end times to the frame of the movie then they want a `TIMESTAMP` to handle the fractional seconds. If they don't care about that granularity and just want to start and end at the nearest second then, yes, they probably should not use `TIMESTAMP` and should just use a `DATE` data type. – MT0 Dec 07 '20 at 12:13

Timestamp difference in SQL

2 Answers2