Timestamp Data convert

Manikandan R

Hi,

I need to convert Timestamp Data of (1679722784640) this to datetime format in sql server, i successfully converted the data referring this suggestion stackoverflow.com/a/3650475/14329025 but i need a clarification of what is the need of inputing ('1-1-1970 05:30:00') this date particularly, in case change the date or month or year ouput data show wrongly

--Unix timestamps input (1679722784640) to get Date and Time 2023-03-25 11:09:44

select dateadd(s, convert(bigint, 1679722784640) / 1000, convert(datetime, '1-1-1970 05:30:00'))

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Naimish Makwana3y ago
Accepted answer
The reason '1-1-1970 05:30:00' is used as the starting point is because it represents the Unix epoch time, which is the standardized reference time used in Unix-based operating systems.

Unix time is the number of seconds that have elapsed since January 1, 1970, at 00:00:00 UTC. Therefore, by adding the number of seconds represented by the input timestamp to the Unix epoch time, we can get the exact date and time for that particular timestamp.

The date and time portion of the Unix epoch time '1-1-1970 05:30:00' may vary depending on the timezone and offset settings of the server where the conversion is performed. However, as long as the Unix epoch time is correctly set, the conversion should yield the correct result regardless of the date and time portion used.

Thanks
Sachin Singh3y ago
You need to understand the Unix epoch

for reference follow this

https://www.sqlservercentral.com/articles/convert-unix-timestamps-to-date-times-in-sql-server-part1