Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

client timezone information needs to be handled also for modifications to timestamp values #2962

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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:

* added test for bug #2962

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:

fixed bug #3232, use dayofweek, starting at Monday
fixed bug #2962, convert timestamp with time zone to local time zone, when used as input to the extract functions.
fixed bug #2781, use correct time zone in mclient
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant