I've done a bit of searching but no one has this this specific example.
I have a transaction database which stores the datetime stamp as a unix value. I am trying to create a report that shows a count of transactions that happen per day. The select statement converts the unix timestamp to '2010-03-16' format so I would get a result of
2010-03-16 | 7
2010-03-15 | 3
2010-03-13 | 2
2010-03-12 | 5
2010-03-10 | 6
My problem, as shown above, is that there are days where no transactions occurred. I need every day shown, even if it has zero.
My first idea was to do a sub-query that would output the last 30 days in the same format, then do an outer join so nulls will fill in the blanks. But since I can't find any examples of anything like this, I'm beginning to wonder if this is the best way.
So my question is two-fold:
1) Is this best method for filling in the blank days? Admittedly I could create a dedicated table of every day, but that seems like a huge waste.
2) If I'm on the right track, how can I generate a sub-query of the last 30 days?
The answer is two-fold:
1) Display logic belongs in your application, not the database. The appropriate place to format data for output is in your app, where you can easily display a range of dates.
2) If you must do it in SQL, then create a table with the numbers 1 to 31. Use a join against that (perhaps with a little string concatenation) to get the dates. You can use LAST_DAY() in your WHERE clause to not pass 28/30 if it's not a 31 day month.