1

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.

Sir. Hedgehog
  • 1,260
  • 3
  • 17
  • 40

1 Answers1

1

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This doesnt seem to work correctly and I cannot understand why. My epoch time is 1626167479458, which actually is: GMT: Tuesday, 13 July 2021 . But after I convert it I get 13-MAR-65. ```select (date '1970-01-01' + floor( 1626167479458 / (24 * 60 * 60)) * interval '1' day) FROM DUAL; ``` – Sir. Hedgehog Jul 13 '21 at 12:52
  • Changing the calculation actually fixed my issue, thank you ```select (date '1970-01-01' + floor( 1626167479458 / ( 24 * 60 * 60 * 1000)) * interval '1' day) FROM DUAL;``` – Sir. Hedgehog Jul 13 '21 at 13:12
  • 1
    @Sir.Hedgehog . . . Formally (eg. https://en.wikipedia.org/wiki/Unix_time) Unix time is the number of seconds since 1970-01-01. However, as you have now encountered, sometimes it refers to the number of milliseconds instead. The terminology is ambiguous. – Gordon Linoff Jul 13 '21 at 21:08