Date: Fri, 26 Mar 2021 22:32:25 -0600 From: Gary Aitken <freebsd@dreamchaser.org> To: Bruce Ferrell <bferrell@baywinds.org>, freebsd-questions@freebsd.org Subject: Re: mysql time-zone ambiguity? Message-ID: <66bad0ec-a886-3740-98b3-e30b41b16ccc@dreamchaser.org> In-Reply-To: <6cc15ffb-8941-27bd-e9ef-b4960c4ed303@baywinds.org> References: <dba947d1-c5bd-b732-55da-4651d9297dff@dreamchaser.org> <bfc8b528-8af0-2d13-7294-0095549ffd25@baywinds.org> <80c1be90-8780-6cb9-36aa-1dc4e22ccfaf@dreamchaser.org> <6cc15ffb-8941-27bd-e9ef-b4960c4ed303@baywinds.org>
next in thread | previous in thread | raw e-mail | index | archive | help
On 3/26/21 12:11 PM, Bruce Ferrell wrote: > On 3/26/21 10:39 AM, Gary Aitken wrote: <snip> >> I read that but thought (and still think) it should happen by default. >> Why doesn't the default installation process load the time zone tables? >> The default installation results in mysql setting the time to "SYSTEM", >> with or without the time zone tables loaded. >> Since the system appears to be returning the time "MDT", which mysql >> doesn't understand, it seems to me there is something wrong with the >> mysql_tzinfo_to_sql translation or the zoneinfo or both. >> After loading the time zone tables, if .my.cnf default-time-zone is explicitly >> set to "MDT" (what the default 'SYSTEM' value for mysql results in), I still >> get an error. >> I have to not only load the zone tables into the server, >> I have to also explicitly set default-time-zone='America/Denver' >> >> For some reason, 'MDT' and 'America/Denver' are not considered equivalent. > There is what "should be" and the way it works. Opinions always vary on what should be default. > > I suspect the reason for not making a symbolic setting is the data/time information is stored numerically and all matching is done numerically. Conversion from a symbol to a number (and any related manipulations) would slow the sql. Do enough of them and it really messes with performance. hmmm At first I thought this was just about server startup, but I can see it may be about jdbc connection establishment. If that's so, then possibly bad, depending on how often the connection is made/broken, although it is only a hash lookup in a small table. But there shouldn't be any translation on record access or sql computations other that adding the GMT offset, which would be done whether the offset is specified on the connection via symbol or number. Output formatting is a different matter. localhost [(none)]> select @@GLOBAL.time_zone, @@SESSION.time_zone; +--------------------+---------------------+ | @@GLOBAL.time_zone | @@SESSION.time_zone | +--------------------+---------------------+ | America/Denver | America/Denver | +--------------------+---------------------+ 1 row in set (0.00 sec) localhost [(none)]> select now(); +---------------------+ | now() | +---------------------+ | 2021-03-26 22:25:17 | +---------------------+ 1 row in set (0.02 sec) Gary
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?66bad0ec-a886-3740-98b3-e30b41b16ccc>