Date: 2020-09-30 18:37:30 +0200
From: @skinkie
To: SQL devs <>
Version: -- development
CC: @njnes, @PedroTadim

Last updated: 2020-10-19 11:06:20 +0200

Comment 28131

Date: 2020-09-30 18:37:30 +0200
From: @skinkie

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4173.0 Safari/537.36
Build Identifier:

The documentation at https://www.monetdb.com/Documentation/SQLReference/FunctionsAndOperators/DateTimeFunctionsOperators states that adding an integer to a timestamp or a date should be possible (it was in the past, but I also recall that a .sql file should be added to the database in order to have some temporal functions. But at least the documentation would then be incomplete.

Reproducible: Always

Steps to Reproduce:

1. date '2020-09-28' + (7 * 24 * 60 * 60)

Actual Results:

SELECT: no such binary operator 'sql_add(date,int)'

MonetDB 5 server 11.40.0 (hg id: cf0a10c) (64-bit, 128-bit integers)
This is an unreleased version
Copyright (c) 1993 - July 2008 CWI
Copyright (c) August 2008 - 2020 MonetDB B.V., all rights reserved
Visit https://www.monetdb.org/ for further information
Found 62.8GiB available memory, 12 available cpu cores
Libraries:
libpcre: 8.44 2020-02-12
openssl: OpenSSL 1.1.1h 22 Sep 2020
libxml2: 2.9.10
Compiled by: skinkie@database (x86_64-pc-linux-gnu)
Compilation: /usr/bin/cc -Werror -Wall -Wextra -Werror-implicit-function-declaration -Wpointer-arith -Wundef -Wformat=2 -Wformat-overflow=1 -Wno-format-truncation -Wno-format-nonliteral -Wno-cast-function-type -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wno-missing-field-initializers -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -Wstack-protector -fstack-protector-all -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wduplicated-cond -Wduplicated-branches -Wrestrict -Wnested-externs -Wmissing-noreturn -Wuninitialized -Wno-char-subscripts -Wunreachable-code
Linking : /usr/bin/ld

Comment 28135

Date: 2020-10-01 10:01:32 +0200
From: @PedroTadim

Hello Stefan,

During this Summer, we made a revision of the temporal types for a more strict SQL standard compliance. It is not specified there the addition/subtraction between temporal and numeric types. This is because it's unclear what the numeric value means to a temporal type, so we can't perform it. For this purpose, the interval types should be used instead: date '2020-09-28' + interval '7' day

The Oct2020 branch was not released yet, so we still have time to update the documentation.

Comment 28136

Date: 2020-10-01 10:11:55 +0200
From: @skinkie

I would strongly pledge to keep this functionality as is, or allow sec_interval type to be cast to an integer in some way, which is also not possible. By removing this functionality any arithmetic to timestamps is killed.

postgres= select extract(epoch from now() - now());
date_part

      0

sql>select extract(epoch from now() - now());
syntax error in: "select extract(epoch"

sql>select epoch(now() - now());
SELECT: no such unary operator 'epoch(sec_interval)'

sql>select epoch(now()) - epoch(now());
+------+
| %2 |
+======+
| 0 |
+------+
1 tuple

Comment 28137

Date: 2020-10-01 10:15:14 +0200
From: @skinkie

To me more explicit:

date '2020-09-28' + interval '7' day

A user wants:

date '2020-09-28' + interval column_or_expression day

create table test(i integer, d date);
insert into test values (1, date '2020-09-28');
select d + interval i day from test;
syntax error in: "select d + interval i day"

select d + interval '1' day from test;
+------------+
| %1 |
+============+
| 2020-09-29 |
+------------+
1 tuple

Comment 28139

Date: 2020-10-01 10:53:36 +0200
From: @PedroTadim

It's not about convenience, it's about what makes sense. Nothing is killed, it's just made correct.

We don't have the functionality to extract the 'epoch' from a timestamp, or interval. I just just checked on the standard, and it's not there, so PostgreSQL has it as an extension.

Intervals and integers are not compatible, so casting is not possible.

On your example, you should do:

create table test(i interval day, d date);
insert into test values (interval '1' day, date '2020-09-28');
select d + i from test;

I see PostgreSQL allows addition between dates and integers as an extension, but it disallows it with time and timestamps.

Comment 28140

Date: 2020-10-01 11:12:15 +0200
From: @skinkie

(In reply to Pedro Ferreira from comment 4)

It's not about convenience, it's about what makes sense. Nothing is killed,
it's just made correct.

With respect to the SQL standard it might be "more correct", but it still lacks an previously acknowledged missing features. I am happy to use a user defined function, because this functionality is obviously available at the MAL layer.

We don't have the functionality to extract the 'epoch' from a timestamp, or
interval. I just just checked on the standard, and it's not there, so
PostgreSQL has it as an extension.

Intervals and integers are not compatible, so casting is not possible.

On your example, you should do:

create table test(i interval day, d date);
insert into test values (interval '1' day, date '2020-09-28');
select d + i from test;

I see PostgreSQL allows addition between dates and integers as an extension,
but it disallows it with time and timestamps.

The above would also fail. I receive a timestamp, and I receive an offset. This would be loaded into the database from a CSV file. I am not able to go from an integer to an interval because the inverse of EXTRACT is not defined. I guess this is the primary problem. But the other way around I can also make an argument I am not able to use any integer functions such as ROUND on an interval while this makes sense too.

Comment 28141

Date: 2020-10-01 11:39:16 +0200
From: @skinkie

I guess the CSV problem could be handled by multiplication.

select d + i * interval '1' day from test;

But I am not too lucky:

sql>select interval '1' day / interval '1' day from test;
SELECT: no such binary operator 'sql_div(sec_interval,day_interval)'

Comment 28144

Date: 2020-10-02 11:20:53 +0200
From: @PedroTadim

Dividing intervals is not possible. However an user can do: integer * interval to obtain an interval. More specifically: number * interval '1' second/month is an easy conversion from numbers into intervals. At the same time: extract(second from second interval) and extract(month from interval month) are easy conversions from intervals to numbers.

Comment 28147

Date: 2020-10-02 13:09:00 +0200
From: @njnes

sql>select interval '1' day / 5 from test;
+-----------+
| %1 |
+===========+
| 17280.000 |
+-----------+
1 tuple

ie. we support division, but only by integers not by interval.

I'm marking this as a wontfix. We have stricter semantics, but I think with the shown solutions it still supports your requirements.

Comment 28148

Date: 2020-10-02 14:43:33 +0200
From: @skinkie

Niels, Pedro, the suggestion is basically not working, it would only work up to 59 seconds. So in order to work with that extracting seconds + extracting minutes * 60 + extracting hours * 3600 + etc.

select extract(second from interval '3600' second);
+------+
| %2 |
+======+
| 0 |
+------+
1 tuple

select extract(hour from interval '3600' second);
+------+
| %2 |
+======+
| 1 |
+------+
1 tuple

So no, they are not easy conversions. And this behavior is like PostgreSQL so no complaints at this point.

postgres= select extract(epoch from interval '3600' second);
date_part

   3600

(1 row)

So I guess the featurerequest would be: could extract(epoch be implemented?

Comment 28149

Date: 2020-10-02 18:22:04 +0200
From: @PedroTadim

We can add the epoch function easily. However we can't add it for month intervals, because that cannot be computed. This is because a month has not a consistent number of days to compute an epoch number.

Comment 28151

Date: 2020-10-05 10:07:41 +0200
From: MonetDB Mercurial Repository <>

Changeset 22f0b6b230c9 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=22f0b6b230c9

Changeset description:

Added 'epoch' extract function to datetime related types. This allows to convert an interval value into an integer as a regular cast is not possible as mentioned in bug #6979