How to convert .net DateTime.Ticks to Hive DateTim

Question

I have log file with a column in DateTime.Ticks (635677577653488758) which i am trying to convert it to Date in Hadoop Hive.

First i tried the code block below on MySql and it worked. But the same code didn't work in Hive because date_add function works with INT.

SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL (MAX(f.date) - 631139040000000000)/10 MICROSECOND);

Then i will format it like this...

SELECT DATE_FORMAT(MyDateFromTicks, '%Y-%m-%dT%T.%fZ');

How can i achieve this?

Thank you.

Answer 1

write an UDF in java .. Use SimpleDateFormat class to convert the data into date

Answer 2

I've found the solution. I am writing it down here so if anyone comes this far can check the answer :)

My Ticks: 635683895215023579

EPOCH Ticks: 621355968000000000

SELECT FROM_UNIXTIME(CAST((635683895215023579 - 621355968000000000)/10000000 as BIGINT),'yyyy-MM-dd\'T\'HH:mm:ss.SSSZ');