Extract month from date in Apache Superset SQL Lab Query-CodePudding

I have a MySQL database in the backend of an Apache Superset installation, and I am trying to create a table where each row aggregates some counts by month, given a datetime column. I am able to use the GROUP BY and ORDER BY clauses successfully using Jinja templating, but SELECT does not work:

SELECT client_id, COUNT(DISTINCT file_name) AS n_files, status, '{{ received_date.strftime("%B %Y") }}' AS 'month'
FROM `Table`
WHERE received_date >= '2022-01-01 00:00:00.000000'
AND received_date < '2022-09-30 00:00:00.000000'
GROUP BY client_id, status, '{{ received_date.strftime("%B %Y") }}'
ORDER BY client_id, '{{ received_date.strftime("%B %Y") }}', status

Ideally this query's result should be something like:

client_id | n_files | status | month
-------------------------------------
1         |   10    | Pass   | January 2022
1         |   5     | Fail   | January 2022
2         |   42    | Pass   | January 2022
2         |   12    | Fail   | January 2022
1         |   30    | Pass   | February 2022
1         |   8     | Fail   | February 2022
2         |   96    | Pass   | February 2022
2         |   7     | Fail   | February 2022
...

But instead I am getting:

client_id | n_files | status | month
-------------------------------------
1         |   10    | Pass   | {{ received_date.strftime("%B %Y") }}
1         |   5     | Fail   | {{ received_date.strftime("%B %Y") }}
2         |   42    | Pass   | {{ received_date.strftime("%B %Y") }}
2         |   12    | Fail   | {{ received_date.strftime("%B %Y") }}
1         |   30    | Pass   | {{ received_date.strftime("%B %Y") }}
1         |   8     | Fail   | {{ received_date.strftime("%B %Y") }}
2         |   96    | Pass   | {{ received_date.strftime("%B %Y") }}
2         |   7     | Fail   | {{ received_date.strftime("%B %Y") }}
...

Unfortunately this flavor of MySQL on Superset does not support something like MONTH(received_date) which would make it easier, but I am not sure what am I doing wrong with the Jinja templating of the datetime column.

CodePudding user response：

You could use DATE_FORMAT instead because strftime is used in sqlite only so you could edit it like this

SELECT client_id, COUNT(DISTINCT file_name) AS n_files, status, DATE_FORMAT(received_date, '%M %Y') AS 'month'
FROM `Table`
WHERE received_date >= '2022-01-01 00:00:00.000000'
AND received_date < '2022-09-30 00:00:00.000000'
GROUP BY client_id, status, '{{ received_date.strftime("%B %Y") }}'
ORDER BY client_id, '{{ received_date.strftime("%B %Y") }}', status