Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Timezone antidote on mysql

The 'timestamp' fields are my problem



9:16 pm on Sep 6, 2005 (gmt 0)

10+ Year Member

Hello coopster and everybody

Iīve moved my hosting to a much bigger server, but the problem now is that it 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 my hosts 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



3:22 am on Sep 7, 2005 (gmt 0)

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member

with mysql you can't set a formula for a default db value.

I've run into another quirk with the timestamp type. I use it as a quick row identifier to match transactions between tables. It's very rare that the same client can make two transactions at the same time, so that has been helpful.

However, I noticed that when I make an update to the row with the timestamp datatype, the timestamp changes to the current timestamp! This is very disturbing, considering I'm using it as a join field. So I have to retrieve the value before making the update, and include the old value in my update command.

But to your problem.. I would create a new column with a datetime field, then write a simple update to set the value of the dt field to the ts field.

Then you can delete the timestamp field.

When you insert a row, just be sure to add the 4 hours, or whatever you need, to the sql command.


3:51 am on Sep 7, 2005 (gmt 0)

10+ Year Member

Hello txbakers,

Thanks a lot for your reply.

I see, your problem is just what I will miss most in the timestamp data type: to mark just what it says, a time-stamp of the time that record was modified. Thatīs just what I needed it to do, BUT WITH THE F* RIGHT TIMEZONE! ...lghs

As you say thereīs no way to include formulas in the default, I think Iīll just have to bury all my timestamps, and make them datetime, nanny-recorded every time I want to keep track of the last-modified-date of that record. Too bad, thatīs a dirty solution I think. If only mysql allowed a time_zone per user or connection... I could set Perlīs DBI to define it on the begginning of the db connection, every script.




4:36 pm on Sep 7, 2005 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

txbakers, the TIMESTAMP column-type quirk you described really isn't a quirk. It is actually designed that way and come in quite handy for exactly what phoenix_fly has described here. It is a bit different between release prior to and after 4.1 though. Details are here:

The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns in a table, only the first one is updated automatically...


One of the neat features is that if the column(s) aren't actually changed (the user left the value the same as it was prior to submitting), then no update actually occurred and the TIMESTAMP column is left unchanged. Nice.

If only mysql allowed a time_zone per user or connection...

Ah, but you can ...
MySQL Server Time Zone Support [dev.mysql.com]


4:38 pm on Sep 7, 2005 (gmt 0)

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member

That is a cool feature - especially if there are two of them. I'll have to add a second one then that doesn't change.


9:33 pm on Sep 7, 2005 (gmt 0)

10+ Year Member

Hey Coopster,

Thatīs right! I discovered that at 3 am in the morning yesterday!

Itīs just about including this in every script, just after connecting to the $dbh handle: (example for Perl)

$dbh->do ("SET time_zone = '-3:00';"); # or any timezone you're in!

As for Perl, just add this line to a .htaccess in your web root:

SetEnv TZ America/Sao_Paulo

I know, I know. Donīt know exactly whatīs the string for your timezone, right? Just type this at your shell prompt and go on surfing the menu untill you find your location:


And to make it perfect, just add this line to your document-root .bash_profile and your shell sessions will start already with your timezone set!

TZ='America/Sao_Paulo'; export TZ

Bingo! You wonīt notice your server is in Japan or across the street!

Well, I mean, ALMOST that... lghs... There are two magics I havenīt discovered the recipe yet:

#1 - How to tell Mr Cron the timezone he shall consder for his eternal dream-and-labor agony.

#2 - How to make mysql sessions you start on the shell already start in your timezone, I mean, without having to type "SET time_zone = '-3:00';" all the time before exercising your sql-talk interactively.

If you know one of these, Coopster, weīre about to have the most comprehensive and didatic timezone antidote thread on the web. And to break dogmas also, as I heard just about everywhere that this task wasnīt possible on a user basis.




2:32 am on Sep 8, 2005 (gmt 0)

10+ Year Member


Still didnīt find out the 2 recipes, but hereīs another tip:

The .htaccess only affects the timezone of the scripts when theyīre executed on the web, so if you plan to use CRON to execute any of them, and want to make sure your code remember your timezone in its localtime() functions, just make sure you add this to the beginning of the script: (example in Perl)

$ENV{'TZ'} = "America/Sao_Paulo";

(or the correspondent string for your tz)



Featured Threads

Hot Threads This Week

Hot Threads This Month