Bug #1776 hour() without TZ correction
Submitted: 7 Nov 2003 4:58 Modified: 17 Nov 2003 8:36
Reporter: Olaf van der Spek (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S4 (Feature request)
Version:MySQL 4 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[7 Nov 2003 4:58] Olaf van der Spek
The hour() function always uses TZ correction, while I want it to use GMT time (no correction).
The only way to achieve this, is by setting the server-wide TZ environment var.
I'd like to be able to set the TZ correction per-user or per-connection instead.

How to repeat:
mysql> select hour(now()), now() from t;
| hour(now()) | now()               |
|          13 | 2003-11-07 13:53:58 |
1 row in set (0.01 sec)

Suggested fix:
Add per-connection or per-user TZ var or always use GMT.
[17 Nov 2003 8:36] Dmitry Lenev

Per connection timezone support is in our short-term TODO and moreover it is being implemented now. It will appear in one of not so distant versions of MySQL (but I can't tell now would it be in 4.1 branch or in 5.0).

On the other hand starting from version 4.1.1 (and in version currently availiable from 4.1 source repository) MySQL will support UTC_TIMESTAMP() function. Using this fucntion with HOUR() function you will be able to solve your problem:

|                    17 | 2003-11-17 17:33:37 | 2003-11-17 20:33:37 |
1 row in set (0.00 sec)

Thank you for your interest in MySQL!
[17 Nov 2003 8:55] Olaf van der Spek
That was just a simple example. I'm using hour on a timestamp column. Is there a workaround for that situation too?