How to Calculate the Number of Seconds Since Last Epoch (1/1/1970) in T-SQL

In certain situations, you may need to calculate the number of seconds that have passed since the start of the epoch. In computer world, the start of the epoch most often refers to the Unix epoch, also known as Unix time, POSIX time and Unix timestamp and which is January 1, 1970. If you would like to know a bit more details, here is a good post on the history of computer epoch date.

Calculating that number is rather very simple on MS SQL Server because you have a built-in DATEDIFF function. To calculate the number of seconds for the “right now” time, you can use the following statement:

SELECT DATEDIFF(SS, '1/1/1970', GETDATE())

Since we are specifying the date only and not time, the time will automatically be assumed to be 12am. The GETDATE() function, however, will return both date and time. And, of course, the SS parameter means we want to return the number of seconds when calculating the difference between the two dates we are passing in.

This entry was posted in Quick Tips and tagged , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *