Your query is fine. If you're only getting 1 row, then there's only 1 date in your data. If you're not getting a date column, then there's something drastically wrong with whatever you're using, as you have it right there in your query. Are your datetimes not stored correctly perhaps?
SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table foo (dttm datetime not null, i int not null); --EQP-- 0,0,0,SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?) sqlite> insert into foo values ...> ('2018-03-22 07:00:00', 12), ...> ('2018-02-01 10:00:00', 1), ...> ('2018-03-22 05:00:00', 20), ...> ('2018-02-01 12:00:00', 2); sqlite> select date(dttm) as dt, max(i) from foo group by dt order by 1; --EQP-- 0,0,0,SCAN TABLE foo --EQP-- 0,0,0,USE TEMP B-TREE FOR GROUP BY dt|max(i) 2018-02-01|2 2018-03-22|20 -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Ron Watkins Sent: Wednesday, March 21, 2018 11:30 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] How to convert a datetime column to a date? I have a table which contains a datetime column: table|foo|foo|2|CREATE TABLE foo ( dttm datetime not null i int not null ) I want to select out the max(i) value for each day where there are multiple records per day. select date(dttm) dt,max(i) from foo group by dt order by 1; However, it’s returning only 1 row, with no date column shown. How can I get an actual “date” listed in the first column, and also get 1 row per “date” value. |5283598256 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users