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)
• Status: Closed
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: MySQL 4
• OS: Linux (Linux)
• CPU Architecture: Any

[7 Nov 2003 4:58] Olaf van der Spek

Description:
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

Hi!

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:

mysql> SELECT HOUR(UTC_TIMESTAMP()), UTC_TIMESTAMP(), CURRENT_TIMESTAMP();
+-----------------------+---------------------+---------------------+
| HOUR(UTC_TIMESTAMP()) | UTC_TIMESTAMP()     | CURRENT_TIMESTAMP() |
+-----------------------+---------------------+---------------------+
|                    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?