CODEWITHSUNDEEP
androidsqlsqlitedatetime
Psypher
Psypher

Reputation: 10829

SQLlite query to get data between 2 Datetime

I am using below query to get data from DB between a start time(it is 2hours from start time) and end time mentioned, but this query does not seem to work.

The column REC_CONT_TIME is datetime in the format YYYY-MM-DD HH:MM

Query is:

SELECT * FROM REC_CONTACT WHERE datetime('now')

BETWEEN

strftime('%Y-%m-%d %H:%M:%S',REC_CONT_TIME)

AND

datetime(strftime('%Y-%m-%d %H:%M:%S',REC_CONT_TIME),'+120 minutes')

Start time: strftime('%Y-%m-%d %H:%M:%S',REC_CONT_TIME)

End time: datetime(strftime('%Y-%m-%d %H:%M:%S',REC_CONT_TIME),'+120 minutes'). Added 120 minutes from start time.

Upvotes: 0

Views: 562

Answers (2)

clesiemo3
clesiemo3

Reputation: 1129

If you do not include 'localtime' on your datetime('now') function you will be using GMT time. This most likely is not the timezone your dates are in and will result in no values returned as it is not in your 2 hour window.

You can see the difference with this query:

select datetime('now'),datetime('now','localtime')

Try this:

SELECT * 
FROM REC_CONTACT 
WHERE datetime('now','localtime')
    BETWEEN
        strftime('%Y-%m-%d %H:%M:%S',REC_CONT_TIME)
    AND
        datetime(strftime('%Y-%m-%d %H:%M:%S',REC_CONT_TIME),'+120 minutes')

Upvotes: 1

GVillani82
GVillani82

Reputation: 17429

If I understand, you are trying to get all the rows from your table that have the date value in a range from startDate to endDate.

Supposing the startDate is NOW and the endDate is NOW + 120 minutes, you can use this query:

SELECT *
FROM REC_CONTACT
WHERE strftime('%s', REC_CONT_TIME )  
BETWEEN strftime('%s', 'now','localtime') 
AND strftime('%s', datetime(strftime('%Y-%m-%d%H:%M:%S','now','localtime'),'+120 minutes'))

Look at this fiddle

Upvotes: 0

Related Questions