2

I want to display a user's entered information on a time series line graph. ChartJS seems to be a great free way to do this. It needs an array of dates, and an array of the values for those dates.

Say if the user asks for data between two dates, and grouped by the day.

In my database I have a table, and each row has a UNIX timestamp. I can easily call up data and group by a given day, e.g.

SELECT some_metric 
FROM tbl_example 
WHERE date BETWEEN [start_ts] AND [end_ts] 
GROUP BY DAY(FROM_UNIXTIME(date))

I also have a function that can make an array of the days between two dates.

Now the problem is that users don't have data on certain days, so I'll need to amend the array that MySQL spits out so that some days have 0.

Apart from a horrid double nested foreach cross-checking dates in either array is there an efficient way to fix gaps in data in MySQL or PHP?

Update: This seems like a possible solution for handling gaps in time series data on the JavaScript side.

Update: I came up with a relatively simple 20-line solution in PHP. It reformats the array of values that MySQL spits out and for each date does an if (isset(...)) check on that array and then adds 0 for that date.

It does the job for days so far but I'll have to delve a little deeper for it to work with weeks and months.

Community
  • 1
  • 1
jonbaldie
  • 378
  • 5
  • 12
  • This could be done with mysql by generating days dynamically for a given range and then by left joining with the table. But the query is somewhat complex. Here is one similar http://stackoverflow.com/questions/23300303/mysql-single-table-select-last-7-days-and-include-empty-rows/23301236#23301236 – Abhik Chakraborty Jun 21 '15 at 08:33
  • That's quite clever and interesting. But as you say quite complex just for filling gaps in data. – jonbaldie Jun 21 '15 at 08:34
  • I said complex since it looks to be, but its not so.. if you look at the query it just generates the dynamic dates and then does left join so it will fill the gap which is not there in the original query. – Abhik Chakraborty Jun 21 '15 at 08:36
  • Just handle the missing results at the application level. – Strawberry Jun 21 '15 at 08:38

1 Answers1

2

I would suggest to use this method to generate values for the dates between start_ts and end_ts:

Get a list of dates between two dates

And then UNION with your results.

It would save you the trouble of iterating the results in php...

If you are using some aggregation (e.g. SUM,COUNT) on some_metric, you can generate a new field called some_metric with the generated dates, with NULL or 0 values, and the aggregation would be valid regardless of the existence of records for those dates.

Community
  • 1
  • 1
Uri Goren
  • 13,386
  • 6
  • 58
  • 110
  • Thanks, the `make_intervals()` method looks useful. – jonbaldie Jun 21 '15 at 08:45
  • A possible solution is to have an existing table of days in the database to `UNION` with. Wouldn't that be more performant than dynamically generating a temp table each time? – jonbaldie Jun 21 '15 at 09:12