MySQL – Does Not Follow Ubuntu System Time Zone

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values.

http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html

Change your binlog format to ROW to eliminate the vagaries of statement-based replication and then change your DATETIME columns to TIMESTAMP (on the master, let the change replicate).

This solves the issues you're experiencing.

Although... it solves entirely the wrong problem, which is that it's typically better to store times properly in UTC and localize them as needed. Of course, that's what TIMESTAMP columns essentially do -- they store datetimes as UTC after converting then from the current session time zone, and then they convert back when you read them.

If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.

I used to face the same issue as you and I resolved it by following these quick steps.

Please follow below:

Environment:

• MySQL Version: 5.7

• OS: Windows 10 (64 bit)

Steps:

• Download the package that contains the data files of pre-built time zone tables. You can download here https://dev.mysql.com/downloads/timezones.html
  • timezone_2018e_posix_sql.zip - POSIX standard for MySQL version 5.7
• Extract the downloaded package, you will then have a file named timezone_posix.sql
• Open a command line in the root directory of MySQL. Ex: On my computer this is "C:\Program Files\MySQL\MySQL Server 5.7\bin"
• Run the command mysql -u root -p to login to MySQL with root access and then enter password.
• Run the command show databases; to view all database. You will see a database named mysql. You need to insert data into the mysql database
  • Run the command use mysql;
• Finally, you can insert data by issuing the command source *path timezone_posix.sql*.

After inserting the timezone data, you can test by issuing the following query:

 SELECT CONVERT_TZ('2018-07-02 14:46:46','utc','America/Los_Angeles')

If the result is not null, you're successful!!!