Calculation Date function using interval and year

[monetdb-team]

Date: 2013-01-03 19:00:31 +0100
From: Glauber <>
To: SQL devs <>
Version: 11.13.9 (Oct2012-SP3)

Last updated: 2013-02-19 13:18:00 +0100

Comment 18316

Date: 2013-01-03 19:00:31 +0100
From: Glauber <>

User-Agent: Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11
Build Identifier:

I need to get the same date period for the previous year based on the current year. Example: 01.01.2013 to 02.01.2013 - last year Period: 2012-01-01 to 2012-01-02, then used the same sql I use in Postgres.

select date from dim_tempo
where date between cast ('2013-01-01 'as timestamp) + INTERVAL' -1 'year
and cast ('2013-01-02 'as timestamp) + INTERVAL' -1 'year

• -------------------- +
  | Date |
• ==================== +
  | 2012-01-01 00:00:00 |
  | 2012-01-02 00:00:00 |
• -------------------- +

MonetDB - Result Wrong

select date from dim_tempo
where date between cast ('2013-01-01 'as date) + interval' -1 'year
and cast ('2013-01-02 'as date) + interval' -1 'year;

• ------------ +
  | Date |
• ============ +
  | 2012-01-02 |
  | 2012-01-03 |
• ------------ +

Reproducible: Always

Steps to Reproduce:

DDL and inserts Postgres:
CREATE TABLE dim_tempo
(
id_tempo double precision,
data timestamp without time zone
);

insert into dim_tempo values (1,'2012-01-01'), (2,'2012-01-02'), (3,'2012-01-03'), (4,'2012-01-04'), (5,'2013-01-01'), (6,'2013-01-02'), (7,'2013-01-03'), (8,'2013-01-04');

DDL and inserts MonetDB:
CREATE TABLE dim_tempo
(
id_tempo integer,
data date
);

insert into dim_tempo values (1,'2012-01-01'), (2,'2012-01-02'), (3,'2012-01-03'), (4,'2012-01-04'), (5,'2013-01-01'), (6,'2013-01-02'), (7,'2013-01-03'), (8,'2013-01-04');

1. run this query on another DBMS example: postgres

select date from dim_tempo
where date between cast ('2013-01-01 'as timestamp) + INTERVAL' -1 'year
and cast ('2013-01-02 'as timestamp) + INTERVAL' -1 'year

2. run this query in Monetdb, I used to run mclient
   select date from dim_tempo
   where date between cast ('2013-01-01 'as date) + interval' -1 'year
   and cast ('2013-01-02 'as date) + interval' -1 'year;

Actual Results:

• ------------ +
  | Date |
• ============ +
  | 2012-01-02 |
  | 2012-01-03 |
• ------------ +

Expected Results:

• ------------ +
  | Date |
• ============ +
  | 2012-01-01 |
  | 2012-01-02 |
• ------------ +

I am Brazilian!

Comment 18442

Date: 2013-01-29 10:22:37 +0100
From: @sjoerdmullender

Changeset fdbfe3a3f1bd made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Add test for bug #3215.

Comment 18443

Date: 2013-01-29 10:37:31 +0100
From: @sjoerdmullender

Changeset 27f146dcaa6c made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Add or subtract number of days in correct month.
We need to adjust the year when wrapping months since some years are
leap years.
This fixes bug #3215.

Comment 18444

Date: 2013-01-29 10:40:31 +0100
From: @sjoerdmullender

Should appear in the next release.

Comment 18516

Date: 2013-02-19 13:18:00 +0100
From: @sjoerdmullender

Feb2013 has been released.