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 mytableAs 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 mytableThis 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!