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

Ruchita — Tue, 01 Aug 2023 11:56:27 GMT

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 !

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

anurag — Tue, 01 Aug 2023 13:34:22 GMT

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

topic Re: Formula to convert Long datatype field to DateTime format without materialized views in Data & Schema Discussions

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

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