Use EPOCH time for timestamp to get records within 1 minute

0

votes

I was curious to see how in Oracle 12c you can take a timestamp datatype and convert the records into EPOCH time to make them a number and then use that number to find any records within that date column that are within 1 minute of each other (assuming the same day if needed, or simply any calculations within 1 minute).

I tried the following but got an ORA-01873: the leading precision of the interval is too small error.

select (sold_date - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))*86400 as epoch_sold_date from test1;

oracletimestampepoch

1

votes

If you are just looking to compare dates and find rows that are within one minute of each other, you do not need to use epoch time. There are several solutions to this problem on this thread.

1

votes

What is SOLD_DATE? For e.g. SYSDATE (function that returns DATE datatype), your code works OK.

SQL> select (sysdate
  2          - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
  3         ) * 86400 as epoch_sold_date
  4  from dual;

EPOCH_SOLD_DATE
---------------
     1600807918

SQL>

As SOLD_DATE is a timestamp, but - it appears that fractions of a second aren't or special interest to you, cast it to DATE:

select (cast (systimestamp as date)                                --> this
        - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
       ) * 86400 as epoch_sold_date 
from dual;

Saying that you get the same result for all rows: well, I don't, and you shouldn't either if SOLD_DATE differs.

SQL> with test (sold_date) as
  2    (select timestamp '2020-09-22 00:00:00.000000' from dual union all
  3     select timestamp '2015-03-18 00:00:00.000000' from dual
  4    )
  5  select sold_date,
  6         (cast (sold_date as date)
  7          - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
  8         ) * 86400 as epoch_sold_date
  9  from test;

SOLD_DATE                      EPOCH_SOLD_DATE
------------------------------ ---------------
22.09.20 00:00:00,000000000         1600732800
18.03.15 00:00:00,000000000         1426636800

SQL>

One more edit: when you subtract two timestamps, result is interval day to second. If you extract minutes from it, you get what you wanted:

SQL> with test (sold_date) as
  2    (select timestamp '2020-09-22 10:15:00.000000' from dual union all
  3     select timestamp '2015-03-18 08:05:00.000000' from dual
  4    )
  5  select sold_date,
  6         lead(sold_date) over (order by sold_date) next_sold_date,
  7         --
  8         lead(sold_date) over (order by sold_date) - sold_date diff,
  9         --
 10         extract(day from lead(sold_date) over (order by sold_date) - sold_date) diff_mins
 11  from test
 12  order by sold_date;

SOLD_DATE                      NEXT_SOLD_DATE                 DIFF                            DIFF_MINS
------------------------------ ------------------------------ ------------------------------ ----------
18.03.15 08:05:00,000000000    22.09.20 10:15:00,000000000    +000002015 02:10:00.000000000        2015
22.09.20 10:15:00,000000000

SQL>

In your case, you'd check whether extracted minutes value is larger than 1 (minute).

If you just want to see how many minutes are there between two timestamps, then

cast them to dates
subtract those dates (and you'll get number of days)
multiply it by 24 (as there are 24 hours in a day) and by 60 (as there are 60 minutes in an hour)

Something like this:

SQL> with test (date_1, date_2) as
  2    (select timestamp '2020-09-22 10:15:00.000000',
  3            timestamp '2020-09-22 08:05:00.000000' from dual
  4    )
  5  select (cast(date_1 as date) - cast(date_2 as date)) * 24 * 60 diff_minutes
  6  from test;

DIFF_MINUTES
------------
         130

SQL>

Use EPOCH time for timestamp to get records within 1 minute

2 Answers