This month and last month are the same with sqlite

[gseva]

Querys filtered by last month and this month are returning the same data (and it's for this month, this is March).

I checked the logs and it's converted into WHERE (DATE(DATETIME("table"."date", 'unixepoch'), 'start of month') = DATE(DATETIME('now', '-1 months'), 'start of month')).

And DATE(DATETIME('now', '-1 months'), 'start of month')) at March 30 returns March.

This looks like this problem. The second answer seems to solve it.

• I'm running Metabase on docker.
• My database is sqlite
• My Metabase version is v0.16.0.

[camsaul]

Thanks for spotting this @gseva. I'll look into it.

[camsaul]

Some more details

this month

WHERE (DATE("checkins"."date", 'start of month') = DATE(DATETIME('now'), 'start of month'))

last month

WHERE (DATE("checkins"."date", 'start of month') = DATE(DATETIME('now', '-1 months'), 'start of month'))

The problem in DATETIME('now, '-1 months') in SQLite on March 30th -> Feb 30th -> March 2nd. And the start of that month is March 1st.

The recommendation from the SO link in the description is to do

DATETIME('now', 'start of month', '-1 month')

instead.