How to find difference between 2 timestamp | Oracle

I just encounter a problem where the data type of column was varchar2(20)

That column contains timestamp which is of varchar2(20).

How do i need to minus both timestamp to find difference

Table : emp -> dtime_details

dtime_details
2021-03-01 08:36:00
2021-03-01 17:40:00

Exected Output : 9 (Hours)

How do i need to write a query when the data type of dtime_details is varchar2(20)

If it had a data type of timestamp it was easy just need to minus between both.

>Solution :

Don’t store dates and times as strings. Store it as:

• DATE if it has date and time components (but no fractional seconds);
• TIMESTAMP(0) if it has date and time components (but no fractional seconds) and you want it to be treated as a TIMESTAMP and not a DATE;
• TIMESTAMP if it has date, time and fractional seconds.

If you do have a string (don’t) then convert it to one of the above data-types and subtract:

SELECT TRUNC(
         (
           TO_DATE('2021-03-01 17:40:00', 'YYYY-MM-DD HH24:MI:SS')
           - TO_DATE('2021-03-01 08:36:00', 'YYYY-MM-DD HH24:MI:SS')
         ) * 24
       ) AS hours_diff
FROM   DUAL;

or:

SELECT EXTRACT(
         HOUR FROM
         (
           TO_TIMESTAMP('2021-03-01 17:40:00', 'YYYY-MM-DD HH24:MI:SS')
           - TO_TIMESTAMP('2021-03-01 08:36:00', 'YYYY-MM-DD HH24:MI:SS')
         )
       ) AS hours_diff
FROM   DUAL;

Outputs:

fiddle