Getting year DATEPART from Unix date

Question

In my database, the dates are retained as days elapsed since/before 01/01/1970, which I understand to be a Unix date format, eg. today is 23rd May 2018 and the value is 17674.

I'm trying to run a select statement against a column in a date table to show only the year.

If I run this:

datepart(year, c.cko_date)

I get dates that are in the 1940s (they should be the last couple of years).

When I run

select datepart(year, getdate()) 

it correctly tells me the date is 2018.

Any suggestions about what I'm doing wrong?

Answer 1

Assuming the column is an int, and not a varchar column, you could do this:

datepart(year, c.cko_date + 25567)

25567 is the number of days between 1900-01-01 and 1970-01-01; essentially this adjusts for the discrepancy between unix and SQL Server datetime.

The SQL Server datetime is designed around 1900-01-01 being the "epoch". It may help to think of SQL Server dates as an decimal value representing the quanta of time until or since January 1st, 1900. Times are the decimal portion such that midnight is 0.0, noon is 0.5, 6pm is 0.75. The valid range of values that can be stored in a datetime column is 1753-01-01 00:00:00 to 9999-01-01 23:59:59.997. Numerically, this corresponds to the range starting at -53690.0000000 and ending at 2958463.99999998.

Unix uses 1970-01-01 00:00:00 as its epoch. By convention, the epoch starts in Universal Coordinated Time, or UTC. For North America, the Central Daylight Time timezone is 5:00 hours behind UTC. Therefore, to accurately convert a value from a Unix epoch time value into a datetime value in CDT requires adjusting for that 5-hour difference. Therefore, a slightly more accurate example of converting from a Unix-time expressed in a number of days would be:

--declare an integer representing the number of days since 1970-01-01
DECLARE @days_since_unix_epoch int = 17674; --2018-05-23
--show the datetime value in UTC
SELECT UTC = CONVERT(datetime, @days_since_unix_epoch + 25567)
--show the datetime value in North American CDT
   , CDT = CONVERT(datetime, @days_since_unix_epoch + 25567) - CONVERT(datetime, '05:00:00');

The results:

╔═════════════════════════╦═════════════════════════╗
║           UTC           ║           CDT           ║
╠═════════════════════════╬═════════════════════════╣
║ 2018-05-23 00:00:00.000 ║ 2018-05-22 19:00:00.000 ║
╚═════════════════════════╩═════════════════════════╝

While we're technical, typically the Unix epoch is stored as the number of milliseconds since 1970-01-01 00:00:00, not the number of days. The number of milliseconds can be quite difficult to deal with if extreme accuracy is a concern since there have been a number of adjustments to time since 1970 in order to deal with leap-seconds. According to the wikipedia article I just linked to, there have been 26 one-second additions to time since 1972; to accurately deal with dates between 1970 and today, you need to appropriately add between 0 and 26 seconds. I am uncertain if SQL Server's datetime structure accurately understands those adjustments.

Note also that SQL Server's datetime structure has a resolution of 3 milliseconds. Consider the following:

SELECT [1ms] = CONVERT(datetime, '2018-05-22 00:00:00.001')
     , [2ms] = CONVERT(datetime, '2018-05-22 00:00:00.002')
     , [3ms] = CONVERT(datetime, '2018-05-22 00:00:00.003')

The output:

╔═════════════════════════╦═════════════════════════╦═════════════════════════╗
║           1ms           ║           2ms           ║           3ms           ║
╠═════════════════════════╬═════════════════════════╬═════════════════════════╣
║ 2018-05-22 00:00:00.000 ║ 2018-05-22 00:00:00.003 ║ 2018-05-22 00:00:00.003 ║
╚═════════════════════════╩═════════════════════════╩═════════════════════════╝

If you need better resolution, you should consider using the datetime2 data type since it supports microsecond-precision, and a potentially much larger range of dates and times:

Date range: 0001-01-01 through 9999-12-31
Time range: 00:00:00 through 23:59:59.9999999