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...

https://dev.mysql.com/doc/refman/5.7/en/datetime.html

https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html

• "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; 

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_system_time_zone 

http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/

• NOW() - gives the server time
• UTC_TIMESTAMP() - gives UTC date and time

*** READ THIS ***
http://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices?rq=1

*** AND THIS ***http://stackoverflow.com/questions/1646171/mysql-datetime-fields-and-daylight-savings-time-how-do-i-reference-the-extra

http://stackoverflow.com/questions/1349280/storing-datetime-as-utc-in-php-mysql

http://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp


http://stackoverflow.com/questions/19843203/how-to-store-a-datetime-in-mysql-with-timezone-info

• "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"

http://programmers.stackexchange.com/questions/126208/datetime-vs-timestamp-in-mysql-and-php-in-practice

http://www.webdevelopersdiary.com/blog/good-to-know-how-to-properly-store-date-and-time-values-in-mysql

http://code.openark.org/blog/mysql/timestamp-vs-datetime-which-should-i-be-using