MySQL Server GMT

[MarkW]

I'm trying to set a datetime column in my table to the current GMT time, however local time is what actually gets set.[nbsp][nbsp]I use the NOW() command in my query to do this.

Anyone know how I can achieve my goal?

Thanks
Mark

[Rich]

You need to set the TZ (timezone) environment variable before invoking the mysql date functions.

In Perl, use:
my $env = $ENV{TZ}; # save it
$ENV{TZ} = 'GMT'; # change it
# Do mysql date thingy here
$ENV{TZ} = $env; # restore it

In PHP, use:
$env = getenv('TZ'); # save it
putenv('TZ=GMT'); # use it
# Do mysql date thingy here
putenv("TZ=$env"); # restore it

For a complete list of time zone names you can use, see:
www.timezoneconverter.com/cgi-bin/tzref.tzc (http://www.timezoneconverter.com/cgi-bin/tzref.tzc)

Rich

[John Lim]

In PHP you can use the gmdate() function i believe.

Bye, john

[MarkW]

Have tried your code example Rich, but have been unsuccessful in getting it to work.[nbsp][nbsp]

$env = getenv('TZ'); # save it
putenv('TZ=GMT'); # use it
$query = "INSERT INTO mytable(dt) VALUES (NOW())";
$result=MYSQL_QUERY($query) or die();
putenv("TZ=$env"); # restore it

Anyone see any obvious problems?

I've echoed the saved var but it doesn;t have a value stored in it.

Thanks
Mark

[PaulKroll]

Well, yes. :)

The obvious problem there is you're setting the web server's local TZ variable to GMT, but using the MySQL NOW() function: MySQL is on another server, so setting the web server's TZ will only help with the PHP date/time commands, so I think the issue got confused here.

You may want to look at the UNIX_TIMESTAMP() MySQL command, on page http://www.mysql.com/doc/D/a/Date_and_time_functions.html (near the end) which looks to do what you want but I've not used it and can only go by the docs.

[MarkW]

Paul - thanks for that wake-up call about the different servers.

Have resorted to the following solution which *seems* to work - and is quite tidy:

$gmdt = gmdate("Y-m-d H:i:s", TIME());
$query = "INSERT INTO mytable(dt) VALUES ('$gmdt')";
$result=MYSQL_QUERY($query) or die();

Regards
Mark

[Rich]

<gulp>

Yes, thanks, Paul.

Rich

[PaulKroll]

Hey, we all miss things from time to time... wouldn't have to scan many of my posts to find something Just Slightly Off in one (or ten... or twenty...) of them. :)