Video Screencast Help
Protect Your POS Environment Against Retail Data Breaches. Learn More.

Time stamp format in database

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

Hi,

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

 

Thanks,

Comments 2 CommentsJump to latest comment

Mithun Sanghavi's picture

Hello,

Check this Thread:

https://www-secure.symantec.com/connect/forums/sql-querys-database

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
Senior Consultant
MIM | MCSA | MCTS | STS | SSE | SSE+ | ITIL v3

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

SOLUTION
.Brian'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.