MySQL NOW() showing incorrect time (-1 Hour due to UK BST)

Give this a try : http://laravel.io/forum/08-27-2014-timezone-in-homestead?page=1#reply-20492

Thanks for that, I tried a few combinations (all the logical ones that should work). GMT, GB, Europe/London but still the same issue. MySQL (and Homestead) are an hour behind my Mac's current time.

@mstnorris Try this one:

http://stackoverflow.com/a/19069310/1972768

@kfirba thank you, that helped but I would have to do that each time I run homestead up (after the VM has been destroyed). I don't think it is a permanent solution.

Does anyone know where MySQL is getting it's Timezone from as my Mac and Homestead both show the correct time.

See what time the VM is set to as well (SSH)

date

Mac

Fri 26 Jun 2015 10:07:33 BST

Homestead

Fri Jun 26 10:08:01 BST 2015

MySQL

As per this question that @kfirba mentioned earlier. When I run the following within the MySQL prompt within Homestead I get:

SELECT @@global.time_zone;
+--------------------+
| @@global.time_zone |
+--------------------+
| +00:00             |
+--------------------+

SELECT @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| Europe/London       |
+---------------------+

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
+--------------------------------+
| TIMEDIFF(NOW(), UTC_TIMESTAMP) |
+--------------------------------+
| 01:00:00                       |
+--------------------------------+

@mstnorris did you try setting the timezone in the my.cnf file of MySQL?

Try adding the default-time-zone property after the [mysqld]:

sudo vim  /etc/mysql/my.cnf

# in the file:
[mysqld]
default-time-zone='-01:00'

I think it may actually solve your problem

@kfirba can that be added to the after.sh script so it doesn't need to be run manually each time?

You can replace or append stuff to a file via a command, yes.

@mstnorris First, I don't get it why you destroy your machine each time instead of (homestead) suspending it.

I guess you can do something like:

sed  '/\[mysqld\]/a default-time-zone="-01:00"' /etc/mysql/my.cnf

@kfirba the timezone is still an hour behind. date on my Mac and Homestead show 10:26 AM while MySQL is wrong.

select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-26 09:25:48 |
+---------------------+

@bashy I know you don't use Homestead as you have you own custom setup however the principles are still the same.

I'd also like to know if this affects others who are based in the UK as I guess it is a BST thing.

Hi @mstnorris, this also affects me (UK, OS X, Homestead):

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-26 09:53:39 |
+---------------------+

@gwp I'm glad I'm not the only one :)

It isn't an issue (well it is) as I am aware of it. But I have some Middleware that checks if a User is Active and another to check if they are Verified and both of these use Timestamps so I check that a Timestamp is present and is it <= the current time which gives me a lot of control over being able to define a date in the future when a User will be Active. (I hope that makes sense).

Just a note, I have mine set to SYSTEM so it uses the system time for dates.

mysql> SELECT @@global.time_zone;
+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM             |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-26 12:48:10 |
+---------------------+
1 row in set (0.00 sec)

mysql> \q
Bye
┌[vagrant☮local]-(~)
└> date
Fri Jun 26 12:48:16 BST 2015
┌[vagrant☮local]-(~)
└>

@bashy mine is set to "SYSTEM" too. The above was after I altered it according to @kfirba's suggestion. As that didn't solve it I reverted back to the default. I (seemingly) have the same set up as you yet it still shows the hour difference - so there must be a discrepancy elsewhere.

SELECT @@global.time_zone;
+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM             |
+--------------------+

SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-26 11:53:11 |
+---------------------+

\q
Bye
vagrant@homestead:~$ date
Fri Jun 26 12:53:31 BST 2015

Ah okay, weird. What does system use then?

I have no idea. Will do a little more digging and report back.