CODEWITHSUNDEEP
c#.netsqlite
Fatal510
Fatal510

Reputation: 1723

get data from the last X minutes sqlite

I have this query:

SELECT who,whenAT
FROM seen
WHERE whenAT <= Datetime('now', '-5 minutes')

DateTimes stored in whenAT are formatted like this "10/12/2011 12:33:13 AM" whenAT is a TimeStamp.

that current query returns all records for some reason.

i'm inserting the datetime from code as DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") this is what is being saved into the table "10/12/2011 12:33:13 AM" i want to get all records within the last 5 minutes. everything i have tried either returns all records or no records.

Upvotes: 1

Views: 5873

Answers (3)

Julian Cendana
Julian Cendana

Reputation: 31

as Fatal510 said, you need to add the modifier 'localtime'

here some reference about datetime modifier https://www.sqlite.org/lang_datefunc.html

and here some example

SELECT who,whenAT FROM seen WHERE whenAT >= Datetime('now', '-5 minutes', 'localtime')

Upvotes: 3

Fatal510
Fatal510

Reputation: 1723

needed to add the modifier 'localtime'

Upvotes: 1

fluf
fluf

Reputation: 1265

Your query should be 

SELECT who,whenAT FROM seen WHERE whenAT >= Datetime('now', '-5 minutes')

To get the last 5 minute, < will get everything besides the last 5 minutes

Upvotes: 2

Related Questions