topic Re: Convert SQL datetime to date or timestamp in Application Development and Automation Discussions

Convert SQL datetime to date or timestamp

former_member805562 — Fri, 03 Jun 2022 10:02:18 GMT

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 — Fri, 03 Jun 2022 15:29:30 GMT

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

yannmiquel — Wed, 17 Aug 2022 21:06:55 GMT

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 — Thu, 18 Aug 2022 12:46:43 GMT

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 — Thu, 18 Aug 2022 14:41:19 GMT

Kudos 🙂