sqlite> select '_' || dttm || '_' from foo limit 20; _'2017-11-08 07:00:01'_
_'2017-11-08 07:00:01'_ _'2017-11-08 07:00:01'_ _'2017-11-08 07:01:01'_ _'2017-11-08 07:01:01'_ _'2017-11-08 07:01:01'_ _'2017-11-08 07:02:01'_ _'2017-11-08 07:02:01'_ _'2017-11-08 07:02:01'_ _'2017-11-08 07:03:01'_ _'2017-11-08 07:03:01'_ _'2017-11-08 07:03:01'_ _'2017-11-08 07:04:01'_ _'2017-11-08 07:04:01'_ _'2017-11-08 07:04:01'_ _'2017-11-08 07:05:01'_ _'2017-11-08 07:05:01'_ _'2017-11-08 07:05:01'_ _'2017-11-08 07:06:01'_ _'2017-11-08 07:06:01'_ -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, March 22, 2018 6:48 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] How to convert a datetime column to a date? On 2018/03/22 3:21 PM, Ron Watkins wrote: > It doesn't seem to work that way. I have 192330 distinct dttm entries, but I > still only get 1 row. It seems as the “date(dttm)” result is either null or > the empty string for all records. Not sure why. This works in other > databases, so there must be something subtle about the “date()” function or > the “dttm” column that im missing here. > > > > sqlite> select date(dttm) as dt,max(used_kb)/1024.0/1024.0 from foo > sqlite> group by dt order by 1; > > |5038.83195495606 > > sqlite> select count(distinct dttm) from foo; > > 192330 > > sqlite> Could you post the results of: SELECT '_' || dttm || '_' FROM foo LIMIT 20; The underscores are just so I can tell if there are any weird leading/trailing spaces in your data. _______________________________________________ sqlite-users mailing list <mailto:sqlite-users@mailinglists.sqlite.org> sqlite-users@mailinglists.sqlite.org <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> 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
