Specifying/remembering MySQL session time_zone setting

Hello,

I could not see how I may specify connection time_zone for a session in session manager to avoid having to issue a SET time_zone = 'American/Vancouver' every time I open a saved MySQL session.

Is this possible at all and if not, could it be added in future updates?

Thanks

Never heard of that SET time_zone feature yet. What does it exactly do, for what purpose do you set it? Do NOW() and other date/time function look at that variable and return appropriate values?

You can specify anything you want for a session. Just write it into a file and set it in the "Startup script" input:



Uploaded with ImageShack.us

http://img820.imageshack.us/img820/1591/heidisqlstartupscript.png

Valid link is the last one.

Sorry for the noise, I just wanted to test if the forum supports the [img] tag :)

@anse: SET time_zone defines the client time zone. When this is different from the server´s time zone, the server will convert some datetime values, notably timestamp field values and now(), to the client's time zone:
"[...] affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns." (from here).

[img] and [url] only worked in lower case letters, which I just made case insensitive.

For the timezone thing I'm just thinking if I can automatically detect it, using some Windows API function or whatever. That way, HeidiSQL could fire a SET TIMEZONE ... without a need for a new session setting.

Ah, seems I should use offset integers in a string, like this:

SET @@session.time_zone:='+10:00';

AFAIK, GetTimeZoneInformation() will provide this offset as 'Bias'.

I suspect if I set the timezone at connection start, I should remove that SELECT NOW() when editing date/time values in grids, and send them to the server using

INSERT INTO ... (dcol) VALUES (CONVERT_TZ('2012-05-14 08:38:00', @@time_zone, 'system');

Hm, I found I will break grid editing of DATE/TIME columns when settings the time zone, as entering such a cell with a NULL value in it issues a SELECT NOW() to overwrite the NULL value. If NOW() retrieves UTC-fixed date/time, and I send that value later to the server, that's all fine. But I could also leave that SELECT NOW() away and use the client time. SELECT NOW() was introduced for a user which complained about wrong time zone for default date values.

Now, MySQL says that only TIMESTAMP columns do have this magic. DATETIME/TIME/DATE columns don't do this conversion. Is that true or am I missing something? If so, I will introduce inconsistent behaviour for editing date + timestamp grid cells.

Note that the result of Now() is computed according to the @@session.time_zone value.

Done in r4141. Which will break UTC-fixed values for datetime, date and time values in grid editing, when sending the local client time. See issue #1835.

It should be noted that for this automagic to work, the corresponding server variables system_time_zone and time_zone have to be set to a correct timezone value.
With MariaDB 5.5 on windows, the system_time_zone value is set wrong on my system; this can be corrected by setting time_zone=xxx in the [mysqld] part of server´s my.ini.

SET time_zone funktioniert ab 4.1.xx
Aber nicht bei 4.0.xx, die ich besitze!

r4140 funktioniert ja

mysql 4.0.xx kennt time_zone nicht!
set time_zone=xxxx wird nicht akzeptiert!
ich denke, es liegt an r4141 ff.

select @@session.timezone führt auch zu einem Fehler, obwohl diese Variable existiert!

I'll translate for the other non-germans here...

SET time_zone does not work on old servers. Somebody here who knows in which server version that was introduced? I need the *exact* version so I can add an appropriate version conditional in HeidiSQL.

in v3+4 docs: "This variable was added in MySQL 4.1.3".

Thanks. Fixed in r4145.

Danke fürs "fixing"

vg rh

Bitte bitte. Das nächste mal aber bitte auf englisch. Danke.

Hallo all,

Although this feature might be handy for some users, I'm having trouble to write and debug code with it. Let met illustrate that with the following example:

The server is set to UTC, as is Mysql. I connect to it from my PC and write a stored procedure. If I use the function NOW() (which is by far the most used function for the current datetime in our code and in other sample code on the net) the behaviour is different while debugging and executing the procedure. The procedure runs as root on the server with th server timezone. The test runs with the local timezone of the client. And there is no warning at all that there is a timezone conversion taking place.

So therefore I suggest that this feature is optional, and off by default. The most convinient way would be to have a drop down list on the server connection panel with all possible local timezones in it, including the Mysql default 'System' timezone. With that one can simulate the workings of the database as a user in an other timezone (like in America or so) if needed, and the feature can be turned off.

(btw: I'm the user that requested the NOW() to use the server timezone in the record grid. If the timezone for the connection can be set by the user, then it probably makes sense to use the return value of NOW() in the grid as is now. If someone has set the timezone, it will be the local timezone. If someone has set the system timezone (Mysql default), the NOW() will return the system timezone)

Three notes about the grid topic:
* That's the default value for NULL date/times when starting a cell editor, nothing more.
* Also, for TIMESTAMP fields, MySQL automatically converts this local time to the server's time, now that Heidi sets the time_zone. So, if any, we have a minor glitch in DATE/TIME fields, not in TIMESTAMP fields.
* Recently I added the functionality to insert function calls into a grid cell, via Ctrl+F2 (Right click, "Insert value" > "SQL Function"). I think this is what you really need - just press Ctrl+F2, enter "NOW()" and you're done.

Ah, one more note: To switch back to the server's time_zone, you can create a startup script with "SET time_zone='system';" and set this up in the session properties. The startup file is executed after HeidiSQL's own "SET time_zone='...';", so that should fix your problem.

Yes that could be done, but is quite non-standard. I haven't seen a client yet that does timezone conversion by default. It needs to be added for every server and is easily forgotten. It's a potential source of bugs, because scripts no longer give the same result whether they're run on HeidiSQL or other clients. Working with timezones in the correct way is already hard enough.

A checkbox to turn the option on or off (use client PC timezone) is very welcome to overcome this problem.

The more luxury approach would be something like this:



This also allows to choose an other timezone than the one on the current PC.

Hm, why not. But I'd prefer to have a checkbox only "Use local timezone (GMT +02:00)", as I find it confusing to be able to set the local timezone to any other than system or the client pc's timezone. Thanks for the idea, I think that's a good solution finally.

Thanks, the checkbox "Use local timezone (GMT +02:00)" will be fine. Will it be set to off by default?

The luxury option can always be set by users with the startup script if they want.

Done in r4152, as discussed, the simple checkbox, off by default. chinook, please notice you have to activate that in the session manager.