Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Nebraska SQL from @DBA_ANDY

I'm a forty-something Microsoft SQL Server DBA of 12+ years, a devoted husband, and a father of two young boys (with another coming soon!). I have been a DBA at a university, at a major bank, at a healthcare system, and I now work as a consultant with customers across the United States.

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!

Comments

Leave a comment on the original post [nebraskasql.blogspot.com, opens in a new window]

Loading comments...