Re: Timezone

From: Lew Pitcher <lew.pitcher_at_digitalfreehold.ca>
Date: Sun, 04 Nov 2018 10:02:04 -0500
Message-ID: <prn1le$ba4$1_at_dont-email.me>

bill wrote:

> My server is on the West Coast.  My client is in the Eastern
> timezone.  Is there any way to have a default timezone set to the
> Eastern timezone so that NOW() will return the client local time ?

Sure - take a look at
  https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html

[Quoted] [Quoted] Your client can set their own timezone when they connect, and it will only [Quoted] affect their own connection.

For example...
  mysql> select _at__at_GLOBAL.time_zone, @@SESSION.time_zone;

  +--------------------+---------------------+

  | _at__at_GLOBAL.time_zone | @@SESSION.time_zone |

  +--------------------+---------------------+
  | SYSTEM             | SYSTEM              |
  +--------------------+---------------------+

  1 row in set (0.00 sec)

both my session timezone and my global timezone are the same, and are whatever the local system timezone is

  mysql> select now();
+---------------------+

  | now() |
+---------------------+

  | 2018-11-04 09:56:24 |
+---------------------+

  1 row in set (0.00 sec)

and, it's almost 10AM, in this timezone

  mysql> set time_zone = CST6CDT;
  Query OK, 0 rows affected (0.00 sec)

  mysql> select now();
+---------------------+

  | now() |
+---------------------+

  | 2018-11-04 08:56:30 |
+---------------------+

  1 row in set (0.00 sec)

Now, we tell the connection to assume that we are in a new timezone, and ask it the time again. And now, it's almost 9AM.

  mysql> set time_zone = EST5EDT;
  Query OK, 0 rows affected (0.00 sec)

  mysql> select now();
+---------------------+

  | now() |
+---------------------+

  | 2018-11-04 09:56:40 |
+---------------------+

  1 row in set (0.00 sec)

Finally, we explicitly set our session timezone to the local timezone, and ask again. Back to almost 10AM

  mysql> select _at__at_GLOBAL.time_zone, @@SESSION.time_zone;

  +--------------------+---------------------+

  | _at__at_GLOBAL.time_zone | @@SESSION.time_zone |

  +--------------------+---------------------+
  | SYSTEM             | EST5EDT             |
  +--------------------+---------------------+

  1 row in set (0.00 sec)

And we only affected the session timezone. Other sessions have their own, unaffected timezone settings, and the server has /it's/ own, unaffected timezone setting.

-- 
Lew Pitcher
"In Skills, We Trust"

Received on Sun Nov 04 2018 - 16:02:04 CET