How can I group selected data by date (timestamp)?-CodePudding

I need to count entries grouped by date (field has TIMESTAMP type in database). I wrote code

sql = "
    SELECT COUNT(id) AS cnt, TO_CHAR(closed_on, 'YYYY-mm-dd') AS closed_on, issues.status_id
    FROM issues
    WHERE closed_on IS NOT NULL AND closed_on <= '#{end_from_created_date}'
    AND created_on <= '#{end_from_created_date}'
    GROUP BY closed_on, status_id
"

This query work on postrgesql, but it does not work on sqlite, because sqlite does not have TO_CHAR function Solution should be compatible with mysql, postgresql, sqlite.

I can use different sql for different DB. But it is not good idea.

May be I can use functions from ActiveRecord, but i do not find solutions in docs

CodePudding user response：

SQLite uses several date formats; PostgreSQL uses similar formats and more.

Both understand YYYY-MM-DD HH:MM:SS.

CodePudding user response：

The solution is

sql = "SELECT COUNT(id) AS cnt, DATE(closed_on) AS closed_on, issues.status_id FROM issues WHERE closed_on IS NOT NULL AND closed_on <= '#{end_from_created_date}' AND created_on <= '#{end_from_created_date}' GROUP BY DATE(closed_on), status_id"