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.
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.
In my opinion it would be best to store the dates/times as a unix value in the database. You then have an exact number that can easily be manipulated to be displayed however you like, using the stored timezone or otherwise, and crucially if you decided to change how its presented it would be quick to alter.
Why go to all the trouble of storing in UTC?
There is no trouble of storing time in UTC. IMO it is easier to maintain datetime fields if they are all in same timezone, rather that every record of table being in different timezone and relation with user table tells you in what timezone that field is (or even absurd approach would be to store offset like +02:00 in extra field).
At sign-up the client will have nominated their timezone.
Think of what could happen when you will add functionalities:
Is there an absolute 'must store dates as UTC' reason?
Ofc not; it is all about your design. What you choose is what you work with; just think what would be easier for you, for future upgrades, etc.