MYSQL and timezones


#1

sigh

so here’s my issue… I have a cron job that runs a script every 5 minutes to update my database automatically. My only problem is when the cron job runs let’s say at 3pm PST, the database will show that the data downloaded at 11pm (apparently GMT). So it will think any data that comes through between 3pm and 11pm is old. I resolved this issue before on my old server by setting the server time zone and mysql time to GMT.

Is there any way I can change the MySQL and server time zones or to download the data with a PST time stamp? I have already attempted to add the line SetEnv TZ GMT in .htaccess and that did not help. I’m am using a php script to run the mysql query.


#2

I’m wondering why you are seeing this happen, as all my MySQL instances (I’ve checked both MySQL 4 and 5 databases on Dreamhost) show the MySQL system_time_zone variable set to “PST” and return the time_zone variable set to “SYSTEM” when queried with “show variables” (from within the SQL query box of phpMyAdmin).

I’m wondering if somehow your php code is modifying the times before, or while, running the query that loads your database.

I know that’s not much help, but without seeing your code, or being able to inspect the system/runtime variables of your database, I can’t say much else. Did you perchance load this database with ajn “import” of data exported from your previous host (possibly inadvertently executing a query that affected your runtime variables for the database)?

I suggest you start with trying to find out why your MySQL server is using GMT instead of the SYSTEM (PST) values for the runtime variables. Open your database with phpMyAdmin (or code), run the query “show variables”, and look for the system_time_zone and time_zone variables to see for sure what your database in using. Once you confirm that, it will help to tell where to look next.

–rlparker


#3

Variables on my MySQL server:

system time zone = PST
time zone = system

The code I am playing with is the Backbase RSS Reader Starter Kit. The code can be downloaded here: http://www.backbase.com/demos/RSS.zip

Just a reminder, when the server is set to GMT, everything runs/updates perfectly! Unfortunately, you cannot set the time zones for dreamhost servers.


#4

Thanks for the link, I’ll go take a look at the code. You are correct you cannot set the server time at Dreamhost. The proper way to “correct” for timezone differences should be in your code.

Give me a few minutes to look at the code and I’ll get back to you :slight_smile:

–rlparker


#5

Thank you so much! Let me know if you have any questions about the code.


#6

Okay, I took a quick look a the code (though the Readme.txt file in the .zip archive refers to installation instructions that are not in the .zip :frowning: ), and noted that there is a setting in the RSS/includes/config.inc.php that should allow you to compensate for the offset your server has from GMT:

[quote]$config[‘db’][‘host’] = ‘localhost’; // Database connection
$config[‘db’][‘database’] = ‘rssreader’;
$config[‘db’][‘username’] = ‘rssreader’;
$config[‘db’][‘password’] = ‘rssreader’;

$config[‘time’][‘zone’] = 1; // The servers timezone offset from GMT 0
$config[‘time’][‘dst’] = true; // Take Daylight Savings Time in account?
[/quote]
What do you have the in your congig.inc.php for that value? If this setting is correct for the DH servers (the database is set to the same), and the code in the application is correct (and correctly installed), you should be good to go, while an incorrect setting in this variable could well account for the problem you are having.

–rlparker


#7

Sorry, the instruction the .txt file was talking about is probably located here

http://www.backbase.com/#dev/bpc3/tutorialsexamples/starterkits/rssreader/rssreader.xml

My time zone currently reads a value of 1 just like the code example above. I’ve played with this value before, but I’ll try both 8 and -8 to compensate for PST. I’ll let you know the results tomorrow crosses fingers


#8

It should be -8, but there is no harm in experimenting, and that will give you a couple of “test” parameters to see exactly how the code works. :wink: .

I’m pretty sure that is your issue, but I would like to hear how you make out.

–rlparker

ps. Thanks for the link to the doc…


#9

no luck :frowning: I changed the timezone value to -8 and 8 and left them each running for a few hours. The settings didn’t change the time stamp (still in GMT) and it did not update the database with new information (before changing the time zone values, I clear the data in the mysql database so it wouldn’t rely on the timestamps of the previous data)

Hrm… any other ideas? I think I’m on the verge of leaving dreamhost and going somewhere that runs their servers on GMT. It just sounds like a hassle.


#10

I’m sorry, I’ve not a clue what the demo application is doing :frowning: . As the http server, and the MySQL server, are both running with a system time setting at PST, it has to be the application that is manipulating the time to GMT.

It would seem to me, that to change hosts to facilitate the running of an “in development” application, or to limit yourself to a server running with a system time of GMT would be a lot of hassle for what you hope to gain, but obviously, YMMV :wink: .

Good luck!

–rlparker


#11

You are correct.

RSS/server/update.php is storing the date and time in the database as GMT.

This means that a new article added to the database will show the time as 23:00 even though it is 15:00 at DreamHost when querying the database.

The ‘zone’ variable comes into play when the script obtains the date and time of the last article. The script asks for the timestamp as UNIX epoch (which is UTC which is close to GMT). But wait, the mysql engine thinks the value was originally local time, not GMT, so the conversion is off. The script tries to compensate by adding or subtracting to the UNIX epoch value returned.

From what I can tell the script should work correctly - it’s just that browsing the database will look funny because the strings are GMT regardless of how you set ‘zone’ or what mysql thinks the timezone is. And setting the machine to GMT wouldn’t make a difference because the GMT strings are converted from UNIX epoch which is already GMT.

mysql reads the TZ environment variable when it starts mysqld, so setting it in a script or shell won’t help.

:cool: [color=#6600CC]Atropos[/color] | openvein.org


#12

Doh! Now I feel pretty stupid, and lazy, as I really only looked at the config file, and thought setting the time offset variable would do the trick.

Thanks! for looking at it further…you Rock!

–rlparker