Extract Unix timestamp from json and converting to human readable datetime

Question

Hi
I have a json file in datalake. It has createtime and updatetime fields in Unix timeformat. I am unable to transform using Derived column. I am getting null in the result.

 "createTime": 1716327033431, "updateTime": 1716327033431,

I tried various syntax
toTimestamp('createtime')

toDate('createtime')

toTimestamp(toLong(toString('createtime')),'yyyy-MM-dd HH:mm:ss')

Answer 1

Hi @Ali, Ayesha

Thank you for posting your query!

I inferred that the createTime and updateTime values are in milliseconds based on their format in the provided JSON example
Adjust your expression to convert the Unix timestamp from milliseconds to seconds before applying the toTimestamp() function.

Correct Format:

toTimestamp(toLong(createTime) / 1000, 'yyyy-MM-dd HH:mm:ss')

Below Similar thread will helpful
https://stackoverflow.com/questions/59118389/azure-data-factory-mapping-data-flow-epoch-timestamp-to-datetime

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.