06-03-2022 11:02 AM
Hi experts,
I got the value "1552895432011" of datetime field(createdAt) from SaaS, how to convert to date or timestamp using SQL ?
to_date(createdAt) , to_timstamp(createdAt) not worked.
Tks.
06-03-2022 4:29 PM
08-17-2022 10:06 PM
Hi,
You have epoch time as an input. Its the time in second since 1970. So the reverse formula is
SELECT ADD_SECONDS(to_timestamp('1970-01-01'), 1552895432011/1000 -- or "createdAt"
)
FROM DUMMY
Best regards,
Yann
PS : Please edit you title as "convert epoch to timestamp" 🙂
Edit: corrected after reviewed by Matija
08-18-2022 1:46 PM
Hi yannmiquel,
You detected correctly that it's Epoch, but the correct formula would be
SELECT ADD_SECONDS(to_timestamp('1970-01-01'), (1552895432011/1000)) -- or "createdAt"/1000
FROM DUMMY
This is because this Epoch time is calculated in miliseconds (13 digits), so you need to divide the input with 1000.
BR,
Matija
08-18-2022 3:41 PM