timezones

[zeroradius]

I am having a few problems, I have tried dev.mysql.com and php.net trying to find a solution but came up short.

here goes:

I need to convert my the Datetime of user post to a specific timezone (dosent really matter which as long as i know which one it is) I tried to get my server timezone by running :

SELECT @ @global.time_zone

at the MySQL prompt on my server But it returns a value of SYSTEM

Also is there a php function that would easily allow for me to convert from one time zone to another just for displaying data?

[Orjan]

I think you could store the time zone difference in a var, then do

$display = strtotime($zone." hours"); 

that is depending on having your computer set to UTC. If you don't, you could try read out your timezone in hours with

date("O")/100 

and add that to $zone used above to get the correct difference.

[Jordan]

Run this query (if you are root):

SET GLOBAL time_zone = timezone;

Replace timezone with the value you want. If you are not root, you can set the timezone per session:

SET time_zone = timezone;

Here is where this information comes from: MySQL :: MySQL 5.1 Reference Manual :: 9.7 MySQL Server Time Zone Support

To convert from one timezone to the next use convert_tz:

SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

From: MySQL :: MySQL 5.1 Reference Manual :: 11.6 Date and Time Functions

[zeroradius]

Thanks guys that was really helpful.

One thing I don't understand thou is this:

If you are not root, you can set the timezone per session:

SET time_zone = timezone;

Here is where this information comes from: MySQL :: MySQL 5.1 Reference Manual :: 9.7 MySQL Server Time Zone Support

I read the info at the link but it did not make sence to me. I also checked in my mySQL pocket guide and found nothing. Could some one show me an example of how to use this/a MySQL session veriable?

[Jordan]

That is an SQL query so you just need to run it before executing any other queries.

[Orjan]

I believe that you would only need to use CONVERT_TZ really, and use the default timezone set on the server as it is.

[zeroradius]

I believe that you would only need to use CONVERT_TZ really, and use the default timezone set on the server as it is.

That was one of the problems i was having.

I tried to get my server timezone by running :

SELECT @ @global.time_zone

at the MySQL prompt on my server But it returns a value of SYSTEM

I understand how to do it now. Thank you both that saved me a considerable headache.

[Orjan]

How do you do it?

[zeroradius]

you run

SET time_zone = timezone;

at the mysql prompt. One thing I just thought of thou, if my hosting resets my server and dosent tell me, it will screw up my timezones from there on out (-_-) that poses a potential problem. Sence session veriable are dynamic could I execute this when users log in as an extra precation by running it the way i would anyother mysql querry?