Date: 2011-02-24 11:57:40 +0100
From: Milena Ivanova <<M.Ivanova>>
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: duc, @mlkersten, @njnes

Depends on: #2809
Last updated: 2020-05-06 10:24:43 +0200

Comment 15501

Date: 2011-02-24 11:57:40 +0100
From: Milena Ivanova <<M.Ivanova>>

User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-GB; rv:1.9.2.13) Gecko/20110103 Fedora/3.6.13-1.fc14 Firefox/3.6.13
Build Identifier:

select localtime();
uses internally MTIMEcurrent_timestamp that returns the GMT time.

Reproducible: Always

Comment 15744

Date: 2011-04-28 10:10:42 +0200
From: @grobian

interestingly current_time() is wrong too, although it differs between mclient and JDBC (the latter sets the timezone)

Even though JDBC does
SET TIME ZONE INTERVAL '+02:00' HOUR TO MINUTE;
the server returns a correction for just 1 hour, but not when used directly in mclient:

sql>select localtime();
+-----------------+
| localtime |
+=================+
| 08:09:09.000000 |
+-----------------+
1 tuple (0.579ms)
sql>select current_time();
+-----------------------+
| current_time |
+=======================+
| 08:09:15.000000+00:00 |
+-----------------------+
1 tuple (0.541ms)
sql>SET TIME ZONE INTERVAL '+02:00' HOUR TO MINUTE;
sql>select current_time();
+-----------------------+
| current_time |
+=======================+
| 10:09:27.000000+02:00 |
+-----------------------+
1 tuple (0.532ms)
sql>

The latter seems to be a bug in JDBC?

Comment 15794

Date: 2011-05-11 15:40:27 +0200
From: @grobian

Changeset e04ced483138 made by Fabian Groffen fabian@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=e04ced483138

Changeset description:

JDBC: rework ResultSet timestamp/time parsing

bug #2781 indicated a mismatch between what the server returned, and
what Java would print by default for time values.

The big issue here is working with timezones in the correct way.  We now
rely more on the default Java parsers to get this right.  For fields
with a timezone stored on the server, we can rely on the server doing
the work, but for fields without, we have to do zone calculations,
possibly correcting to what timezone the caller thinks the server is
using.

Comment 16319

Date: 2011-09-22 11:51:11 +0200
From: @sjoerdmullender

Created attachment 76
fix for the problem

The attached fixes the problem. It makes that times and timestamps are reported in the client's local timezone always. The difference between having and not having WITH TIME ZONE is that the client's timezone is reported if WITH TIME ZONE is used.

However, this patch is not enough. The input should also be handled. I would expect that
select cast(timestamp '2011-09-22 11:29:45' as timestamp with time zone);
would then result in
2011-09-22 11:29:45+02:00
(if my timezone is +02:00).
This patch doesn't do that and so is not (yet) acceptable.

Attached file: tz.patch (text/plain, 1024 bytes)
Description: fix for the problem

Comment 17664

Date: 2012-08-24 14:54:30 +0200
From: @sjoerdmullender

Jul2012-SP1 has been released.

Comment 18139

Date: 2012-11-27 16:04:20 +0100
From: @mlkersten

sql.mx is part of SQL, redirect bug

Comment 18196

Date: 2012-11-28 13:45:25 +0100
From: duc <>

Changeset b39f2769929d made by Minh-Duc Pham duc@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=b39f2769929d

Changeset description:

Test script for Bug #2781 (Replace from the wrong number, i.e., 2791)

Comment 19071

Date: 2013-08-25 14:53:16 +0200
From: @njnes

implemented new localtime/stamp functions and made sure mclient computes time zone, without the daylight saving time correction (ie plain diff in hours).

Comment 19075

Date: 2013-08-25 16:23:38 +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

Comment 19135

Date: 2013-09-06 00:26:59 +0200
From: MonetDB Mercurial Repository <>

Changeset c8ead4fbb1f6 made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=c8ead4fbb1f6

Changeset description:

(trying to) fix test currenttime.Bug-2781 on Windows:

properly export SQLcurrent_daytime() & SQLcurrent_timestamp()
(introduced by changeset 0620e9df2003)

Comment 27692

Date: 2020-05-06 10:24:43 +0200
From: MonetDB Mercurial Repository <>

Changeset fb1d7bb740ac made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=fb1d7bb740ac

Changeset description:

Use timestamp instead of time at bug #2781 test, so it can't never overflow if the test is performed at around midnight