Let me clarify with an example.
A user from Florida visits my web service and sets her time zone to be "-05:00". The system works fine until it reaches the date of Daylight Saving Times and suddenly the system serves the time one hour behind.
Unfortunately it isn't as easy as letting a proc substract one hour during Daylight Saving Times since certain places (for example one U.S. state, don't remember which one) don't make use of DST.
Perhaps I should store two times and add an extra column to my data model:
create table timezones (
timezone_id
timezone -- i.e. "-05:00"
timezone_dst -- "-04:00"
description "EST"
);
And then let the script do the SQL query using either
timezone or
timezone_dst according to the date. For places with no DST the 2 times would just be the same.
I guess what I'm really asking is, "Is there way to get around this using an SQL query, one that will return the time correctly even during DST?"
