How to work with multiple time zones in applications that store dates and times?

Question

I understand that this is a bit subjective, but I hope that every brain will be here, how do they work with several time zones? There are many similar questions and an equally wide range of accepted answers.

How do you share this in the applications you created, and what problems did you have to overcome?

Answer 1

You always save the date / time in one time zone (9 out of 10 is the time in London) and convert it to your user's time zone display mode. This is strictly an application level issue, not db.

Answer 2

At work, we simultaneously manage several hours, not just time zones, but also a few more esoteric watches used to navigate a spaceship.

The only thing that really matters is that you agree with ONE AND ONLY ONE HOUR , whichever you choose, and that you have JOINT CONVERSIONS OF THE CLOCK when you need to SEE THE TIME IN THE CLOCK, VARIOUS OF YOUR AND ONLY ONE HOURS .

So:

One and only one time . Choose the simplest option that will help solve your problem, most likely it will be UTC (which some people might not correctly name Greenwich, but the point remains: the zero line).

Appropriate clock conversion . It depends on your application, but you need to ask and answer the following 2 questions: what permission do I need? Do I need to make sure I have a stopwatch? As soon as you answer, you can choose standard libraries or more estoric ones. Again, you should ask these questions.

View time : when someone selects a time (for example, Pacific time), just call the corresponding clock conversion on demand.

Indeed that is.

As for libraries, I use Python for scripting, but the NAIF Spice Library for developing missions and internal code for navigating a spaceship. The difference between them is simply resolution and reliability when taking into account all that you need to consider (Earth rotation, relativity, temporal expansion, seconds of jump, etc.). Of course, you will choose a library that suits your needs.

Good luck.

Edit:

I forgot to mention: do not try to implement your own time management library - use a cutout from a shelf. If you try, you can be successful, but your real project will die, and you will have only one medium library of time to display. Perhaps they exaggerate me, but creating a reliable time library with an end date is far from trivial, i.e. This is the project itself.

Answer 3

One of the projects I'm working on is using SQL Server 2005 and GETUTCDATE() to store the date in UTC.

Answer 4

I think the new best practice with SQL Server 2008 is to always use the datetimeoffset data type. normalizing dates to UTC is also good practice; but not always doable or desirable.

See this blog post to learn more: http://blogs.msdn.com/b/bartd/archive/2009/03/31/the-death-of-datetime.aspx

Answer 5

+1 for @ kubal5003.

The display of dates and times is always complicated by the culture and time zone, so for this it is always best to use the layer closest to the user (for example, a browser or local application). It also moves part of the download from the database to the user machine.

However, there is an exception for server reporting. So I store the name / timezone identifier (sometimes just an offset / offset) to find the beginning of the day. It can be system-wide or based on each customer / brand.

For web applications, I usually find the user’s time zone by geolocation (this is rarely wrong, since the geodata is pretty accurate right now).

Answer 6

You need to save information about the user's time zone and all information about the time zone. And always save time in UTC. And when you want to display this information to a specific user, simply enter the user’s time zone (which is stored for that user) and add or subtract this period of time from the time stored in UTC and display it. This will be in the time zone of the user who is viewing the information.