SET time_zone problem

Discussion in 'Databases' started by dizyn, May 31, 2010.

0
  1. #1
    Hi,

    I am facing a problem in SET time_zone

    mysql_query("SET time_zone = '+4:00'") or die("");
mysql_query("insert into `aa` set mytim = now()") or die("");
    PHP:
    I expect this to insert time GMT +4 time in database but it does not do that. how can i do that? i have field of "timestamp" type and i want to insert FMT +4 time in it when updated.

    any help please

    thanks
     
    Last edited: May 31, 2010
    dizyn, May 31, 2010 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    You need to name the actual timezone that you want to set to.

    ex: SET time_zone = 'US/Eastern';

    GMT +4 would be something like:

    SET time_zone = 'Asia/Dubai';
     
    jestep, Jun 1, 2010 IP
  3. saviola

    saviola Peon

    Messages:
    17
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #3
    To set the MySQL timezone to UTC without privilege:

    SET SESSION time_zone = '+0:00'
    to read the timezone which currently applies to NOW(), FROM_UNIXTIME(), UNIX_TIMESTAMP(), and the implicit conversion done when reading and writing columns of the TIMESTAMP type:
    SELECT @@session.time_zone
    do not use the magic timezone name 'SYSTEM' anywhere, because if you have used 'SET SESSION time_zone' then you are not operating under 'SYSTEM' timezone. For example

    CONVERT_TZ(, 'SYSTEM', @@session.time_zone) is no longer a no-op in that case.

    to write a UNIX timestamp into a DATETIME column in UTC, regardless of the session timezone:

    CONVERT_TZ(FROM_UNIXTIME(%ld), @@session.time_zone, '+0:00') (**note)

    to write the same to a TIMESTAMP column, instead of a DATETIME column:

    FROM_UNIXTIME(%ld)

    to write the present time to a DATETIME column in UTC, regardless of the session timezone:

    CONVERT_TZ(NOW(), @@session.time_zone, '+0:00') (**note)

    to write the same to a TIMESTAMP column:

    NOW()

    to read a UNIX timestamp out of a DATETIME column stored in UTC:

    UNIX_TIMESTAMP(CONVERT_TZ(column, '+0:00', @@session.time_zone)) (**note)

    to read a UNIX timestamp out of a TIMESTAMP column:

    UNIX_TIMESTAMP(column)

    to read a date and time, in UTC, pretty-printed by MySQL, out of a DATETIME column stored in UTC:

    SELECT column

    to read the same date out of a TIMESTAMP column:

    SET SESSION time_zone = '+0:00'
    SELECT column

    it is not possible to use CONVERT_TZ to read a TIMESTAMP column. You _have_ to set the session time_zone instead. You'd think CONVERT_TZ would work, but AFAICT passing @@session.time_zone as an argument to CONVERT_TZ works on INSERT but does not work on SELECT.
     
    saviola, Jun 3, 2010 IP