May 31, 2006 at 4:52 pm
Hello all,
What I would like to know is how to extract/obtain the EPOCH timestamp in milliseconds from SQL Server. I've seen a lot of web-postings were people are taking the EPOCH time and then converting it into GMT (for example); but I haven't been able to figure out how they are getting the EPOCH time in the first place.
Thanks in advance,
Jose Garcia
June 1, 2006 at 6:23 am
Are you wanting to convert to or from EPOCH time? Other than leap second handling (which would probably require a small lookup table), it should be pretty easy if you elaborate on what your requirements are.
Keep in mind that EPOCH is basically just the number of seconds since midnight on January 1st, 1970.
To get the date from an EPOCH value, the following should work:
Dateadd(dd, BigOlEpochNumber / 86400, '01/01/1970')
Note that 86400 is the number of seconds in a day. The formula: BigOlEpochNumber % 86400 should give you the seconds since midnight, so you can get the time from that. Remember that modulo doesn't like fractional numbers, so if the EPOCH has one, drop it. You can save this dropped fractional part and add it back on at the end if you need accuracy within fractions of a second, but if you need that accuracy, you'll also want that table of leap days seconds.
June 1, 2006 at 8:43 am
Thanks for the reply David.
I think this will work. I'm running a Kronos application on my SQL Server; the application extracts data from the database for an external system. One of the requirements is that the EPOCH time be added to one of the fields.
Jose
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply