Is there a way to convert epoch timestamp to a human readable datetime?

Matillion ETL
1

My file format has a column that is in epoch format. ex - {"time_epoch_sample": 1648612800000}.I'd like to load into Snowflake in a human readable DATETIME format. Any help please?

2

Hey @VVeda​,

I think your simplest option here would be to use the TO_TIMESTAMP function in Snowflake. For example:

TO_TIMESTAMP(1648612800000, 3)

which produces the result:

2022-03-30 04:00:00.000

I hope this helps!

3

Thanks Chris. That does help. But the bigger issue I have is my file is in .ndjson format with one of the fields in the above mentioned epoch time, which needs to be converted/ or add another column with regular datetime field into the target table.

I was able to use python and convert the file from .ndjson to .csv prior to loading via S3. However, I'm looking if there is a way to just be able to use Matillion and automate this process. Support indicated that I should be able to use a python script component, but I was unsure how to proceed. In case anyone is able to provide solution, I'm attaching a sample file here.

4

@vveda - what i might do (similar to chris' answer without converting json to csv) is land the original data (json) from the file into a temporary table with 1 column of variant datatype, then process the tmp table into the target table converting with snowflake sql.

simulate file loaded into tmp table:

create table x.y.TEST_VARIANT (TEST_VARIANT variant)

insert into x.y.TEST_VARIANT (TEST_VARIANT)

select parse_json('{"time_epoch_test": 1648612920000, "device": "CHEC.SUB4.RTU4", "substation": "CHEC.SUB4", "institution": "CHEC", "point": "1", "name": "1", "value": 1240.}')

convert timestamp:

select TEST_VARIANT, TEST_VARIANT:time_epoch_test as time_epoch_test, to_timestamp_ltz((TEST_VARIANT:time_epoch_test::integer),3) as time_epoch_test_as_timestamp

from x.y.TEST_VARIANT

I would use as little python as necessary, in this case none!

5

@VVeda​ here's a quick demo in Snowflake showing how this could work, starting from the raw string in a single field and ending with the timestamp. I have also attached the SQL I've used, which is similar to @PCorb​'s

0694G00000KCKsOQAX_0D74G000006c82OSAQ

6

Thanks a lot for @PCorb​. Appreciate your response. I'll check it out today.

7

Thanks a lot for the detailed demo Chris. Really appreciate your response. I'm guessing I can use this sql in the sql component in Matillion and load the target table. Will check it out.

 

thanks

Vani