How to convert EPOCH time in String to Timestamp in Ignite by SQL

user_6578305 0
Viewed 309

I have to convert a varchar column in Ignite table containing EPOCH time to Timestamp.

I tried but could not find any available date function.

Epoch time example in table : "20210428060207"

Is there any way to achive with SQL query in Ignite?.

Actual Problem: I have to extract hour, day and month from this epoch time.

ignite
1 Answers

You can try to leverage the DATEADD function to transform a Long-represented value into a TIMESTAMP along with the EXTRACT function to grab required parts from the produced TIMESTAMP.

SELECT 
   EXTRACT (HOUR FROM DATEADD('MILLISECOND', EPOCH_FIELD, DATE '1970-01-01')) AS HOUR,
   EXTRACT (DAY FROM DATEADD('MILLISECOND', EPOCH_FIELD, DATE '1970-01-01')) AS DAY,
   EXTRACT (MONTH FROM DATEADD('MILLISECOND', EPOCH_FIELD, DATE '1970-01-01')) AS MONTH
FROM "schema_name".TABLE_NAME;

Another option is to utilize Ignite custom SQL functions. It's possible to put conversion logic there. In that case SQL call would be simpler (FOO is a custom function here).

SELECT FOO(EPOCH_FIELD) FROM "schema_name".TABLE_NAME;
user_55581220
Related