Group by date in a particular format in SQLAlchemy

Question

I have a table called logs which has a datetime field. I want to select the date and count of rows based on a particular date format.

How do I do this using SQLAlchemy?

Answer 1

I don't know SQLAlchemy, so I could be off-target. However, I think that all you need is:

SELECT date_formatter(datetime_field, "format-specification") AS dt_field, COUNT(*)
    FROM logs
    GROUP BY date_formatter(datetime_field, "format-specification")
    ORDER BY 1;

OK, maybe you don't need the ORDER BY, and maybe it would be better to re-specify the date expression. There are likely to be alternatives, such as:

SELECT dt_field, COUNT(*)
    FROM (SELECT date_formatter(datetime_field, "format-specification") AS dt_field
              FROM logs) AS necessary
    GROUP BY dt_field
    ORDER BY dt_field;

And so on and so forth. Basically, you format the datetime field and then proceed to do the grouping etc on the formatted value.

Answer 2

Does counting yield the same result when you just group by the unformatted datetime column? If so, you could just run the query and use Python date's strftime() method afterwards. i.e.

query = select([logs.c.datetime, func.count(logs.c.datetime)]).group_by(logs.c.datetime)
results = session.execute(query).fetchall()
results = [(t[0].strftime("..."), t[1]) for t in results]

Answer 3

I don't know of a generic SQLAlchemy answer. Most databases support some form of date formatting, typically via functions. SQLAlchemy supports calling functions via sqlalchemy.sql.func. So for example, using SQLAlchemy over a Postgres back end, and a table my_table(foo varchar(30), when timestamp) I might do something like

my_table = metadata.tables['my_table']
foo = my_table.c['foo']
the_date = func.date_trunc('month', my_table.c['when'])
stmt = select(foo, the_date).group_by(the_date)
engine.execute(stmt)

To group by date truncated to month. But keep in mind that in that example, date_trunc() is a Postgres datetime function. Other databases will be different. You didn't mention the underlyig database. If there's a database independent way to do it I've never found one. In my case I run production and test aginst Postgres and unit tests aginst SQLite and have resorted to using SQLite user defined functions in my unit tests to emulate Postgress datetime functions.