Timezone antidote on mysql

software development

#1

Hello everybody

Dreamhost is in GMT -7, while I´m in GMT -3 (Brazil).

I´ve given up adjusting another timezone just for me (it´s a shared hosting plan), as dreamhost staff said the .my.cnf wouldn´t work for that. So now I´m resignated to setting offset with Perl, before recording the ‘datetime’ columns.

But my problem is the ‘timestamp’ fields, on wich automatic recording relies my code. I tried setting the default of this ‘timestamp’ column to CURRENT_TIMESTAMP + interval 4 hour but this formula isn´t accepted by mysql as a default, not even between quotes, paranthesis or anything. The now() + interval 4 hour won´t work either.

So, before I convert them all to ‘datetime’ and make my sql statements act as a nanny when all I need is a timestamp behavior, I´d like to hear from you guys if you know about some work around for that.

Thanks a lot

phoenix_fly


#2

Have you tried using .htaccess to set the local environment timezone?
Frex, edit the .htaccess file in the working directory of your script to contain a line that reads:SetEnv TZ GMT-3Note that if you’re running this from shell, you’ll need to edit your .profile to include a line that reads:export TZ=GMT-3But honestly, I don’t know if that will affect the datetimes in mySQL - I would imagine it should, but I’ve never tried it myself.
Oh, and I’m not so sure, but it might actually be “GMT_3” (where the underscore is actually a plus-sign - for some reason this forum software edits-out plus signs); even after many years of flying around, timezones still sometimes confuse the fark-all outta me.


#3

Hey, thanks for the .htaccess tip. I posted another thread this weekend in this forum about problems with my perl cgi script breaking after the OS upgrade.

SetEnv TZ CST6CDT - fixed it.

Thanks again!
Mike


#4

Hey netdcon!

Thanks for the tip! And it works with mysql too, as long as you issue a ‘set time_zone …’ query on each script, right after connecting to the db handle.

Take a look at this thread I´ve been posting your and other findings I harvested with uptimate difficulty between many saying that it wasn´t possible:

http://www.webmasterworld.com/forum112/10-1-10.htm#msg6