Re: Timezone
From: bill <william_at_TechServSys.com>
Date: Sun, 4 Nov 2018 10:56:06 -0500
Message-ID: <prn4ql$qcu$1_at_gioia.aioe.org>
>
> Sure - take a look at
> https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html
>
> Your client can set their own timezone when they connect, and it will only
> 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.
>
Date: Sun, 4 Nov 2018 10:56:06 -0500
Message-ID: <prn4ql$qcu$1_at_gioia.aioe.org>
On 11/4/2018 10:02 AM, Lew Pitcher wrote:
> 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
>
> Your client can set their own timezone when they connect, and it will only
> 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.
>
Thanks a lot - just what I needed Received on Sun Nov 04 2018 - 16:56:06 CET