Why Is your datetime saved as a bigint anyway?

Yet another issue I had never seen before - thankfully this time I was able to find a relatively quick answer.

On a client system their primary database had grown to throw disk alarms on the DATA drive, so I was alerted.  I signed onto the system and found that the largest table in the 43GB database was itself 20GB.  I looked at the contents of the table and found several fields labelled sent_time and audit_time...only to find that they were bigints rather than datetime - yuck!

The best description of the situation I found was a post from Ben Northway (Blog/@northben) titled "SQL Server date time conversions to/from Unix bigint format UPDATED".  Ben describes how the bigint datetime I found is actually a UNIX-style timestamp, counting the milliseconds since January 1, 1970. (makes sense, doesn't it?)  It even has a catchy name - Epoch time.

Why January 1, 1970?  Here's one response I found on a StackOverflow post asking this same question:

The universe was created on Jan 1, 1970. Anyone who tells you otherwise is clearly lying. –  ijw Jul 7 '09 at 23:52

...and now you know.

Ben's formula worked perfectly for me:

SELECT DATEADD(s,mycolumn/1000,'19700101') from mytable

As you can see, rather than performing match in milliseconds, the formula divides by 1000 in order to do math in seconds - as Ben notes this prevents an arithmetic overflow error.

A couple of limitations of this approach:

• Epoch Time ignores leap seconds, so if you need that level of precision you will need a much much more complicated formula than this.
• This formula is based on UTC/GMT, so if you need to convert to a specific time zone you need to add in a correcting factor like this:

SELECT DATEADD(s,mycolumn/1000+8*60*60,'19700101') from mytable

This corrects by 8 hours (8 hours * 60 mins/hour * 60 secs/min) to UTC/GMT+8.

Thanks Ben - hope this helps - I know it helped me!