0

I am looking for a method how to effectively request data from sql server assuming that request goes from different timezones.

for example request from Australia (UTC/GMT +11 hours) will ask for records older than 2005-11-19 18:00:00

but server in Canada (UTC/GMT -5 hours) and all records saved as a Canadian.

canadian user through UI will pick '2005-11-19 18:00:00' date and we will do this seelct to sql server

select * from table1 where added < '2005-11-19 18:00:00'

same select from Australian user assuming that user picked same date through UI has to be

select * from table1 where added < '2005-11-19 02:00:00'

my question is what is the right way to query sql server from different timezones and get results assuming time zones.

answers like Effectively Converting dates between UTC and Local (ie. PST) time in SQL 2005 do not work because I would like to do it declaratively through the connection and do not want to put any logic in sql.

Community
  • 1
  • 1
Cherven
  • 1,101
  • 3
  • 17
  • 26

2 Answers2

2

If you want to store a moment in time, you should use UTC on the database (this is the most common case, such as for logging etc.).

If you want to store a location-specific date, use a datetime including its timezone information. This is needed for instance for proper storage of a birthdate including time (converting to UTC would possibly shift the date).

The same goes for queries. Typically the users enter local time in a query and it needs to be converted to UTC for querying. However, if you query something like birthdates, you don't want to do this conversion because users would not get the expected results.

So the answer really is "it depends". The worst is having local times without time zone information, because this usually cannot be reverted to the correct date. Note that daylight-saving times affect the time zone as well, and since the rules change over time you may not be able to correctly reconstruct the local date even if you know what the actual date and server time zone (not including DST) was.

So if you don't want logic in SQL, use UTC on the DB, and convert the dates to UTC for insertion/update/querying.

Lucero
  • 59,176
  • 9
  • 122
  • 152
-1

my question is what is the right way to query sql server from different timezones and get results assuming time zones.

Hiring a programmer that knows about the issue and handling it in the program code.

I would like to do it declaratively through the connection and do not want to put any logic in sql.

Bad solution. It is a code problem.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • Do you think that doing all conversions in the code is exactly the right way? I found this way a very manual and thought may be there is more intelligent/automated way. – Cherven Dec 21 '11 at 15:40
  • Well, manual is relative. I found it to be most flexible. I store all timestamps in UTC on teh server and deal with the localization - which may change .- on an as needed basis. – TomTom Dec 21 '11 at 15:57