Logging time in Db wrt to clients TimeZone

Question

I have an web application which logs all the activities perform by the client with the userid and time when the event was performed. As of now the application logs the time dat is of the DB server. my application supports different time zone so i want to capture the time of the client machine which invokes an event and store that time in db rather than the Db server timezone.

Structure of table Columns: UserID, EventId, Time (this value needs to be the client machine time rather than the DB server value)

My question is how do i get to record the client machine time zone and not the dbserver time zone.

I hope i was clear enough. Thanks in advance

This SO questions suggests some ways: http://stackoverflow.com/questions/630321/how-do-you-retrieve-the-client-users-time-zone-for-a-web-application — Frank Bollack, Dec 19 '10 at 20:46

score 2 · Answer 1 · answered Dec 19 '10 at 22:08

On the database end, you'd want to use a TIMESTAMP WITH TIMEZONE datatype.

Obviously this makes some queries more complex. For example, if you want to pull out Saturday's records, do you mean Saturday as seen by the user who initiated the insert, or Saturday as seen by the person doing the query or Saturday as seen from the point of view of the database (or other 'impartial' viewpoint).

score 1 · Accepted Answer · edited May 23 '17 at 11:47

Your issue here is getting the time zone of the client browser, but unfortunately there isn't a foolproof way of doing this. There are a number of options though:

I. Ask the user

As part of your "User Preferences" section, you give the user an option to set their preferred time zone.

II. Take a guess

Using the HTTP_ACCEPT_LANGUAGE header, you can infer a country. Obviously if the setting is just "en" or "fr" or "es" then your guess will be just that, if there's a country preference as well, e.g. "en-US" or "en-GB" or "fr-FR" then your guess will be closer. You'll still need a "User Preferences" box to handle the edge cases.

III. Use Javascript

Javascript has a getTimezoneOffset() function that will give you the offset from GMT for the client browser. The following shows an example of using this function:

How can I obtain the local time in jQuery?

Hope this helps.

Logging time in Db wrt to clients TimeZone

2 Answers2