5
Formulating the question was a little difficult, but I will try to be as explicit as possible using an example to put the question.
There are the following data in my table:
The aim is to carry out a consultation sql
(the DBMS used is Mysql) in the date range 2014-09-01 until 2014-09-10 and have the following result:
What I already have:
To be honest, I have no idea how to do this, so far I have solved this in the control of my application, an algorithm like:
- Select the data in the DB, by means of a
between
of the desired dates and store in a list; - For each day (or time) existing in the desired date range, store in an auxiliary variable (an instance of
Calendar
in Java); - Check if In the list obtained in point 1 there is an element with this date, if there is not, add an element in the resulting list with this date, but zero in the quantity field;
Is there any way to do this already in consultation on sql
? The way I do it now is not the most efficient.
Cold, you can create a table with periods (only with dates), and make a
left join
with the table you already use.– Wakim
@Wakim, if I work with long intervals of dates, I will have to have a huge table in parallel just to save dates?
– Cold
You only need one record per day. In the worst case, 3650 lines for 10 years. You will have to analyze if the performance gain is worth it, you can even use an index.
– Wakim
Eh... but we would be considering only days, my realization is statistical data with a considerable accuracy : every 10 seconds. A query, in my view If much more efficient, we would only consume resources when necessary and not occupy physical memory permanently and have to "join" always. But if no other possibility arises, I will think about using something like that. Thanks @Wakim
– Cold