EXTRACT(SECOND FROM timestamp) always returns 0 integer seconds part.

[monetdb-team]

Date: 2011-03-25 18:20:21 +0100
From: spamme.gs
To: SQL devs <>
Version: 11.1.1 (Mar2011) [obsolete]
CC: @njnes

Last updated: 2011-04-28 16:25:34 +0200

Comment 15612

Date: 2011-03-25 18:20:21 +0100
From: spamme.gs

User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US) AppleWebKit/534.16 (KHTML, like Gecko) Chrome/10.0.648.151 Safari/534.16
Build Identifier:

EXTRACT(SECOND FROM timestamp) appears to be incorrectly implemented. It always returns 0 for the integer part. I have traced this behaviour to the way this SQL functionality was implemented.

Reproducible: Always

Steps to Reproduce:

Here is a sample session:

Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2010-SP1)
Database: MonetDB v5.22.3, 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on

sql> select extract(second from timestamp '2011-3-10 9:30:42.246');

Actual Results:

+---------------------+
| second_single_value |
+=====================+
| 0.246 |
+---------------------+

Expected Results:

+---------------------+
| second_single_value |
+=====================+
| 42.246 |
+---------------------+

We can find the source of this behaviour using EXPLAIN :

sql> explain select extract(second from timestamp '2011-3-10 9:30:42.246');

function user.s2_1{autoCommit=true}():void;
sql.mvc();
_4 := mtime.milliseconds(2011-03-10 09:30:42.246:timestamp);
sql.exportValue(1,".","second_single_value","decimal",9,3,8,_4,"");
end s2_1;

mtime.milliseconds is declared (modules/atoms/mtime.mal) as follows
413 command milliseconds(d:daytime) :int
414 address MTIMEdaytime_extract_milliseconds

and MTIMEdaytime_extract_milliseconds only returns the millisecond part.

Comment 15675

Date: 2011-03-28 17:36:27 +0200
From: @sjoerdmullender

The Mar2011 version has been released.

Comment 15699

Date: 2011-03-30 11:58:25 +0200
From: @njnes

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

Changeset description:

fixed bug #2793, ie implemented extract( seconds) including fractional milli
seconds.

Comment 15700

Date: 2011-03-30 11:58:30 +0200
From: @njnes

fixed by implementing the required seconds with fractional milliseconds.
Test added extract_seconds.Bug-2793.sql

Comment 15746

Date: 2011-04-28 16:25:34 +0200
From: @sjoerdmullender

The Apr2011 release has been uploaded.