Sunday, September 20, 2015

2015-09-20 Sunday - MySQL Date Handling

In preparation for some API design work I'm doing - to include exposing an API internally, to B2B partners, and potentially to a Developer Community - as well as the challenges of supporting a global deployment of a solution - I'm doing some research today on MySQL date (and timezone) handling...these are the resources I've found relevant...and which may be of interest/use to others...
  • "When the server starts, it attempts to determine the time zone of the host machine and uses it to set the system_time_zone system variable. The value does not change thereafter."
  • "You can set the system time zone for MySQL Server at startup with the --timezone=timezone_name option to mysqld_safe. You can also set it by setting the TZ environment variable before you start mysqld."
  • "The initial global server time zone value can be specified explicitly at startup with the --default-time-zone=timezone option on the command line, or you can use the following line in an option file:"
    • default-time-zone='timezone' 
  • "Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:"
    • mysql> SET time_zone = timezone;
  • "The current values of the global and client-specific time zones can be retrieved like this:"
    • mysql> SELECT @@global.time_zone, @@session.time_zone;

*** READ THIS ***

*** AND THIS ***

  • "You shouldn't want to store time zone info in database. Store all date/time data as UTC and always make time zone offset adjustment on the application layer. –  marekful Nov 7 '13 at 18:00" 

No comments: