1

I have a dataset of values from a MySQL table being returned which are then charted. Each of the data points should have a fixed time period between them, EG:

Data Point 1: 1 - Date/Time: 2013-02-11 09:00:00

Data Point 2: 2 - Date/Time: 2013-02-11 09:05:00

Data Point 3: 3 - Date/Time: 2013-02-11 09:10:00

However, this is not always the case. If we can provide our charting package with a set of data values, a start time and an interval time it can process and display the chart much quicker than if we provide a list of values and a list of dates/times.

What is the most efficient way to either pre-fill or post-fill a MySQL query with 0 values where the data point is missing? For example, using the data above if data point 2 was missing , what query could I use to get MySQL to return the following dataset:

Data Point 1: 1 - Date/Time: 2013-02-11 09:00:00

Data Point 2: 0 - Date/Time: 2013-02-11 09:05:00

Data Point 3: 3 - Date/Time: 2013-02-11 09:10:00

Thanks in advance

user2060944
  • 33
  • 4

2 Answers2

0

i wouldn't input garbage into the DB, you can do the pre-processing in application layer, it should be faster and more efficient.

If you are performing IO for each insert than the IO will be a huge bottleneck. ordering and inserting dummy points in a application layer , such as Java will probably be faster (you may do it before rendering your charts).

Additionally, assuming you have a file with the data logged in CSV formatted way, the insert using LOAD DATA will be very fast (might be 20 times faster then pre-processing each line and inserting line by line )

Michael
  • 2,827
  • 4
  • 30
  • 47
0

In this scenario I would initialize an array/hash/list in code to my zero/null values and then overwrite your values where you receive data back from the Database.

If this needs to be done server side, you could create a temp table storing all possible dates in your interval of interest and left join your current query to it.

Example here of the second here: What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

Community
  • 1
  • 1
Matthew
  • 9,851
  • 4
  • 46
  • 77