How to MySQL query between two dates when the result holds only the start date from which the attribute is valid?

Question

I've got a simple example table with the following structure:

PK_ID | PK_VALID_FROM_DATE | VALUE
------+--------------------+------
1     | "1980-01-01"       | 42
1     | "1999-06-06"       | 103
2     | "2011-12-12"       | 92
1     | "2014-04-04"       | 512

The table is created by adding a row, on each date the value changes.

Or is there any better way to create a history of the date when a value was changed and look it up?

Now I want to query to find out which VALUE was valid on a particular date for a particular PK_ID.

Usage Example:

What was the VALUE for PK_ID=1 on 2010-05-22?

It's easy to see that from 1999-06-06 to 2014-04-03 the VALUE=103 for PK_ID=1, but how to query for 2010-05-22 ?

so you want to check if your PK_VALID_FROM_DATE -1DAY is between what DATE? I don't understand your question — klapvee, Jul 30 '15 at 13:47
Isn't this just a subquery? The subquery to find the date; and use that to find the most previous (earlier) date using <= LIMIT 1. If this is what you want, please let me know so I can create an official answer. — user3741598, Jul 30 '15 at 13:50
Sorry, I edited my question for more clarity - at least I hope. =) — rikaidekinai, Jul 30 '15 at 14:03

score 2 · Accepted Answer · answered Jul 30 '15 at 14:14

2

if i understood correctly. First find the maximal date before needed one. Then take value with that date

drop table if exists t1; 
create table t1 (PK_ID int, PK_VALID_FROM_DATE date, VALUE int);
insert into t1 values
(1,"1980-01-01",  42),
(1, "1999-06-06", 103),
(2, "2011-12-12", 92),
(1, "2014-04-04", 512);

select value 
  from t1 
  where PK_ID=1
    and PK_VALID_FROM_DATE = 
        (select max(PK_VALID_FROM_DATE) 
           from t1 
             where PK_ID=1
               and PK_VALID_FROM_DATE<= DATE("1999-06-06"))

answered Jul 30 '15 at 14:14

splash58

26,043
3
22
34

This makes plenty of sense if OP doesn't actually need to display the to-from range. – Tobia Tesan Jul 30 '15 at 14:19
i dont see such wishing in the question :) – splash58 Jul 30 '15 at 14:20
A couple of revisions ago I could have sworn it was there :) – Tobia Tesan Jul 30 '15 at 14:21
I don't need to display the from-to range, it was just something stuck in the back of my head to query if my date is between anything =) Now I got the issue to decide on the best answer.. the one explaining all in detail or the short'n'neat solution lol. – rikaidekinai Jul 30 '15 at 14:54
@rikaidekinai no need: splash58's is the *correct* solution for what you need. I'm tempted to remove mine, in fact. – Tobia Tesan Jul 30 '15 at 14:56
@TobiaTesan: No don't delete it i think it has an educational value, if indeed one needs the date-ranges. I also liked your edited one line approach =) sorry for wasting your time with an unclear question. – rikaidekinai Jul 30 '15 at 15:09
1

@splash58: One question, isn't the outer `where PK_ID=1` redundant as the inner select already only returns rows with `PK_ID=1` ? – rikaidekinai Jul 30 '15 at 15:21
1

@rikaidekinai if we remove inner `where` we can receive as max date of PK_ID=2 and outer where not find such date – splash58 Jul 30 '15 at 15:28
1

If we remove outer `where` and dates for both id will be the same we select both id – splash58 Jul 30 '15 at 15:29

splash58 · Answer 2 · 2015-07-30T20:10:29.163

I write it here only to don't loose. If @TobiaTesan would be right and we need to receive intervals when value is valid, it can be done by such query

drop table if exists t1; 
create table t1 (PK_VALID_FROM_DATE date, VALUE int);
insert into t1 values
("1980-01-01",  42),
("1999-06-06", 103),
("2011-12-12", 92),
("2014-04-04", 512);

select PK_VALID_FROM_DATE as 'from', 
       ifnull(((select min(PK_VALID_FROM_DATE) 
                   from t1 
                   where f1.PK_VALID_FROM_DATE < PK_VALID_FROM_DATE) 
                 - interval 1 day), 
               curdate()) as 'to', 
       value 
  from t1 as f1

result

from         to            value
1980-01-01   1999-06-05    42
1999-06-06   2011-12-11    103
2011-12-12   2014-04-03    92
2014-04-04   2015-07-30    512
             (today)

Tobia Tesan · Answer 3 · 2015-07-30T20:33:20.020

DISCLAIMER: This solution does no longer apply to the edited question.

I'm leaving it here for future reference, just in case.

1 - Start by JOINing the table with itself imposing a condition such that you only have dates greater than the left hand side on the right hand side:

SELECT * FROM foo AS f1 
JOIN foo AS f2 
WHERE f1.PK_VALID_FROM_DATE < f2.PK_VALID_FROM_DATE;
AND f1.PK_ID = f2.PK_ID;

2 - Using aggregation, we then take only the minimum date on the RH side:

SELECT 
  f1.PK_ID, f1.PK_VALID_FROM_DATE, f1.VALUE, 
  MIN(f2.PK_VALID_FROM_DATE) AS PK_VALID_TO_DATE 
FROM foo AS f1 
JOIN foo AS f2 
WHERE 
  f1.PK_VALID_FROM_DATE < f2.PK_VALID_FROM_DATE 
  AND f1.PK_ID = f2.PK_ID 
GROUP BY f1.PK_ID, f1.PK_VALID_FROM_DATE, f1.VALUE;

3 - Now DATE_SUB() comes to our rescue:

SELECT 
  f1.PK_ID, 
  f1.PK_VALID_FROM_DATE,
  f1.VALUE,
  MIN(DATE_SUB(f2.PK_VALID_FROM_DATE, INTERVAL 1 DAY)) AS PK_VALID_TO_DATE 
FROM foo AS f1 
JOIN foo AS f2 
WHERE 
  f1.PK_VALID_FROM_DATE < f2.PK_VALID_FROM_DATE 
  AND f1.PK_ID = f2.PK_ID 
GROUP BY f1.PK_ID, f1.PK_VALID_FROM_DATE, f1.VALUE;

Resulting output:

+-------+---------------------+-------+---------------------+
| PK_ID | PK_VALID_FROM_DATE  | VALUE | PK_VALID_TO_DATE    |
+-------+---------------------+-------+---------------------+
|     1 | 1980-01-01 00:00:00 |    42 | 1999-06-05 00:00:00 |
|     1 | 1999-06-06 00:00:00 |   103 | 2014-04-03 00:00:00 |
+-------+---------------------+-------+---------------------+

Thanks for this detailed answer, I am sure this is helpful for someone who really needs to display the range, which I don't really need for my query, but who knows, requirements might change. — rikaidekinai, Jul 30 '15 at 14:55
@TobiaTesan i newb in mysql so may be wrong. But so it seems a little easy. http://sqlfiddle.com/#!9/ff57b/1 — splash58, Jul 30 '15 at 16:31
@splash58 excellent, thank you. Personally, I tend to prefer self joins to subqueries out of habit (the subqueries vs. self joins thing popped up [here](http://stackoverflow.com/questions/31648489/how-to-efficiently-select-a-columns-field-based-on-another-row-with-the-same-i/31648626#31648626) as well), but your solution is equally valid and probably slightly more readable. — Tobia Tesan, Jul 30 '15 at 17:07

How to MySQL query between two dates when the result holds only the start date from which the attribute is valid?

3 Answers3