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:[email protected]] On
Behalf Of Ron Watkins
Sent: Wednesday, March 21, 2018 11:30 AM
To: [email protected]
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
