2

I am a new to storing dates based on time zones.

Need to know the standard way to store the date in the datastore.

My requirements are

  1. Easy to query the date based on the date range.
  2. show the date with the client appropriate time zone selected by him(I am having a table maintained for the timezone separately)
  3. Able to query using the datastore Admin console also.

Any suggestions/ideas regarding this will be a great help in proceeding further.

Improve this question
3
  • When you say “date” what do you actually mean? Timestamps? Abstract days? With the former, you care about the time of the day (and the timezone) and with the latter, you don't. Different applications need different things…
    – Donal Fellows
    Jun 18, 2013 at 16:22
  • 1
    If each user has a timezone defined then go with GMT.
    – dbasnett
    Jun 18, 2013 at 16:30
  • 3
    @dbasnett: This answer may shed some light on why forcing everything into GMT isn't always a good idea.
    – Blrfl
    Jun 18, 2013 at 19:30

2 Answers 2

Reset to default
4

You might consider always storing the times in UTC. Then have your code logic handle conversions per timezone appropriately. IMO this is easier than trying to convert to other arbitrary timezones, and many code libraries have built-in options for UTC. This also has the benefit of still working if your data storage (database?) server is ever moved to a new location -- or even the cloud.

Improve this answer
2
  • 1
    And it also make life easier when dealing with day light saving.
    – the_lotus
    Jul 19, 2013 at 19:32
  • Ah yes, the dreaded daylight savings! Great point! :)
    – Allan
    Jul 22, 2013 at 14:48
-1

Use the Java standard timezones for all settings.

I would recommend storing the dates and times in the database in the time zone that the server is configured in. i.e. if your server is in London, use GMT to store all dates and times

Each user could potentially have their own timezone. i.e. users in Eastern US, users in Auckland.

You can then convert from any user time zone to the known server time zone to the user time zone.

You can store the server time zone in some sort of application configuration file for ease of reference.

  • Easy to query the date based on a date range:

If the users timezone is know, you can just do a conversion to the server timezone in your application when the query is being completed. Pass through the converted date and time to your query.

  • Show the date with the client appropriate time zone:

Once the queries are returned, you can convert them to the user time zone and display in their own time.

The java docs have sample code on how to do the conversion between time zones.

Improve this answer
3
  • 3
    Could you give your rationale for storing date and time in a timezone dependent way? That implies that you have to know the timezone where the data has been created to process it meaningfully, which seems a burden -- especially for time with the day light saving times changes. IMHO, time zone awareness should be restricted to the human interface and ignored after.
    – AProgrammer
    Jun 18, 2013 at 16:39
  • Good question - Say for auditing reasons. You have two users, A user and manager. If these two users are in separate timezones and the manager wants to see when this holiday was requested (i.e. an InsertDateTime column or simliar) How do you display that result back to the manager in their timezone? The input data must have meaning. You have to be able to convert from a timezone to a timezone. Converting the dates/times to a common timezone(the server) gives you this flexibilty.
    – Gibson
    Jun 18, 2013 at 17:06
  • Also, if the manager is searching for this input and they enter "All holiday request from Monday to Friday" if he resides in the UK and manages 20 people in multiple different timezones, their "Monday to Friday" different to the managers. Whos "Monday to Friday" is correct? The input criteria by the manager must be converted to the common timezone, the search conducted on the server with the adjusted inputs, results returned to the business layer and converted then to the managers "Monday to Friday"
    – Gibson
    Jun 18, 2013 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.