Select entries between two dates by converting Unix timestamp in Oracle Database.

UNIX for Advanced & Expert Users
1

Hi,

I need to select the entries between two dates from an Oracle db. The Oracle db has a column with Unix timestamps. I use the following querry, but it doesnt seem to be working as desired.

select count(*) from reporter_status where to_char(FIRSTOCCURRENCE, 'mm-dd-yy') between ('08-07-06') and ('08-08-06');

FIRSTOCCURRENCE has the Unix timestamps.

Could anyone help me out with this. Thank you for your help.

2

Since you are using to_char, FIRSTOCCURRENCE must of of datatype DATE.
So, UNIX has nothing to do with it.

Try

SELECT COUNT(*) 
FROM 
     REPORTER_STATUS
  WHERE  TRUNC(FIRSTOCCURRENCE) between to_date('08-07-2006','DD-MM-YYYY')
       and to_date('08-08-2006','DD-MM-YYYY');