Convert SQL datetime to date or timestamp

[former_member805562]

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.

[sap_user1234]

Can you please elaborate what is the actual timestamp for '1552895432011' ?

[yannmiquel]

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

[mgregur]

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

[yannmiquel]

Kudos 🙂