1

I'm thinking about what would be the best way to do what I'm trying to accomplish. As the title says, I have a table with missing dates in mysql, like this:

+------------+
| 2015-09-01 |
| 2015-09-03 |
| 2015-09-05 |
| 2015-09-06 |
| 2015-09-07 |
+------------+

I want to select them to assign them another column with a null value, so if I also wanted to select the missing dates (days 2 and 4 in my case), which are my options? I thought about making a "calendar" table and select both with a left join and using not in, but that means I'd have to fill it with a TON of dates (in case someone for some reason wants to select data from the year 2500 or 1800, and I don't want that).

I don't like the option of using a calendar table because of performance stuff, but it also brings another question: if I still used a calendar table, and restricted people from selecting dates in the past or in the future (thus freeing me from having to put hundreds of years of margin in case some crazy soul decided to select stuff from there), how could I make the table fill itself so it has a date for the current date without me having to insert the current date manually?

Can a trigger/function run itself everyday and insert the current date there?

Edit: My intention is using the table for chartjs, so it can have "holes" where there's missing data (with null values).

Newwt
  • 491
  • 1
  • 5
  • 22
  • http://stackoverflow.com/a/41888886/2286537 check this – krishn Patel Apr 25 '17 at 08:24
  • 1
    A better solution would be to do this in the program that calls the SQL, IOW php or java or whatever. Not exactly something best done in SQL. – Sloan Thrasher Apr 25 '17 at 08:25
  • you can also use this query as sub query and you can get what you want – krishn Patel Apr 25 '17 at 08:25
  • @SloanThrasher How can that be done with php? Might be a dumb question, but I just can't think about a way how to do it (the server returns me a json string with the selected dates and values) :/ – Newwt Apr 25 '17 at 08:27
  • 31 and 8 are also missing – Strawberry Apr 25 '17 at 08:31
  • While I don't necessarily advocate a calendar table in this instance, a table of all plausible dates is a depressingly small affair. – Strawberry Apr 25 '17 at 08:33
  • @knowledge.... That looks like a good solution, but still brings the problem of "the future". Will I have to run this every year? Or fill the table with a few years in advance? – Newwt Apr 25 '17 at 08:35
  • @Strawberry What do you mean by 31 and 8? – Newwt Apr 25 '17 at 08:36
  • you just need to pass date only in range – krishn Patel Apr 25 '17 at 08:41
  • @newwt what do you want to do with a missing date? you want to fill it up? – Vijunav Vastivch Apr 25 '17 at 08:56
  • @reds Yes! I want the date to be like "2015-08-02 | null" if the date is missing and "2015-08-03 | 3" if it's not, so I can use them to make a chart with chartjs. – Newwt Apr 25 '17 at 08:58
  • 1
    ok thanks.. just i'll try some solution if i can.. – Vijunav Vastivch Apr 25 '17 at 09:00
  • what do you mean by this? "2015-08-03 | 3" where's that 3 came from? you need the last value of the day? – Vijunav Vastivch Apr 25 '17 at 09:06
  • @reds Sorry for the confusion. The "3" is just a value from another column (call it score, temperature...). When I select a missing day it will automatically set to null, which is exactly what I want. – Newwt Apr 25 '17 at 09:09
  • so the 3 you mean is not included for your question? all you need from us is the missing date am i right? ill post some answers then try to evaluate. – Vijunav Vastivch Apr 25 '17 at 09:12
  • @reds Exactly. I can get the other values easily and mysql sets the missing ones to null by itself, so that is not a problem. – Newwt Apr 25 '17 at 09:16
  • I mean "How do you know that '2015-09-08' isn't missing" Likewise '2015-08-31' ? – Strawberry Apr 25 '17 at 10:24
  • @Strawberry '2015-08-31' doesn't exist because '2015-09-01' is the first/minimum date in the table. '2015-09-08' may or may not exist (there's no need to know if it does). The data in the table goes until the current date, missing a few random ones in the process. I don't care about knowing if they do or do not exist, I want mysql to return the missing ones, and I will add another colum with a value for the existing ones and `null` for the "missing" dates with a left join (for example). – Newwt Apr 25 '17 at 10:39
  • To answer your question SloanThrasher, you have some choices. One option is to adapt the code that builds the json to include missing results. Another option is to build the logic of missing results into the code that parses the 'incomplete' json. – Strawberry Apr 25 '17 at 12:05
  • @Strawberry, I didn't ask a question. – Sloan Thrasher Apr 25 '17 at 13:05
  • 1
    @SloanThrasher I think he meant *my* question to you – Newwt Apr 25 '17 at 13:07

1 Answers1

1

Check this:

select this_date,given_dates from
(SELECT ADDDATE('2015-09-01', INTERVAL @i:=@i+1 DAY) AS this_date
        FROM (
        SELECT a.a
        FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
        ) a
        JOIN (SELECT @i := -1) r1
        WHERE 
        @i < DATEDIFF('2018-01-30', '2015-09-01')) as a

left JOIN

(select given_dates from
 (select '2015-09-01' as given_dates union all 
select  '2015-09-03' union all 
select  '2015-09-05' union all 
select  '2015-09-06' union all 
select  '2015-09-07') as a) as b
on a.this_date = b.given_dates

sample Result:

2015-09-01  2015-09-01  
2015-09-02      
2015-09-03  2015-09-03  
2015-09-04      
2015-09-05  2015-09-05  
2015-09-06  2015-09-06  
2015-09-07  2015-09-07  
2015-09-08      
2015-09-09      
2015-09-10      
2015-09-11

You can get easily the null value of it.

Vijunav Vastivch
  • 4,153
  • 1
  • 16
  • 30