CODEWITHSUNDEEP
androiddatetimesqlitetimestamp
JustCurious
JustCurious

Reputation: 1858

Compare times in SQLite

I am using SQLite in Android to store a timeStamp in this format: yyyy-MM-dd HH:mm:ss I would like to compare just the time of the timestamp with some values eg. HOUR(timestamp)>12 etc. , but HOUR is not a legal function in SQLite. I have changed it as described below and I have something like this: " where "+" strftime('%H','"+timeStamp+"')>"+time1+" AND strftime('%H','"+timeStamp+"')<'"+time2; where time1 and time2 are integer between 0-23. I have 7 entries in the table so 7 timestamps. however i get 0 results back...

Upvotes: 2

Views: 1305

Answers (2)

Frohnzie
Frohnzie

Reputation: 3569

If the timestamp is in the table you want something like:

String time1 = 0;
String time2 = 12;    
String query = "select timestamp from table where strftime('%H',timestamp) < " +
       time1 +
       " and strftime('%H',timeStamp) < " +
       time2 +
       ";";

Upvotes: 1

Marc
Marc

Reputation: 16512

You can try

SELECT strftime('%H', 'now');

or

SELECT strftime('%H', columnName);

the output will be 0 to 24

Upvotes: 3

Related Questions