5 Replies Latest reply on Jun 10, 2010 12:42 AM by pmorch

    Changing time zone

    AngryCloud Level 1
      This isn't a Dreamweaver question but I thought someone here might be able to help.
      How do I change my server's time zone? Right now MySQL seems to be timestamping every record with Mountain time. I would like it to be Eastern by default.
        • 1. Re: Changing time zone
          Level 7
          AngryCloud wrote:
          > How do I change my server's time zone? Right now MySQL seems to be
          > timestamping every record with Mountain time. I would like it to be Eastern by
          > default.

          Unless it's your own server, the answer is that you can't.

          There are a number of ways that you can convert the timestamp. Which
          method you use depends on your server setup. The easiest is if your
          server runs PHP 5.1 or later on Apache. This lets you set your own
          timezone in an .htaccess file. Other alternatives are to store the
          timestamp as UTC, and convert, or to use the CONVERT_TZ() function in
          MySQL 4.1.3 or above. To use CONVERT_TZ(), you need to ask your hosting
          company if it supports it.

          --
          David Powers, Adobe Community Expert
          Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
          Author, "PHP Solutions" (friends of ED)
          http://foundationphp.com/
          • 2. Re: Changing time zone
            AngryCloud Level 1
            My host uses PHP 4.4.6 and MySQL 4.1.22. Does this mean I have no option but to allow timestamps to be stored in GMT-7 and just manually convert them to GMT-5 in every place on my site that displays a timestamp from a database?
            • 3. Re: Changing time zone
              Level 7
              "AngryCloud" <webforumsuser@macromedia.com> wrote in message
              news:fb1v8j$o0n$1@forums.macromedia.com...
              > My host uses PHP 4.4.6 and MySQL 4.1.22. Does this mean I have no option
              > but to
              > allow timestamps to be stored in GMT-7 and just manually convert them to
              > GMT-5
              > in every place on my site that displays a timestamp from a database?

              The conversion doesn't have to be manual, you may be able to use one of the
              built-in Server Formats to reformat the databindings. There are several
              third-party server format extensions available.

              Joris

              • 4. Re: Changing time zone
                Level 7
                AngryCloud wrote:
                > My host uses PHP 4.4.6 and MySQL 4.1.22.

                MySQL 4.1.22 supports the CONVERT_TZ() function. If your hosting company
                has configured MySQL to understand time zone names, you can use names
                such as US/Eastern. If it hasn't configured MySQL to support time zones,
                you can use it with the time offset like this in a SQL query:

                SELECT CONVERT_TZ(timestamp_column_name, '-07.00', '-05.00') AS
                local_time FROM table_name

                --
                David Powers, Adobe Community Expert
                Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
                Author, "PHP Solutions" (friends of ED)
                http://foundationphp.com/
                • 5. Re: Changing time zone
                  pmorch

                  MySQL lets you set the timezone on a pr. session basis. Here we first view a datetime, then change time zones, then view the same datetime again:

                   

                  $ mysql

                  mysql> use information_schema

                  mysql> select CREATE_TIME from TABLES where CREATE_TIME is not NULL limit 1;
                  +---------------------+
                  | CREATE_TIME         |
                  +---------------------+
                  | 2010-06-10 09:37:01 |
                  +---------------------+
                  1 row in set (0.03 sec)

                  mysql> SET SESSION time_zone = 'America/Los_Angeles';
                  Query OK, 0 rows affected (0.00 sec)

                  mysql> select CREATE_TIME from TABLES where CREATE_TIME is not NULL limit 1;
                  +---------------------+
                  | CREATE_TIME         |
                  +---------------------+
                  | 2010-06-10 00:37:16 |
                  +---------------------+
                  1 row in set (0.03 sec)

                   

                  Note how the displayed datetime is different. Wouldn't it be fine to call "SET SESSION time_zone"?