1

How can I convert SYSDATE to Unix timestamp?

I want to compare (date + time) stored in unix timestamp format to (sysdate minus 1 day) in the WHERE cluase.

I have tried this but got empty rows.

SELECT DAT_CLOSEDATE -- STORED AS FLOAT DATATYPE IN UNIX TIMESTAMP FORMAT  
FROM TROUBLETICKET 
WHERE DAT_CLOSEDATE = to_number(to_char(to_date(sysdate,'dd-mm-yyyy')-1,'yyyymmdd')) -- CONVERTING (SYSDATE - 1 DAY) TO UNIX TIMESTAMP
;

Here is a sample for DAT_CLOSEDATE stored values:

DAT_CLOSEDATE
-------------
1531499762
1531499774
1531499794
1531499808
1531499822
1531499866

I expect to get the yesterday date

APC
  • 144,005
  • 19
  • 170
  • 281
Gambos
  • 21
  • 1
  • 4
  • Sysdate is already a date. You don't need to apply `to_date()` to it. Use `trunc()` to remove the time element. – APC Jan 03 '19 at 09:27

0 Answers0