CODEWITHSUNDEEP
sqlite
SpecTrum
SpecTrum

Reputation: 67

DELETE query with WHERE operations

how I can delete my rows from this way?

My table:

sqlite> .schema mytable
CREATE TABLE mytable (_id INTEGER PRIMARY KEY, hash TEXT, date TEXT);

I want delete all rows which (current time - stored time) > 10 seconds. My query does not works:

DELETE FROM mytable WHERE (datetime('now') - datetime(date)) > 10;

Thanks in advance!

Upvotes: 0

Views: 93

Answers (2)

HalR
HalR

Reputation: 11073

Perhaps this would work:

DELETE FROM mytable WHERE datetime('now') > datetime(date, '+10 seconds')

Upvotes: 1

Larry Lustig
Larry Lustig

Reputation: 50970

First off, you have to make sure that you have valid SQLite date and time strings in your date field (you don't show any examples of what data that field holds).

Then, you have to use one of the methods described here http://www.sqlite.org/cvstrac/wiki/wiki?p=DateAndTimeFunctions to convert the dates into something subtractable. Probably the most straightforward is to format each value with the %s formatting string:

 strftime('%s', 'now') - strftime('%s', date_col)

Upvotes: 1

Related Questions