Video Screencast Help

Time stamp format in database

Created: 07 Nov 2012 • Updated: 28 Nov 2012 | 2 comments
This issue has been solved. See solution.


  Can anybody tell me how to convert TIME_STAMP field in SEPM tables to an understandable format?


Comments 2 CommentsJump to latest comment

Mithun Sanghavi's picture


Check this Thread:

Ian_C states provides the Answer:

A lot of queries with time stamps have this format

dateadd(s,convert(bigint,[TIME_STAMP])/1000,' 01-01-1970 00:00:00') as [Calculated Time Stamp]

To display the time field in your local time zone, change the '00:00:00' field after 1970 to your timezone offset from UTC.

PS I guess, but am not sure, that for people west of the Greenwich meridian, that time would be in 1969?

PPS Don't forget the DST offset too.


As a header to your query, place the following two lines before your select statement

DECLARE @TimeZoneDiff int    
SELECT @TimeZoneDiff = datediff(minute, getutcdate(), getdate())

The first two lines declare the variable called @TimeZoneDiff and give it a value in minutes the offset from UTC. I don't know if this is based on the TZ of your work station or the TZ of the server where the DB is hosted.

Then, where ever you select a time field from the database, replace [TIME_STAMP] with the name of the field in this line:

dateadd(minute, @TimeZoneDiff, dateadd(second, [TIME_STAMP]/1000, '01/01/1970')) AS [Time Stamp]

This last line adds the TZ offset in minutes to the [TIME_STAMP] field to show the value in your local time.

Yip, this means several queries will have to be updated.

Hope that helps!!

Mithun Sanghavi
Associate Security Architect


Don't forget to mark your thread as 'SOLVED' with the answer that best helped you.

ᗺrian's picture

select dateadd(s, time_stamp/1000, '1/1/1970 00:00') from sem_client

Please click the "Mark as solution" link at bottom left on the post that best answers your question. This will benefit admins looking for a solution to the same problem.