Finding the age of an event in Oracle

Manngo 0
Viewed 23

I have a table of sales with an ordered column as a timestamp type.

I would like to find the number of days since the last order. I though it should be simple.

I have tried various methods, but I can’t get a meaningful answer:

select max(ordered) from sales;                         --  2022-05-17 22:47:24.467000
select sysdate-max(ordered) from sales;                 --  Unknown column type: 10
select current_time_stamp-max(ordered) from sales;      --  Unknown column type: 10

I want to use the result in a CTE to then add to some other dates, so I thought it should at least result in either an interval type or a number of days.

How can I get the age of the above date?

date-arithmetic oracle
1 Answers

There are 2 common options:

  1. cast timestamp to date and use sysdate - cast(max(...) as date) - in this case you'll get a number in days:
SQL> select sysdate - cast(timestamp'2000-01-01 00:00:00' as date) diff1 from dual;

     DIFF1
----------
8290.97766
  1. use systimestamp - max(...) - in this case you'll get an Interval Day to Second:
SQL> select systimestamp - timestamp'2000-01-01 00:00:00' from dual;

SYSTIMESTAMP-TIMESTAMP'2000-01-0100:00:00'
------------------------------------------
+000008291 00:27:19.105859000
Sayan Malakshinov0
Related