Formula to convert Long datatype field to DateTime format without materialized views

[Ruchita]

Hello !

I have a few database tables that store a datetime value as a Long/Big Int Value. I was windering if there is a formula in incorta that i can use to retreive these values in the original datetime format.

Kindly find an example below:

Stored Value	Required Value
1607948169324	2020-12-14 12:16:09.323

I have quite a few columns like these, so it would be much appreciated if there is a faster way to achieve this without materialized views.

Thanks in advance !

[anurag · Accepted solution]

Hi @Ruchita 

Looks like this (1607948169324) is epoch time.

The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT)

I tried this formula:

addMilliseconds(

timestamp("1970-1-1 00:00:00"), 1607948169324

)

 and got following result:

12/14/20 12:16:09 PM

So you should be able to use this formula:

addMilliseconds(

timestamp("1970-1-1 00:00:00"), <schema.table.timestamp_column>

)

and to convert timestamp to epoch time you can use this formula:

https://docs.incorta.com/cloud/references-built-in-functions-conversion-epoch