time-zone problem

[orengolan]

time-zone problem

Our company has a web application that is being used at some locations around world. the end-users are sales persons of small shops. each location might be in a different time-zone. our servers are located in San Diego, so each insert into the DB is inserted as San Diego time and not the location time, so when the user want to view the sale he made he will see our time and not his. the solution we implemented is to keep the data saved in San Diego time, but when the user want to search or view his sales we convert the time to it's local time, with the help of a look-up table. this table consist of 2 fields - location_id and location_gmt (Greenwich Mean Time). the problem we have now is the need to update the look-up table if any location's gmt is changed (and it changes at different times) the advantage of our solution is the ease of saving data. the disadvantage is the complexity when viewing or searching for saved data, and the need to know the GMT of a specific location in a specific time at the past. 1. do u think we should save the data in each location's time and not San Diego time? 2. do u know of any api (maybe a web service) to get GMT of specific location in a given time at the past? (we want to do it automatically, of course) thanks​

 

[b o n f i r e]

an idea

What I would suggest is as following: 1. The times at the DB are saved by GMT (or Pacific-time, doesn't matter). 2. The client software gets the time from the server, but right after that calculates according to the time-zone, which is can be retrieved from the local computer (I'm sure there's an API that does that). * In case there is not client software and the client side uses an Internet browser, you can use JavaScript.

 

[hg1979]

just to say

i think your approach of saving the local time is basicilly better, it always better to store raw data as much as you can. the classic example is storing persons's year of birth and not his age in the database (if we store the age we need to update it every year , if we store the year of birth or birthdate we dont need to do "yearly update" on our database) i think its best the user will select his time zone and the date and time will be adjusted according to his selection, also if the program has israeli users , the daylight saving here isnt issued on a fixed date , so its better to keep some fine tunes on the hours gap (or even control those exception on the server side) hope it helps