Finding the difference of times in oracle sql

Question

I have a table with two columns(datatype is varchar2) having timestamps. I want to find the difference of timestamps i.e; timestamp difference between ADDITIONAL_COLUMN2 and ADDITIONAL_COLUMN1.

My table columns :

I tried below code but it is giving me error.

SELECT (extract(DAY FROM ADDITIONAL_COLUMN2-ADDITIONAL_COLUMN1)*24*60*60)+ 
(extract(HOUR FROM ADDITIONAL_COLUMN2-ADDITIONAL_COLUMN1)*60*60)+
(extract(MINUTE FROM ADDITIONAL_COLUMN2-ADDITIONAL_COLUMN1)*60)+
extract(SECOND FROM ADDITIONAL_COLUMN2-ADDITIONAL_COLUMN1) into diff
FROM Table;

Error: missing keyword.

Query:

SELECT (extract(DAY FROM to_timestamp_tz(ADDITIONAL_COLUMN2)-to_timestamp_tz(ADDITIONAL_COLUMN1))*24*60*60)+
(extract(HOUR FROM to_timestamp_tz(ADDITIONAL_COLUMN2)-to_timestamp_tz(ADDITIONAL_COLUMN1))*60*60)+
(extract(MINUTE FROM to_timestamp_tz(ADDITIONAL_COLUMN2)-to_timestamp_tz(ADDITIONAL_COLUMN1))*60)+
extract(SECOND FROM to_timestamp_tz(ADDITIONAL_COLUMN2)-to_timestamp_tz(ADDITIONAL_COLUMN1))
FROM MISIMD_FOM_SPM_AUDIT;

Query 2:

with cte (diff) as (
  select to_timestamp_tz(additional_column2, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM')
       - to_timestamp_tz(additional_column1, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM')
       as diff
  from MISIMD_FOM_SPM_AUDIT
)
select (extract(day from diff)*24*60*60)
     + (extract(hour from diff)*60*60)
     + (extract(minute from diff)*60)
     + extract(second from diff) as diff
from cte;

This query is giving me the required output. Output of Query 2

Answer 1

The "ORA-00905: missing keyword" error is because you have into instead of as; changing that:

... AS diff

now works, but...

i changed into to as. I was getting the below error ORA-30076: invalid extract field for extract source

which means your columns aren't actually timestamps, they're strings, so you need to convert them to actual timestamps first:

    with cte (ts1, ts2) as (
      select to_timestamp_tz(additional_column1, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM'),
             to_timestamp_tz(additional_column2, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM')
      from your_table
    )
    select (extract(day from ts2-ts1)*24*60*60)
         + (extract(hour from ts2-ts1)*60*60)
         + (extract(minute from ts2-ts1)*60)
         + extract(second from ts2-ts1) as diff
    from cte;

or do the calculation in the CTE too:

with cte (diff) as (
  select to_timestamp_tz(additional_column2, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM')
       - to_timestamp_tz(additional_column1, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM')
       as diff
  from your_table
)
select (extract(day from diff)*24*60*60)
     + (extract(hour from diff)*60*60)
     + (extract(minute from diff)*60)
     + extract(second from diff) as diff
from cte;

db<>fiddle

Answer 2

I execute the following test case, which obviously uses my own NLS settings:

PARAMETER                                VALUE
---------------------------------------- ----------------------------------------
NLS_RDBMS_VERSION                        12.2.0.1.0
NLS_NCHAR_CONV_EXCP                      FALSE
NLS_LENGTH_SEMANTICS                     BYTE
NLS_COMP                                 BINARY
NLS_DUAL_CURRENCY                        $
NLS_TIMESTAMP_TZ_FORMAT                  DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT                       HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT                     DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT                          HH.MI.SSXFF AM
NLS_SORT                                 BINARY
NLS_DATE_LANGUAGE                        AMERICAN

PARAMETER                                VALUE
---------------------------------------- ----------------------------------------
NLS_DATE_FORMAT                          DD-MON-RR
NLS_CALENDAR                             GREGORIAN
NLS_NUMERIC_CHARACTERS                   .,
NLS_NCHAR_CHARACTERSET                   AL16UTF16
NLS_CHARACTERSET                         AL32UTF8
NLS_ISO_CURRENCY                         AMERICA
NLS_CURRENCY                             $
NLS_TERRITORY                            AMERICA
NLS_LANGUAGE                             AMERICAN

SQL> create table my_test ( c1 timestamp , c2 timestamp ) ;

Table created.

SQL> insert into my_test values ( systimestamp , null ) ;

1 row created.

SQL> -- I wait for a couple of seconds 
SQL> update my_test set c2=systimestamp ;

1 row updated.

SQL> commit;

Commit complete.

SQL> col c1 for a40
SQL> col c2 for a40
SQL> select c1,c2 from my_test ;

C1                                       C2
---------------------------------------- ----------------------------------------
30-JUN-20 11.03.01.262132 AM             30-JUN-20 11.03.16.079616 AM

SQL>  SELECT (extract(DAY FROM c2-C1)*24*60*60)+
  2  (extract(HOUR FROM C2-C1)*60*60)+
  3  (extract(MINUTE FROM C2-C1)*60)+
  4  extract(SECOND FROM C2-C1)
  5* FROM my_test
SQL> /

(EXTRACT(DAYFROMC2-C1)*24*60*60)+(EXTRACT(HOURFROMC2-C1)*60*60)+(EXTRACT(MINUTEFROMC2-C1)*60)+EXTRACT(SECONDFROMC2-C1)
----------------------------------------------------------------------------------------------------------------------
                                                                                                             14.817484

SQL>

You cannot put an INTO inside a query, that is PL/SQL, not SQL. Exactly what is the problem here, because your query works for me, as long as I removed the INTO part of it.

Update Answer

As your case uses varchar2 for timestamp columns and stores the timezone with it ( which is non sense if you ask me ) the case is different. You need to apply to your query a transformation to convert the varchar2 into a right timestamp.

SQL> create table my_test ( c1 varchar2(100), c2 varchar2(100) );

Table created.

SQL> insert into my_test values ( to_char(systimestamp) , null );

1 row created.

SQL> update my_test set c2=to_char(systimestamp) ;

1 row updated.

SQL> commit;

Commit complete.

SQL> set lines 200
SQL> col c1 for a50
SQL> col c2 for a50
SQL> select c1,c2 from my_test ;

C1                                                 C2
-------------------------------------------------- --------------------------------------------------
30-JUN-20 11.12.11.094964 AM +02:00                30-JUN-20 11.12.26.082914 AM +02:00

Here you can see that the values are stored as varchar2 but with timestamp with time zone format.

SQL> select to_timestamp_tz(c1) , to_timestamp_tz(c2) from my_test ;

TO_TIMESTAMP_TZ(C1)                                                         TO_TIMESTAMP_TZ(C2)
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
30-JUN-20 11.12.11.094964000 AM +02:00                                      30-JUN-20 11.12.26.082914000 AM +02:00

Now, the same query you use before

SQL> SELECT (extract(DAY FROM to_timestamp_tz(c2)-to_timestamp_tz(C1))*24*60*60)+
  2  (extract(HOUR FROM to_timestamp_tz(c2)-to_timestamp_tz(C1))*60*60)+
  3  (extract(MINUTE FROM to_timestamp_tz(c2)-to_timestamp_tz(C1))*60)+
  4  extract(SECOND FROM to_timestamp_tz(c2)-to_timestamp_tz(C1))
FROM my_test;  5

(EXTRACT(DAYFROMTO_TIMESTAMP_TZ(C2)-TO_TIMESTAMP_TZ(C1))*24*60*60)+(EXTRACT(HOURFROMTO_TIMESTAMP_TZ(C2)-TO_TIMESTAMP_TZ(C1))*60*60)+(EXTRACT(MINUTEFROMTO_TIMESTAMP_TZ(C2)-TO_TIMESTAMP_TZ(C1))*60)+EXT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                                       14.98795

SQL>

Hope it helps.