Storing TimeZone information in SQLServer !!! URGENT PLEASE !!!
-
Hi Folks,
I have a requirement to store/retrive the date, time and timezone
information of a realtime captured data that has an associated
timezone into the sqlserver database.
Another catch is, i want to achieve this using JDBC. So here are my
questions :
1. What datatypes in sqlserver database table, can store the
specified timezone information as well.
2. What JDBC calls i need to make to store/retrieve the date tiwjt
the timezone data as well
I am sure some one came across this problem and there must be a
solution
All the responses are greatly appreciated.
Thanks
Venu
-
What format is the timezone in? Is it simply an offset from UTC?
"tiwjt" Is this a typo or perhaps is a formt I'm unfamiliar with.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
http://www.devx.com/tips/Tip/17668
My Blog:
http://dineshasanka.spaces.live.com/ -
store it as a separate smallint, just indicating timedifference to GMT. If doing so, it might be interesting to include a gmt-datetime column because otherwise you'll have to include "daylightsavings" stuff etc.
Remember this is a piece of data you have to run client-side or you'll have your server's timesettings
DATEDIFF ( hh , GetUTCDate() , GetDate() )
Decide what you want to store :
"GMT to local time" or "local time to GMT"
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Or just use GetDate to get the server datetime and adjust other dates in the client app by calculating the offset from the client.
-
Are the data collection points all in different time zones? and being sent to a seperate sql location in yet another time zone?
If so then you'll need some identifer from the collection point to identify which offset its from.
If there is no way to read the actual offset from the collection point (servers regional settings, datalogger um info thingy
Then you'll probable need to maintain a table of collection points and what offset they are in.e.g
tblCollectionPoints
point1, -1
point2, +7
whatever you call the points that information has to come with the data you send from that point.
e.g.
insert into dataTable 'point1',DateTimeAtPoint,values...
Then when your reporting, you can shift them all to UTC based on the offset recorded in their related table.
If you can customize the code at each collection point, you could:
myAppObject.offset=-1
myAppObject.sendData
Would be nicer to be able to pull the offset from the collection point itself.
e.g.
myAppObject.getOffsetFromRegionalSettings()
myAppObject.sendData()
I would prefer this because then your sql data is not dependent on the validity of the related table since it would all be in UTC,and reading the offset from the source means only one version of the app. (Unfortunetly I tried reading the regional settings on win2003 from windows script host and it was a nightmare of WMI code.)
I recently had to contend with the same thing (sort of) I was sumarizing log data from windows media servers from different timezones to a central sql. Of course all the log data was in UTC so I din't have your issue, but I needed to offer the report clients a way of viewing the data from their regional time zone perspective.
timezones can give you a headache
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
For the sake of completeness and if you are not afraid of using some undocumented extended procedures what about this one?
DECLARE @delta INT
EXEC master.dbo.xp_regread
'HKEY_LOCAL_MACHINE'
, 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
, 'ActiveTimeBias'
, @delta OUT
SELECT
GETDATE() AS Aktuelle_Zeit
, DATEADD( Minute, @delta,GETDATE()) AS Greenwich_Mean_Time
, @delta AS Delta
Aktuelle_Zeit Greenwich_Mean_Time Delta
------------------------------------------------------ ------------------------------------------------------ -----------
2004-03-09 09:49:04.640 2004-03-09 08:49:04.640 -60
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
If we move to GMT, is there an issue with Daylight Savings Time or other similar time changes? please let me know.
Thank you for your help.
-
Yes, there can be issues. I am on the US east coast (Eastern Standard Time). The we are GMT-4 during DST and GMT-5 the rest of the year.
My database is solely GMT, we don't care about local time.
-SQLBill
-
Forgot to add, one issue with GMT/Local times is during the change from DST to 'normal time' and back again.
Let's say the time changes at 2 am local (as it does in the US). That means that in the spring you turn your clock ahead one hour (2 am becomes 3 am). No big problem, there just won't be any data between 2 and 3 am. However, when the clock goes back an hour, 2 am becomes 1 am again. Now you have two sets of data for the time between 1 am and 2 am. You need to decide if you have to differentiate between the first 1 to 2 am time and the second 1 to 2 am time.
-SQLBill
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply