2

I'm a beginner to Sqlite and struggling to put together a query for what I need. :-( Can anyone help me please?

I have a given date. In my examples below this variable is called 'givenDate' and is of java type Date and therefore in the format yyyy-MM-dd.

I also have a database table called 'main' with an 'ID' and 'Date' column. The Date column entries are in ISO standard String format. So the entries look like this:

ID   Date
001  1949-08-13 06:29:21.000
002  1943-08-13 10:13:45.000
003  1948-08-13 09:12:32.100
004  etc.

I need to get a list of the 10 nearest dates from table 'main' when comparing Date column with my given date 'givenDate'.

So I need to:

  1. Compare each date in the Date column to my given date 'givenDate'
  2. Work out the Absolute difference between them
  3. Order them by smallest to biggest difference
  4. Return the ID and Date for each of the smallest 10 results

So, if the givenDate is 1948-09-13 then the result would be:

003  1948-08-13 09:12:32.100
001  1949-08-13 06:29:21.000
002  1943-08-13 10:13:45.000
etc

DateDiff doesn't work in Android as the database is an Sqlite database.

I have tried doing something like this:

String query = 
"SELECT ID, Date" +
"FROM main" +
"WHERE Date IS NOT NULL" +
"ORDER BY ABS(date("+givenDate+") - Date) ASC" +
"LIMIT 10";

This doesn't seem to work. I just get a list of dates with the same year as the year in the givenDate and in ascending order. The date difference doesn't seem to actually calculate the difference in date and give the 10 nearest.

I've looked through numerous posts but can't find anything specific to do what I need.

Any help would be greatly appreciated.

Thanks, Simmy :-)

The answer was:

Format sqliteDateFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); 

String query2 = 
"SELECT _id, Date " + 
"FROM main " + 
"WHERE Date IS NOT NULL " + 
"ORDER BY ABS(JULIANDAY('" + sqliteDateFormatter.format(aDate) + "') - " + "JULIANDAY(Date)) " + 
"LIMIT 10";
Simmy
  • 188
  • 1
  • 9
  • SQLite does not have variables. How do you actually pass the value of `givenDate` into the query? – CL. Nov 11 '12 at 19:37
  • I've updated my original message to show how the givenDate is passed through in Android. I've changed givenDate to "+givenDate+" – Simmy Nov 11 '12 at 22:35

1 Answers1

0

According to the documentation, just converting a Date to a string will result in a format that is not understood by SQLite; you'll have to explicitly use the correct format.

Furthermore, these date values are just strings, so you cannot simply subtract them. You have to convert them to some numeric date format first.

Format sqliteDateFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String query = ... +
    "ORDER BY ABS(JULIANDAY('" + sqliteDateFormatter.format(date) + "') - " +
                 "JULIANDAY(Date))" +
    ...
Community
  • 1
  • 1
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks for this. I tried it in my code but I get the following error when I try to run it: 11-13 19:11:48.616: E/AndroidRuntime(13804): android.database.sqlite.SQLiteException: near "Date": syntax error (code 1): , while compiling: SELECT ID, DateFROM mainWHERE Date IS NOT NULLORDER BY ABS(JULIANDAY('1995-10-23 00:00:00') - JULIANDAY(Date))LIMIT 10 Any ideas? Thanks, Simmy – Simmy Nov 13 '12 at 19:15
  • Insert spaces where you concatenate strings. – CL. Nov 13 '12 at 20:26
  • Thankyou, this works now! :-D For the benefit of everyone, this is the actual code I used: 'Format sqliteDateFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); String query2 = "SELECT _id, Date " + "FROM main " + "WHERE Date IS NOT NULL " + "ORDER BY ABS(JULIANDAY('" + sqliteDateFormatter.format(aDate) + "') - " + "JULIANDAY(Date)) " + "LIMIT 10"; ' – Simmy Nov 13 '12 at 21:20