April 25, 2003 at 6:04 am
Looking for ideas. We have tons of applications that for the first time are being used at a remote site that is in a different time zone. Some of the apps connect to our main site, some connect to a replicated version on the remote site.
Running into lots of minor issues. For example, when our users log into our timekeeping app we record the user and date/time. Later in the day a job runs that adds "tardy" entries to a table if the user logged in after 8:05. 8:05 eastern of course. Leaving out the idea that not everyone reports for work at 8am, everyone at the remote site ends up being marked as tardy because our system shows them logging in at 9am (because the proc uses getdate() on our box). This one not replicated.
On the ones that are replicated, as an example we log errors to a central table, then we push those back to the main server. We have a job here that runs every 15 mins to broadcast errors to the dev team, but it misses all the errors from the remote site because they are off by an hour. Error happenend at 8CST, gets logged back to the main as 8:00 - 9am our time, we pull from 8:45.
Update dates are equally crazy, we have cases where the record was changed before it was stamped as created.
Anyway. I don't see a magic solution, but if you have one, that would be great. I'm interested in a long term solution. Considering storing time zone associated with each date col. Need ideas!
Andy
April 25, 2003 at 6:22 am
Wouldn't using UTC time function sort this out i.e. using GETUTCDATE() rather than getdate()
Edited by - steven_white40 on 04/25/2003 06:24:54 AM
Steven
April 25, 2003 at 6:40 am
I too agree you should store as UTC, if you need to have the data show as Local Time I have SQL code that can do this. But you can find one of my basic pieces here related to the conversion to GMT from Eastern since this is what you are recording now but it cannot take care of that hour during spring where time repeats itself.
http://qa.sqlservercentral.com/scripts/contributions/158.asp
April 25, 2003 at 6:53 am
There is a basic table showing time zones/utc conversion at
http://setiathome.ssl.berkeley.edu/utc.html
Steven
April 25, 2003 at 8:56 am
I'll look at UTC - but if I go that route, won't I have to change every single date in every table to match?
Andy
April 25, 2003 at 9:03 am
Unfortunately, Yes. If you want the old data to match then all data will need to be UTC. But the code I posted will handle that conversion from Eastern to UTC except for those times in spring where they can overlap at 2AM.
April 25, 2003 at 9:06 am
You would need to run a one-off conversion to bring all the historic data in-line, and from that point onwards save the dates in UTC.
Steven
April 25, 2003 at 12:54 pm
I'll experiment with it in the next few days. Thanks for the comments.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply