SQLite group by localtime date

I am storing dates with a timezone offset and I would like to use group by queries to group by the local time, and not UTC. Here is an example of how the dates are stored:

sqlite> CREATE TABLE t1 (datecol TEXT);
sqlite>
sqlite> INSERT INTO t1 (datecol) VALUES ('2022-01-24T22:00:00-07:00');
sqlite> INSERT INTO t1 (datecol) VALUES ('2022-01-24T23:00:00-07:00');
sqlite> INSERT INTO t1 (datecol) VALUES ('2022-01-25T00:00:00-07:00');
sqlite> INSERT INTO t1 (datecol) VALUES ('2022-01-25T01:00:00-07:00');
sqlite>
sqlite> SELECT DATE(datecol),COUNT(*) FROM t1 GROUP BY DATE(datecol);
2022-01-25|4

What I want to see is:

sqlite> SELECT DATE(datecol),COUNT(*) FROM t1 GROUP BY DATE(datecol);
2022-01-24|2
2022-01-25|2

I realize that one way to do this would be to manually subtract the timezone offset (in this case 7 hours). But that would have the weakness that it wouldn't work for daylight savings time.

sqlite> SELECT DATE(datecol,'-7 hours'),COUNT(*) FROM t1 GROUP BY DATE(datecol,'-7 hours');

You can add the localtime modifier to the date function:

SELECT date(datecol, 'localtime') AS localdate, count(*)
FROM t1 GROUP BY localdate;

See https://www.sqlite.org/lang_datefunc.html for details, including the "Caveats And Bugs" part.

But it is usually recommended to always store timestamps as UTC in SQLite, because it's the default, and makes things easier.

So you could convert your current timestamps to UTC with

UPDATE t1 SET datecol=datetime(datecol);