Temporal extraction glitches

[monetdb-team]

Date: 2014-01-29 10:53:46 +0100
From: @mlkersten
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: @njnes

Last updated: 2014-02-20 15:03:02 +0100

Comment 19509

Date: 2014-01-29 10:53:46 +0100
From: @mlkersten

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:26.0) Gecko/20100101 Firefox/26.0
Build Identifier:

The extraction of hour and month from a timestamp seems incorrect.

Reproducible: Always

Steps to Reproduce:

select now(), extract(second from now());
select now(), extract(minute from now());
select now(), extract(hour from now());
select now(), extract(month from now());
select now(), extract(year from now());

Actual Results:

sql>select now(), extract(second from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:48:26.000000+01:00 | 26.000 |
+----------------------------------+-------------------+
1 tuple (0.672ms)
sql>select now(), extract(minute from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:48:48.000000+01:00 | 48 |
+----------------------------------+-------------------+
1 tuple (0.897ms)
sql>select now(), extract(hour from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:48:58.000000+01:00 | 1 |
+----------------------------------+-------------------+
1 tuple (0.797ms)
sql>select now(), extract(month from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:49:19.000000+01:00 | 3 |
+----------------------------------+-------------------+
1 tuple (1.017ms)
sql>select now(), extract(year from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:49:29.000000+01:00 | 2014 |
+----------------------------------+-------------------+
1 tuple (0.832ms)

Expected Results:

sql>select now(), extract(second from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:48:26.000000+01:00 | 26.000 |
+----------------------------------+-------------------+
1 tuple (0.672ms)
sql>select now(), extract(minute from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:48:48.000000+01:00 | 48 |
+----------------------------------+-------------------+
1 tuple (0.897ms)
sql>select now(), extract(hour from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:48:58.000000+01:00 | 10 |
+----------------------------------+-------------------+
1 tuple (0.797ms)
sql>select now(), extract(month from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:49:19.000000+01:00 | 1 |
+----------------------------------+-------------------+
1 tuple (1.017ms)
sql>select now(), extract(year from now());
+----------------------------------+-------------------+
| L1 | current_timestamp |
+==================================+===================+
| 2014-01-29 10:49:29.000000+01:00 | 2014 |
+----------------------------------+-------------------+
1 tuple (0.832ms)

Comment 19510

Date: 2014-01-29 13:10:23 +0100
From: @mlkersten

sql>explain select now(), extract(day from now());
+----------------------------------------------------------------------------+
| mal |
+============================================================================+
| function user.s3_1{autoCommit=true}():void; |
| X_2 := mtime.current_timestamp(); |
| X_3 := mtime.current_timestamp(); |
| X_5 := mtime.timestamp_add_msec_interval(X_3,3600000000:lng); |
| X_6 := mtime.day(X_5); |
| X_7 := sql.resultSet(2,1,X_2); |
| sql.rsColumn(X_7,".L1","L1","timestamptz",7,0,X_2); |
| sql.rsColumn(X_7,".L1","current_timestamp","int",32,0,X_6); |
| X_19 := io.stdout(); |
| sql.exportResult(X_19,X_7); |
| end s3_1; |
| querylog.define("select now(), extract(day from now());","default_pipe") |
| optimizer.mitosis() |
| optimizer.dataflow() |
+----------------------------------------------------------------------------+
sql>declare ts timestamp;
operation successful (0.657ms)
sql>set ts = now();
operation successful (0.645ms)
sql>explain select now(), extract(day from ts);
+---------------------------------------------------------------------------------+
| mal |
+=================================================================================+
| function user.s7_1{autoCommit=true}():void; |
| X_2 := sql.mvc(); |
| X_3 := mtime.current_timestamp(); |
| X_5:timestamp := sql.getVariable(X_2,"ts"); |
| X_6 := mtime.day(X_5); |
| X_7 := sql.resultSet(2,1,X_3); |
| sql.rsColumn(X_7,".L1","L1","timestamptz",7,0,X_3); |
| sql.rsColumn(X_7,".L1","day_single_value","int",32,0,X_6); |
| X_20 := io.stdout(); |
| sql.exportResult(X_20,X_7); |
| end s7_1; |
| querylog.define("explain select now(), extract(day from ts);","default_pipe") |
| optimizer.mitosis() |
| optimizer.dataflow() |
+---------------------------------------------------------------------------------+
14 tuples (1.308ms)

Comment 19520

Date: 2014-02-03 15:57:57 +0100
From: @sjoerdmullender

(In reply to comment 1)

sql>explain select now(), extract(day from now());
[...]
| X_5 := mtime.timestamp_add_msec_interval(X_3,3600000000:lng);

I think the value 3600000000 is a factor 1000 too high. Where does it come from?

Comment 19533

Date: 2014-02-05 09:33:28 +0100
From: @njnes

that (incorrectly) comes from the second_interval. I have a patch.

Comment 19534

Date: 2014-02-05 09:44:39 +0100
From: @njnes

extended the test ./BugTracker-2011/Tests/extract_seconds.Bug-2793.sql

Comment 19543

Date: 2014-02-05 13:00:13 +0100
From: MonetDB Mercurial Repository <>

Changeset be0051b026da 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=be0051b026da

Changeset description:

approved output for bug #3427 (also use auto commit mode)
fixed bug #3430, ie check if local temporary tables are stored in the 'tmp' schema
fixed bug #3428, don't overwrite the statement number, but use a temporary structure to store the
NotNil select statements. Solves the problem that the sum(a), sub(a*b) uses the a after nil
removal in the a*b.

fixed bug #3425, correctly add the timezone with the proper type (ie second interval (ie with scale 3))

Comment 19619

Date: 2014-02-20 15:03:02 +0100
From: @sjoerdmullender

Jan2014 has been released.