2

Suddenly, the NOW() function stopped saving the server's time and now I have a mess printing data using the 'time ago' implementation. It is weird.

My server's time (Hosting & MySQL) is -07:00 GMT (7:45am). My current time is: -04:00 GMT Eastern Daylight Time (10:45am).

I already corroborated the server's time using local functions.

If I save something using NOW(), the time in the field is (2015-09-04 15:45:40)... it is adding 8 hours ahead, I don't know why, It was working fine.

So, now I'm wondering what would be the best way to fix this? Thanks for any advice.

UPDATE

I just found out that server is using the UNIX timestamp to save things on Database.

So, my current time: -04:00 (13:48)
Server current timezone -07:00 (10:48)
Server UNIX timestamp: GMT (17:48)

Not sure if this information works to find a solution. I want it saves things using the server current timezone.

UPDATE 2

Only solution found so far was using the date function instead of NOW() which uses the hosting server time, not the database's.

$today = date("Y-m-d H:i:s");

This implies making changes in all scripts where I was using NOW()...geez!

Cyrus Mohammadian
  • 4,982
  • 6
  • 33
  • 62
JohnA10
  • 326
  • 1
  • 4
  • 18
  • 1
    Maybe you set your session time zone, so it's displaying in a different timezone than normal. – Barmar Sep 04 '15 at 14:57
  • 1
    using mysql client, issue command and verify it is set to SYSTEM: show variables where Variable_name = 'time_zone'; – Erik Flitman Sep 04 '15 at 15:04
  • @Barmar How I can check if I set my session time zone to a different one? – JohnA10 Sep 04 '15 at 17:16
  • `SELECT @@session.time_zone;` like in the answer. – Barmar Sep 04 '15 at 19:26
  • @Barmar, I tried it that one, but I got: "#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation". I don't have direct access to my MySQL server. – JohnA10 Sep 04 '15 at 21:33
  • 1
    You got that error for a `SELECT` query? I thought you got that when you tried to do `SET @@global.time_zone`. – Barmar Sep 04 '15 at 21:35

2 Answers2

3

As pointed here :

  1. check my.cnf for default_time_zone='+00:00' section.

  2. execute this query SELECT @@global.time_zone; and, if needed, change the values

SET GLOBAL time_zone = '+8:00'; SET GLOBAL time_zone = 'Europe/Helsinki'; SET @@global.time_zone='+00:00';

  1. execute this query SELECT @@session.time_zone; and, if needed, change the values

SET time_zone = 'Europe/Helsinki'; SET time_zone = "+00:00"; SET @@session.time_zone = "+00:00";

Community
  • 1
  • 1
mariobgr
  • 2,143
  • 2
  • 16
  • 31
  • I don't have direct access to the server, only through phpMyAdmin. I executed SELECT @@session.time_zone; SET time_zone = 'America/Los_Angeles'; SET time_zone = "-07:00"; SET @@session.time_zone = "-07:00"; which is the time zone of the server, but it didn't solve the problem. Not sure how to check my.cnf. – JohnA10 Sep 04 '15 at 17:15
  • I got "#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation " trying to use SET GLOBAL time_zone – JohnA10 Sep 04 '15 at 17:39
  • if you are on a shared hosting, can you ask the hosting provider's administrators to give you the needed permissions (or to execute the queries for you)? – mariobgr Sep 04 '15 at 22:00
  • I don't think the inoperative people of G.D. let me do that, but I'll try. Thanks for the suggestion. – JohnA10 Sep 04 '15 at 22:16
  • 1
    Well... if you don't have the permissions to change something, and obviously it was changed, there is a pretty good chance that they messed up somehow... – mariobgr Sep 04 '15 at 22:17
2

Try adding date_default_timezone_set to the top of your script, and then use date instead of NOW()

date_default_timezone_set('Europe/London');
echo date('Y-m-d H:i:s');
Jamie Bicknell
  • 2,306
  • 17
  • 35
  • 1
    That sure is a working option, but it is better to be used when you have a specific script that needs the different timezone. It could cause a pain in the ass if you forget to add it on a page every time. I don't think that's the author's goal. – mariobgr Sep 04 '15 at 15:05
  • I added **date_default_timezone_set('America/Los_Angeles');** at the top of my script which is the time zone of the server, but it is still adding 8 extra hours when saving into the DB – JohnA10 Sep 04 '15 at 17:11
  • mariobgr is right, if this is a solution, I'd have to change algorithms in over 50 different files and scripts... it would be a p.i.t.a... it was working fine, saving things with the server's time, not sure what happened. – JohnA10 Sep 04 '15 at 17:17