How to select all records year to date in oracle?

Question

I have a table with a field create_date (unix-timestamp). How can I select all records of the past 12 months?

Thanks Thomas

Answer 1

You might try something like the following. Keep in mind that Unix timestamps represent the number of seconds passed since midnight on January 1, 1970:

SELECT * FROM mytable
 WHERE date'1970-01-01' + NUMTODSINTERVAL(create_date, 'SECOND') >= TRUNC(ADD_MONTHS(SYSDATE, -12));

I'm guessing that the value of create_date is somewhere in the area of 1,400,000,000.

Answer 2

You need to convert UNIX Timestamp to date format, then use ADD_MONTHS even to subtract them.

-- SQL to see and verify the dates

select ADD_MONTHS(MYTABLE.CREATE_DATE, -12) from MYTABLE;

-- SQL to use to answer your question

select *
from MYTABLE
where MYTABLE.CREATE_DATE > ADD_MONTHS(SYSTIMESTAMP, -12);