CODEWITHSUNDEEP
sqlsqlite
user1971598
user1971598

Reputation:

Get all records that satisfy this month in SQLite

I'm trying to get all the records that occur for during the month.

The table looks like: 

CREATE TABLE event (
    title TEXT NOT NULL,
    all_day INT NOT NULL,
    start INTEGER NOT NULL,
    end INTEGER NOT NULL,
    description TEXT NOT NULL,
    creator INTEGER NOT NULL,
    url TEXT NOT NULL,
    id TEXT NOT NULL,
    UNIQUE (ID)
);

And the query I was using, which is wrong, is: 

select count(*) as event_count from
  (select title from event
  where (strftime('%m', datetime(end, 'unixepoch')) - 1) =
 (strftime('%m', 'now') + 0) group by title);

I'm not sure where the error, appreciate help.

Thanks

Upvotes: 0

Views: 155

Answers (1)

Doug Currie
Doug Currie

Reputation: 41180

You don't need nested selects.

You should have title in the projection.

The easier way to get month is: strftime('%m',end,'unixepoch')

Putting it together...

sqlite> CREATE TABLE event ( ...> title TEXT NOT NULL, ...> all_day INT NOT NULL, ...> start INTEGER NOT NULL, ...> end INTEGER NOT NULL, ...> description TEXT NOT NULL, ...> creator INTEGER NOT NULL, ...> url TEXT NOT NULL, ...> id TEXT NOT NULL, ...> UNIQUE (ID) ...> ); sqlite> insert into event values ("foo",0,0,5667567567,'',9,'',''); sqlite> select title, strftime('%m',end,'unixepoch') from event; foo|08 sqlite> select title, count(*) as eventcount from event where (strftime('%m','now') + 5) = (strftime('%m',end,'unixepoch') + 0) group by title; foo|1 sqlite>

Upvotes: 1

Related Questions