Difference to seconds between dates

1

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!

2

select timestampdiff(SECOND, to_date('20180909', 'YYYYMMDD'), to_date('20180910', 'YYYYMMDD'))

3

Thanks for the help!!! but it doesn’t work for me since one of the columns is in epoch and the other comes from the oracle date type, and the to_date conversions in your example come with static dates.

I think the problem is with the conversion of column types!

4

I fixed it this way

timestampdiff(SECOND, "DATE_ORACLE", TO_TIMESTAMP("DATE_EPOCH"))

Thanks a lot for your help comphead