SQLTeam.com | Weblogs | Forums

How to store Global Time

sql2008r2

#1

Hi All,

I am a beginner. In my customer place they have a third party application. It stores user's local time into the database. That's why when I query the database from the database level, I get user's local time rather than global or centralized time.

Is there any solution available in SQL server level, which can handel this issue and store global/centralized time in the backend database?


#2

If you had the option of changing the query that writes the time, you could use GETUTCDATE() function (instead of the likely GETDATE() that they are using).

If you are not able to do that, then, you can calculate the UTC time using the following:

SELECT DATEADD(mi, DATEDIFF(mi,GETDATE(),GETUTCDATE()), YourDateColumn))
FROM YourTable

But, if you are in a place that uses daylight savings time, this will be incorrect when you run the query to convert a value if the daylight savings time was in effect when the date was stored and currently you are in non-daylight savings time (and vice versa).

You can correct for that as well, but for that you will need a table that indicates the daylight savings time switch over dates.


#3

Unless you know what the time zone of each user was, at the time the entry was made, I'm not sure you have much hope.

Even then small time differences between user's local time [clocks in their PCs] will be an issue - quite possibly a major issue :frowning: We always use the time of the server in all database records (our clients have no multi-site servers), rather than any other form of Local / Client PC time, and we work hard to keep all our clients' server clocks accurate - but even so we find that servers that the time on servers we are not responsible for drift off by scary amounts over the course of a year (yeah, SYNC with Time Server is present but somehow they get into a "Too far adrift to auto-correct state", and then drift even more wildly, or have adjustments that we see in the Event Log which we find scary (reports that sort in chronological order likely to be impacted by the time correction as, surely??, some records will have been saved with the now-wrong time)


#4

Hi James,

The issue is I cant change the query or application code as it is a third party product. What I was thinking, if I can do something at DB level configurations or scripts, which will convert the local time of user to the DB server time and store that. Is there anything possible around that?


#5

Hi Kristen,

The issue is I cant change the query or application code as it is a third party product.

Suppose the server is at singapore and user accessing from china through his browser. In stead of storing server time in the date field it is storing the users local time in the backend.

What I was thinking, if I can do something at DB level configurations or scripts, which will convert the local time of user to the DB server time and store that. Is there anything possible around that?


#6

yeah, but you haven't defined what "local time" is.

  1. If you know that it is the Server Time, in Singapore, then you can just add an offset to convert it to Global Time [the vagaries of daylight-saving-time not withstanding]. You could create a VIEW that has all the original table columns, but with all dates "converted" [by adding the appropriate offset] to global time. Then use that VIEW instead of the original table. Unless you index the calculated date columns in the view using the VIEW in date-based queries will be much slower than the original table/column.

  2. if it is actually the "local time on the PC that connects to a Server in Singapore" then I suspect that all-bets-are-off.


#7

Can you explain why you need or want a global/centralized time? The dates and times entered into the system are appropriate for when the action took place in the location where that action took place. The sale happened at 10:00am local time - reporting that the sale occurred at some other time doesn't make sense to me.