CODEWITHSUNDEEP
mysqlsqldate
jftremblay
jftremblay

Reputation: 94

In MYSQL, how to filter a date interval by using 3 separated fields for the date?

Let's say I have this table:

create table test3( 
id INT NOT NULL AUTO_INCREMENT PRIMARY_KEY,
an int(4),
mois int(2),
jour int(2),
data varchar(255)
);

mysql> desc test3;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      |      | PRI | NULL    | auto_increment |
| an    | int(4)       | YES  |     | NULL    |                |
| mois  | int(2)       | YES  |     | NULL    |                |
| jour  | int(2)       | YES  |     | NULL    |                |
| data  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

I need to fetch everything between 2013-12-01 and 2014-01-12.

I tried the following query with a COUNT but in fact, because the WHERE needs a month greater or equal to 12 and smaller or equal to 01, it returns nothing.

select count(id) 
from rapport_cc_agents 
where (an>=2013 and mois>=12 and jour>=01) 
and (an<=2014 and mois<=01 and jour<=12);

It's probably really simple but I'm blocked on this problem.

Thank you!

[EDIT] The case is that I cannot change the storing format and use only one field because of the impact with other systems.

Upvotes: 0

Views: 126

Answers (3)

Stuart Caie
Stuart Caie

Reputation: 2913

Ideally, don't store the data in that format. If you have the power to change it to a DATE, then do so! If not, then raise the concern to whomever stores the data that it is difficult, slow and error-prone to do data queries when the data is stored in an inappropriate manner.

Also point out to them that if they want the components of a date, then if the data is stored as a DATE the components can easily be EXTRACTed. But storing the date components separately is bad, because you can't then enforce it's a real date -- for example, an=-1024, mois=99, jour=0 would be considered valid in the given schema. This is just wrong. Even if the fields were limited to the typical valid numbers (1-12, 1-31), it's still possible to construct an invalid date like an=2001, mois=2, jour=30. The only way you can be sure you have a valid date is to use the DATE type for it. What use is data in a database if it's possible to add incorrect data?

As an example of migrating the data to a sane format:

CREATE TABLE test3_real (
    id INT NOT NULL auto_increment,
    date DATE NOT NULL,
    data VARCHAR(255),
    PRIMARY KEY (id)
);

INSERT INTO test3_real (id, date, data)
SELECT id, STR_TO_DATE(CONCAT(an,'-',mois,'-',jour), '%Y-%m-%d'), data FROM test3;

DROP TABLE test3;

CREATE VIEW test3
AS
SELECT id,
       EXTRACT(YEAR FROM date) an,
       EXTRACT(MONTH FROM date) mois,
       EXTRACT(DAY FROM date) jour,
       data
FROM test3;

As for your query, the date is a hierarchy of numbers. If the big number is out of range, it doesn't matter what the smaller numbers are:

WHERE (an > 2013 OR (an = 2013 AND (mois > 12 OR (mois = 12 AND jour >= 1))))
  AND (an < 2014 OR (an = 2014 AND (mois < 1  OR (mois = 1  AND jour <= 12))))

Upvotes: 4

Evan Volgas
Evan Volgas

Reputation: 2921

Echoing what Stuart already said, I would also not recommend storing the data in that format. You're likely to encounter holes in your logic with custom date ranges, leap years, and a whole host of otherwise very simple questions (how would you write a query for the previous 7 days? Or the previous 45?).

Now, that being said, it's very often the case that a DATES table with Date as a primary key and things like "Month," "Year," "Quarter," etc can save you some trouble.... this is especially true for things like "Week of the year" or "first Friday of the month" style queries. Storing the data that you have might make sense, but not in the table you are storing it in.

To the original question, though,

where (an>=2013 and mois>=12 and jour>=01) and (an<=2014 and mois<=01 and jour<=12);

is wrong. You're asking for month >= 12 and <= 1.... which won't happen. You actually want a few OR's in here, eg,

WHERE (year = 2013 and month >= 12 and day >= 1) OR (year = 2014 <= 2014 and month <=1 and day <= 12)

If you you stored a date instead of these three columns, by the way, your query would like

WHERE date BETWEEN x AND y

which is a lot more straight forward and less prone to errors in logic... as you've basically just encountered for yourself.

Upvotes: 0

Caleth
Caleth

Reputation: 63392

If you must still have the three parts of the date seperate, you can use the following:

CAST( CAST( an as varchar(4) ) + '-' + CAST( mois as varchar(2) ) + '-' + CAST( jour as varchar(2) ) as DATE )

I would suggest adding this as part of a view on the table, e.g.

CREATE VIEW test3withDate AS
SELECT id, an, mois, jour, data, CAST( CAST( an as varchar(4) ) + '-' + CAST( mois as varchar(2) ) + '-' + CAST( jour as varchar(2) ) as DATE ) as mydate FROM test3

so that you can use it anywhere else you need a date

Upvotes: 0

Related Questions