Hi all, this is a philosophical question.
I have been reading some articles about how to handle date/times. I am saving date/times in UTC time zone, and then I handle the conversion to the user’s location. That was not hard, I adapted this article to my needs.
The problem is that in some cases I need dates (for example birthday), and if create a date filed on MySql (or any other db), I cannot, save it in UTC, for someone born on 2000-01-01 in NYC, what is that date in UTC? (birthday 01/01/2000 means between 2000-01-01 00:00:00 EST to 2000-01-01 23:59:59 EST, which is about, 2000-01-01 05:00:00 UTC to 2000-01-02 04:59:59 UTC -depending on DTS too). If I save in a timestamp or daytime as 2000-01-01 05:00:00 UTC (2000-01-01 00:00:00 EST), maybe when I read that from Hawaii, ti will come as 1999-12-31 23:00:00 HAST (so the birthday will be on 1999 and not in 2000, which in some cases may be important). Of course I may know for sure that the person was born on 2000-01-01 09:23:14 UTC and calculate the exact age, but that is not normally the case.
DateTimes are easy since -for practical purposes- they are exact points in time, but dates are normally intervals that we may use as points in time.
In general this is not very important, but anyone has some guidelines or examples of how to deal with this in the cases when it is important. I am not expecting a global one-size-fits-all solution, but some ideas may help me in a project I am starting (and hopefully others).
Thanks all.
