(09-12-2020, 10:39 AM)tgix Wrote: As pointed out - the database may also be in another timezone. For MySQL I always do a "SET time_zone='+00:00'" after connecting to the database and then make sure all times are handled in UTC and only changed when presented to the user (based on the user's timezone settings).
Yes, this is certainly the best way to keep it from becoming a kidney stone to keep a handle on... (and hence, from what I can tell, generally believed to be best practice).
My thinking has always been to remove the 'database' from the equation by never permitting the 'database' to write time-based values (for example by doing things like having current_time() as a default for a field)... and if there is time-based functionality/jobs to be run on the database, then these are effectively done independently of the data (time values in the data), and based on the time of the 'database' (which, yes, for me is typically also set to UTC anyway). This way, the 'server' is the only place I've needed to watch for it always referencing UTC when it writes to the database. All my recovery of the data for the user is typically done in an instance of the server configured to the user's time zone.
It's quite likely that this approach couldn't be used in every situation... and possibly some folk aren't intimidated by kidney stones (?).
