Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

howto change MySQL time_zone

by jeanluca (Deacon)
on Sep 11, 2007 at 08:25 UTC ( #638251=perlquestion: print w/replies, xml ) Need Help??
jeanluca has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks

My application does everything in the UTC time. Works great, however I use a field which gets the current time:
|Field | Type | Null |Key| Default |Extra | created| timestamp | YES | |CURRENT_TIMESTAMP| |
which stores time as local time (not UTC)
I tried to change the time_zone like
$dbl = DBI->connect(DBI:mysql......) ; $dbl->{'time_zone'} = "GMT" ;
(also tried UTC)
However the current timestamp is still in local time.
Currently the time_zone is set to 'SYSTEM'. I don't own the database, so I need to change the session TZ!

Any suggestions how to change this ?

Thnx a lot

Replies are listed 'Best First'.
Re: howto change MySQL time_zone
by Gangabass (Vicar) on Sep 11, 2007 at 09:04 UTC

    This SQL query will help you

    SET time_zone = timezone;

    See for details

Re: howto change MySQL time_zone
by pajout (Curate) on Sep 11, 2007 at 09:06 UTC
    When you google "mysql time zones", the first link gives you answer - you can set up time zone in connection by
    SET time_zone = timezone;
Re: howto change MySQL time_zone
by jeanluca (Deacon) on Sep 11, 2007 at 09:42 UTC
    I've seen that link before, but it didn't realy solve the problem. However, now that I've red it again, I finally understand that I need to install the time_zone table :( (without this table it is not possible to use names like 'UTC')

    Not very happy with that outcome, anyway, I think I have to go to the db-sysadmin

    Thnx a lot

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://638251]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (5)
As of 2018-06-17 22:20 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (107 votes). Check out past polls.