sqlite - How to select comments by date (with a DATETIME) field -

i have comments table store date , time comment posted, in datetime field. i'd know, how pull out comments 1 day. obviously, comments have been posted @ time during day.

so far, best come either using between 'yyyy-mm-dd 00:59:59' , 'yyyy-mm-dd 23:59:59', both dates day want or like 'yyyy-mm-dd%'.

surely, there's better way this?

many in advance.

in order records 1 day can use date() function in 'where' part of query, serve 2 purposes:
- truncate time portion datetime records
- parse 'yyyy-mm-dd' value of desired day

here create table 2 records, 1 timestamped of january 1st, 2007 , timestamped today datetime. select 2 records separately:

bash-3.2$ sqlite3 some.db sqlite version 3.8.10.2 2015-05-20 18:17:19 enter ".help" usage hints. sqlite> create table mytable (txt varchar(25), timestamp datetime); sqlite> insert mytable (txt, timestamp) values ('asdf',datetime()); sqlite> insert mytable (txt, timestamp) values ('asdf-2','2007-01-01 10:00:00'); sqlite> select * mytable date(timestamp) = date('2007-01-01'); asdf-2|2007-01-01 10:00:00 sqlite> select * mytable date(timestamp) = date(datetime()); asdf|2016-11-17 17:09:15 sqlite>