Generate Queries Symbolically

One of the great advantages of working with the Wolfram Language is that code and data are represented in the same way. This example looks at ways to programmatically generate Wolfram Language code to query relational databases.    

Suppose you want to group orders by quarter. SQL does not have date handling that is as rich as the one in Wolfram Language. Because of that, it is not easy to create a function that turns dates into quarters. First of all, you need to know the span of the dates.

Since SQLite does not support date types natively, these are stored as Unix times; fortunately you can work with those.

Now that you know the span of the data, you can programmatically construct a function that takes the Unix times and turns them into the quarter names.

As you can see, this function generates this rather ugly piece of code.

You can now use this to create a new property for the quarter.

Which you can now use to aggregate the orders by quarter.

The following is the only code that is actually performing a query.

You can now use the data to generate a chart.