PHP / MySQL - 存储用于跨时区的日期选择报告的日期

Question

currently debating the best way to proceed with regard to storing dates and would appreciate experienced comments - looking for the 'why' rather than the 'how'.

Scenario:

We are developing a system that allows clients (across multiple timezones) to create transactional data. At sign-up the client will have nominated their timezone. A large part of the system revolves around the client running date and time related reports on the transactional data.

We have read numerous questions and comments regarding the best way to store the transactional date field and most say store as UTC and then perform various date and time related functions to work out the time in the various timezones in order to run the selection reports.

Our question is - why go to all the trouble of storing in UTC - what is the advantage of storing the date in UTC. From our point of view we have the client's nominated timezone so if we store the date/time in their correct timezone then it’s a calculation that we run once per transaction - thereafter all their reporting will work without any date transforming calculations.

As most of the questions and comments we have read refer to the 'How' we think there must be something about the 'Why' that we have missed.

So, in summary, before we make our decision have we missed something - is there an absolute 'must store dates as UTC' reason?

Thanks for reading - any comments appreciated.

Answer 1

No, in my opinion there is no 'must' in this! this is totally a design issue, what you choose to use will determine the way the things will work, in either case, it's just a referencing issue, the people who are suggesting the use of UTC, is because it is the well known and verified reference, and no matter how big your program becomes or where your clients are, the reference is always there, but if you use, another time zone, things will work the same way but the time zone you choose will be the reference, which may cause misunderstanding in some situations, but still, in a controlled environment it's totally the same. I hope I answered your question.