Convert from unix timestamp to datetime in sql ssms v15

Question

I want to convert unix timestamp from one of my table to normal date time format. Currently under Table, mainTable, i have dateTime column that contains this unix timestamp. Now i want to convert this mainTable.dateTime data from 636912333240000000 to something like yyyy/mm/dd format [e.g 2021/10/23]

I have tried using the following command: select columnA, DATEADD(S,[dateTime]/10000000,'1970/1/1') as FormatedDate from mainTable where ...

The result i am getting for the above is like 1990-03-08 16:05:33.000. The year is same for all data. It doesn't seem to change dynamically according to the year of the unix timestamp..

Can someone please advice on how i can get the exact dateTime by converting the unix timestamp to yyyy/mm/dd format??

Answer 1

Hi，@Edu_Mohd

Welcome to Microsoft T-SQL Q&A Forum!
The prerequisite for this is that the unix format must be correct.Try this:

SELECT columnA,Convert(varchar,DATEADD(MILLISECOND, CAST(RIGHT(dateTime, 3) AS INT)   
      - DATEDIFF(MILLISECOND,GETDATE(),GETUTCDATE()), DATEADD(SECOND, CAST(LEFT(dateTime, 10) AS INT), '1970-01-01')),111)as FormatedDate   
From mainTable where ...  

Test code：

create table #test01  
(  
 dateTime BIGINT  
)  
insert into #test01 values(1633418069),  
(1633504469),  
( 1633590869),  
(1665126869)  
  
SELECT Convert(varchar,DATEADD(MILLISECOND, CAST(RIGHT(dateTime, 3) AS INT)   
- DATEDIFF(MILLISECOND,GETDATE(),GETUTCDATE()), DATEADD(SECOND, CAST(LEFT(dateTime, 10) AS INT), '1970-01-01')),111)as FormatedDate from #test01  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If all your data within the range of int calculation (difference from two bigint numbers that falls in the range of int size), you can find a starting date and use the difference of this datetime and your data to calculate your target values.
Here is an unreliable solution but just an idea:

create table #temp (eventtimegmt bigint null )
 --CST -18000 seconds

 --

declare @startValue bigint =631173600000000 --get this manuallyfor --1990-01-01 
--https://www.epochconverter.com/
--This timestamp is in nanoseconds.covert it to second to remove 9 zeros

Insert into #temp values 
(636912338040000000),
(636912350040000000),
(636958101600000000),
(636912357240000000),
(636912359640000000 ) 

  Select eventtimegmt as  epochdtinNanoseconds,  
  DATEADD(second,(eventtimegmt-@startValue)/1000000000, CAST('1970-01-01 00:00:00' AS datetime)) as gmtdt 


  from #temp

 drop table #temp

Answer 3

Let's say for example this unix timestamp: 636332338040000000 is recorded on 2021/10/5,

No, as others have pointed out you don't have a Unix timestamp if you get that value on that date. The value you posted can only be a Unix timestamp if we assume that it is nanoseconds, and then we end up in 1990. Well, we can make guesses like it's something like 0.01µs, but when I divide by 100 million, and get an int overflow which tells me that we are beyond 2037 which is when the Unix timestamps no longer will be 32-bit.

So you need to investigate whatever it is you are using to get these time stamps. But they are not Unix timestamps.