client timezone information needs to be handled also for modifications to timestamp values #2962
Milestone
Comments
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Date: 2012-01-01 06:16:16 +0100
From: hsaddington <<hs.addington>>
To: Holger <>
Version: 11.15.11 (Feb2013-SP3)
CC: bugs-sql, holger.pirk, @njnes
Last updated: 2013-09-27 13:47:20 +0200
Comment 16709
Date: 2012-01-01 06:16:16 +0100
From: hsaddington <<hs.addington>>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.7 (KHTML, like Gecko) Chrome/16.0.912.63 Safari/535.7
Build Identifier:
current_date and current_timestamp are behaving in odd ways. It looks like the current_date ignores the local timezone and gives a date that is exactly 24 hours ahead. I think it is giving the correct local time, but is giving the date based on GMT. The current_timestamp gives correct result. But, if we extract the day (extract(day from current_timestamp), it also give a wrong result. On New Year's Eve (i.e. tonight), it gives wrong year, month, and day!
It is the New Year's Eve. And, the following query gives incorrect results.
"select current_date" results in 2012-01-01.
Reproducible: Always
Steps to Reproduce:
1.select current_date
2.select current_timestamp, extract(day from current_timestamp)
3.Run the above queries before and after the day switch based on GMT
Actual Results:
It is the New Year's Eve. And, the following query gives incorrect result.
select current_date ====> 2012-01-01
select current_time ====> 21:13:06
select current_timestamp ====> 2011-12-31 21:13:15
select extract(month from current_timestamp) ======> 1
Comment 16713
Date: 2012-01-02 15:09:27 +0100
From: @sjoerdmullender
I'm assuming you're located somewhere West of Greenwich, UK. At the time you did your queries, it was already past midnight in Greenwich, and the server gave back the times in GMT (or UTC).
The server uses GMT internally, but clients can tell the server what timezone they are in. mclient does that.
It looks like you used a client that didn't tell the server what timezone it is in, so all the server can tell is what the current time/date is in Greenwich.
So, which client were you using for your test?
If not mclient, you could use something like
SET TIME ZONE INTERVAL '-05:00' HOUR TO MINUTE
to set the time zone for the client (this example: 5 hours West of Greenwich).
When using mclient for these queries, the results (for me) include the timezone information (after I fixed a bug in the DST determination):
sql>select current_timestamp;
+----------------------------------+
| current_timestamp |
+==================================+
| 2012-01-02 15:05:47.000000+01:00 |
+----------------------------------+
1 tuple (0.519ms)
sql>select current_time;
+-----------------------+
| current_time |
+=======================+
| 15:05:50.000000+01:00 |
+-----------------------+
1 tuple (0.533ms)
Comment 16714
Date: 2012-01-02 22:35:19 +0100
From: hsaddington <<hs.addington>>
Hello:
Yes. I am near Los Anageles, USA.
I was using DBVisualizer and mclient. For DBVisualizer, I was using JDBC driver version 1.17. I thought I got exact same results in both clients except the mclient did not account for the DST so it was off by 1 hour compared to what I was getting in DBVisualizer.
I think DBVisualizer tells the server what timezone I am in because current_time that I get from the server is the correct local time.
Let me try again later in the evening when it is past midnight in Greenwich. I thought I was getting results that did not look right even in mclient.....
Comment 16715
Date: 2012-01-03 02:17:24 +0100
From: hsaddington <<hs.addington>>
Ok. I ran the same queries in DBVisualizer and mclient.
from DBVisualizer around local time, 2012-01-02 17:05
select current_date, extract(year from current_date) as years, extract(month from current_date) as months, extract(day from current_date) as days;
select current_time;
select current_timestamp, extract(year from current_timestamp) as years, extract(month from current_timestamp) as months, extract(day from current_timestamp) as days;
current_date years months days
2012-01-03 2012 1 3
17:05:13
current_timestamp years months days
2012-01-02 17:05:14 2012 1 3
from mclient around local time, 2012-01-02 17:05
select current_date, extract(year from current_date) as years, extract(month from current_date) as months, extract(day from current_date) as days;
select current_time;
select current_timestamp, extract(year from current_timestamp) as years, extract(month from current_timestamp) as months, extract(day from current_timestamp) as days;
+--------------+-------------------+--------------------+------------------+
| current_date | year_current_date | month_current_date | day_current_date |
+==============+===================+====================+==================+
| 2012-01-03 | 2012 | 1 | 3 |
+--------------+-------------------+--------------------+------------------+
+-----------------------+
| current_time |
+=======================+
| 18:08:21.000000-07:00 |
+-----------------------+
+----------------------------------+---------------+----------------+----------------+
| current_timestamp | current_times | current_timest | current_timest |
: : tamp : amp : amp :
+==================================+===============+================+================+
| 2012-01-02 18:10:04.000000-07:00 | 2012 | 1 | 3 |
+----------------------------------+---------------+----------------+----------------+
As you can see above, the just the times are off by 1 hour due to DST, perhaps. But, both clients return incorrect current_date. Extracting the year, month, or day from the current_date or current_timestamp returns the results from GMT, not from local timezone....
Where does the local timezone translation occur? Is it in the JDBC driver or the client? Thanks.
Comment 16716
Date: 2012-01-03 08:29:04 +0100
From: @grobian
JDBC sets the timezone you use on the server. Possibly it does this wrong, though.
Comment 16717
Date: 2012-01-03 10:31:13 +0100
From: @sjoerdmullender
I now understand what goes wrong:
The server works in GMT and needs to convert times to the client's local time zone (as set by e.g. mclient).
When using the query SELECT CURRENT_TIMESTAMP this conversion happens during "export", i.e. when the value is being sent from the server to the client.
With the query SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP), the conversion doesn't happen. The month is extracted before any timezone conversion, and then when exported, there is no need to de timezone conversion.
I expect that you get the wrong result also when extracting the day from the timestamp during the time that your date is different from the date in GMT.
This is a problem that is not so easy to fix.
Comment 16718
Date: 2012-01-04 04:04:58 +0100
From: hsaddington <<hs.addington>>
Yes, you are right. I do get the wrong day when I extract it from the current_timestamp. I think your explanation of where/when the conversion occurs makes sense based on what we are seeing... Too bad, it is not an easy fix, though... That is not what I wanted to hear from the experts! :-)
Comment 17695
Date: 2012-08-24 14:56:07 +0200
From: @sjoerdmullender
Jul2012-SP1 has been released.
Comment 18066
Date: 2012-11-27 14:02:45 +0100
From: @grobian
keep alias in the loop
Comment 18107
Date: 2012-11-27 15:15:14 +0100
From: Holger <<holger.pirk>>
Still occurs in default and Oct2012. Added a test in revision 33598ae3797e
Comment 18203
Date: 2012-11-28 13:45:42 +0100
From: Holger <>
Changeset ef4c8fa0fcb3 made by Holger Pirk holger@cwi.nl in the MonetDB repo, refers to this bug.
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=ef4c8fa0fcb3
Changeset description:
Comment 19072
Date: 2013-08-25 15:49:24 +0200
From: @njnes
functions with timestamp with time zone arguments (not returning timestamps) are now first transformed into the local timezone.
Comment 19074
Date: 2013-08-25 16:23:37 +0200
From: MonetDB Mercurial Repository <>
Changeset 0620e9df2003 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=0620e9df2003
Changeset description:
The text was updated successfully, but these errors were encountered: