CODEWITHSUNDEEP
androidsqlitedatetime
Langkiller
Langkiller

Reputation: 3457

Android and SQLite - select everything between 2 datetime's

How can I select everything from an Android SQLite database that has DATETIME fields for a particular day?

I have created the following table:

private static final String DATABASE_CREATE = "" +
            "create table " + TABLE_STATISTIC + " (" 
              + COLUMN_STATISTIC_ID + " integer primary key autoincrement, " 
              + COLUMN_STATISTIC_DATETIME + " DATETIME default current_timestamp, "
              + COLUMN_STATISTIC_SERVICED + " integer not null, "
              + COLUMN_STATISTIC_CATEGORY + " integer); ";

As you can see, it has a DATETIME field. I need to fetch all data between DATETIME from the beginning of the day and end of the day.

How can this be done? I can't find out how to generate these values in java/android or directly in the SQL statement.

I really hope someone can answer this. Thanks in advance

Upvotes: 0

Views: 2062

Answers (5)

Ahmed Zayed
Ahmed Zayed

Reputation: 2185

I think the best solution in your case, if you only want to get the date between specified number of days is to use date('now') function in SQL. So, i think this would be a better solution in your case

SELECT * FROM TABLE_STATISTIC WHERE COLUMN_STATISTIC_DATETIME BETWEEN DATE('now') and 
DATE('now','1 day')

And, in case if you want to compare considering localtime of the device you can use the following

SELECT * FROM TABLE_STATISTIC WHERE COLUMN_STATISTIC_DATETIME BETWEEN DATE('now','localtime') and DATE('now','1 day','localtime')

Upvotes: 0

Jaihind
Jaihind

Reputation: 2778

SQLite does not have real date/time types but stores everything as strings. A string is accepted as a date in SQLITE if and only if it has a YYYY-MM-DD HH:MM:SS format.

current_timestamp: This function also gets the current date time in the format given above.

SELECT * FROM TABLE_STATISTIC WHERE COLUMN_STATISTIC_DATETIME BETWEEN '2014-01-01 13:42:03' AND '2014-03-20 13:42:03';

Upvotes: 1

Joakim Palmkvist
Joakim Palmkvist

Reputation: 540

As the other people are saying, save the time in long.

private static final String DATABASE_CREATE = "" +
            "create table " + TABLE_STATISTIC + " (" 
              + COLUMN_STATISTIC_ID + " integer primary key autoincrement, " 
              + COLUMN_STATISTIC_DATETIME + " integer not null, "
              + COLUMN_STATISTIC_SERVICED + " integer not null, "
              + COLUMN_STATISTIC_CATEGORY + " integer); ";

Then in your select

"Select * from " + TABLE_STATISTIC + " where " + COLUMN_STATISTIC_DATETIME + " > " + startTime " AND " COLUMN_STATISTIC_DATETIME + " < " + endTime + ";"

Add startTime and endTime into your function, these should be in millis

Upvotes: 0

RizN81
RizN81

Reputation: 1039

There is lot of options available on google if you do some more research on between clause You can also try this 

`SELECT * FROM DOB WHERE birthdate BETWEEN #07/04/1996# AND #07/09/1996#;`

OR 

SELECT Date,dob from pb where uid=1 and Date>'2011/02/25' and Date<'2011/02/27'

and this will guide more.

Upvotes: 0

kirankk
kirankk

Reputation: 628

1.First save date in database in long format.

2.Then compare these date with start and end dates as follows

dbDate > startTime && dbdate < endTime

where startTime and endTime is range in which you want to fetch data

Upvotes: 1

Related Questions