How can I compare a Unix epoch timestamp with a DATE in SQL?
For example I have a DATE data_type column with one entry 14-DEC-20 (i.g to_date('14-DEC-20','DD-MON-RR')) and an epoch date equivalent to this. I want to check if the the DATE is equivalent to the epoch, if not then set the equivalent epoch date in the date.
I have tried from_unixtime or with CAST but it didn't work.
You can convert epoch time to a date in Oracle using:
date '1970-01-01' + <your epoch value> * interval '1' second
You can then use this as a comparison:
where datecol = date '1970-01-01' + <your epoch value> * interval '1' second
Note: This does the comparison at the second level. If you want it at the day level, you need to adjust. And you can just use date for this:
where datecol = date '1970-01-01' + floor(<your epoch value> / (24 * 60 * 60)) * interval '1' day