Show last 30 days, even if no records exist

Databases
1

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

day|count
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?

Thanks

/Cyberfunkr

2

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.