Hello everyone, I am using Dremio to make a query between an elasticsearch cluster and an oracle database. I need to compare two dates to seconds in a query I am making.
In the elasticsearch cluster I have a timestamp column in the epcoh unix format, while in oracle I have a date type column.
So far I have reached the following expression to transform the epoch to a date type:
TO_DATE(TO_CHAR(TO_TIMESTAMP("timestamp"),'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'))
However, the result does not give me hours, minutes or seconds.
In Oracle one can do something like:
(DATE1 - DATE2) * 24 * 60 * 60
And deliver the difference in seconds, but I don’t know if that can be done here. Is there another way to address this situation in Dremio?
I appreciate any help, thanks in advance!