Background

So, I happen to get one of these unusual timestamps that I've encountered during my job as a Database Adminstrator. I was like, "What do I do with this timestamp that is not human-readable?" Well, I searched the web (Google is my friend, mind you) to begin my investigation of how to solve this problem and there is limited information that I've gotten so far for this conversion. After taking a breather, this is my presentable solution.

Analysis

So, I get this UTC Epoch time (measured in seconds) in like this:

1601468183

SQL Server can't even interpret something like that unless there was a really good query that could help me out with this data. It would not even fit with a simple BIGINT (which limits to 2^63-1) nor it correctly parse that timestamp in that fashion with something like this:

SELECT DATEADD(s, '1601468183', '1970-01-01 00:00:00.000')

Which results to this:

Msg 8116, Level 16, State 1, Line 1

Argument data type varchar is invalid for argument 2 of dateadd function.

The column in question was in VARCHAR (string) type. It's not even pretty and readable for a report that I was composing.  While searching around the web for clues and solutions, I ended up using a query that I found via a 2011 SQL Twins post that helped me solved a part of my query problem.

First, I did the conversion with this SQL query:

SELECT DATEADD(ss, ('1601468183' % 86400), DATEADD(DAY, '1601468183' / 86400, '1970-01-01'))
-- Since this is a string column
and this works fine without a problem.

Outputs to:

2020-09-30 12:16:23.000

The results were a bit closer, but my other question was, "How in the heck do I convert this from UTC to local time?" Let's dig a bit deeper on this one. This was even simpler than the first one and thought of something like this from this Stack Overflow answer to see what it does next.

SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()),
'2020-09-30 12:16:23.000') 

Output:

2020-09-30 07:16:23.000

Validation

To validate this date/time stamp, I went to the SQL server in question and opened a Windows Command Prompt as an adminstrator and typed:

systeminfo

And scrolled down to where it said System Boot Time and it matched the system time. It should output like this:

System Boot Time: 9/30/2020 7:16:23 AM

Voila! That should do the trick once and for all. I then used to manipulate it in an automated query script for each and every row.

Conclusion

This was a really good challenge for me and as a first-time DBA in the real-world scenario, it tremendously helped my skillset that I needed. After doing the last query, it was ready for reporting. I had a lot of fun doing this and hopefully this helps for anyone who is tackling this problem as well.

Happy Querying!