CODEWITHSUNDEEP
androidsqlitedatedatetime
Noodelz
Noodelz

Reputation: 143

SQLite isn't returning correct records based on a date

I have four buttons on an android app i'm making. Each button queries an SQLite database and grabs records based on a date. (Buttons are 'Today', 'Week', 'Month', 'All')

The date is stored as a DATETIME in my table, and i'm querying it with a date as well. Everything works well, until i'm grabbing everything that's been done ahead of the previous month.

Example

Todays date is June 29th 2016

I create an entry on June 16th 2016 into my SQLite Database and it stores successfully. I create another entry on June 27th 2016 and it stores successfully.

Now I press the 'Today' button and return 0 records. (Correct)

I press the week button and return 1 record (Correct - Created on June 27th)

I press the month button and return 0 records. (Incorrect. I should be getting 2 records)

The Code

This is the query I use for my database:

String queryString = "SELECT * FROM job_quote_lookup WHERE created_at >= '" + date + "' ORDER BY created_at ASC"

The 'date' is calculated like so:

Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE, -31);
Date fromDate = cal.getTime();

date = dateFormat.format(fromDate);

(31 represents the largest possible number of days in a month period)

It seems that the problem occurs when the date goes back a month. If I remove 20 days instead of 31, the date will "June 9th" and i will receive BOTH records as i should. But if the date goes into May, i will receive 0 records.

Other queryStrings i have tried

String queryString = "SELECT * FROM job_quote_lookup WHERE created_at >= date('now','-31 days') ORDER BY created_at ASC"

This didn't seem to work?

I'm still new to Android so apologies if it's a bad question. I have googled around but haven't found anything that might help.

UPDATE: Extra Info

Here is some additional code that might be more helpful

The dateFormat used to time stamp entries into my SQLite is:

private String getDateTime() {
    SimpleDateFormat dateFormat = new SimpleDateFormat(
            "dd/MM/yyyy - HH:mm", Locale.getDefault()); //"yyyy-MM-dd HH:mm:ss" - "dd-MM-yyyy HH:mm:ss"
    Date date = new Date();
    return dateFormat.format(date);
}

The date format used when i'm querying my database is:

DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");

I'm not sure if this is correct, however my reasoning for leaving "HH:mm" off was due to the "Today" option not working correctly as it would return everything greater than the current time (which is obviously nothing in terms of time stamping)

Unable to solve...Ended up converting my database DATETIME to INTEGER and storing the date in milliseconds then just converting between date format and milliseconds to query and display information.

Upvotes: 2

Views: 421

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

If you want to alter the month of a Java Calendar object you will need to use:

cal.add(Calendar.MONTH, -1);

where the above snippet would roll the month back by one. If you call cal.add(Calendar.DATE, -31) on February 15, for example, you get a weird result because the month won't change but the day will roll over.

Update:

You also have to make sure that the date format you feed into MySQL is correct. MySQL expects the format YYYY-MM-DD for dates, so use this:

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String date = sdf.format(cal.getTime());

I recommend using JodaTime if you expect complex calendar operations.

Upvotes: 3

Related Questions