CODEWITHSUNDEEP
javaandroidsqlitedateandroid-sqlite
Seif E. Attia
Seif E. Attia

Reputation: 33

problem while using database column value into Date() sqlite android


i have small problem, i googled it many times but i couldn't get it...
i'm using a query (sqlite) to retrieve some data ..
in this query their is Date() used to increase certain date dynamic number of days (coming from column in the same table) ..
if i put this days static (1,2,3,.....) it works fine but if i put column name it doesn't.
this query fails and i want it to work:

String selectQuery = "SELECT DISTINCT * FROM " + TABLE_NAME + " WHERE "
            + DATE + " = date('" + targetDate + "',' " + REPETITIONS + " day') ";

this works fine :

String selectQuery = "SELECT DISTINCT * FROM " + TABLE_NAME + " WHERE "
            + DATE + " = date('" + targetDate + "','2 day') ";

where

targetDate: date entered by user to get events of that date

DATE: date of every event in the table

REPETETIONS: dynamic number (column in the same table)

the problem is using REPETITIONS in the date function...

create statement 

String SQL_CREATE_EVENT_TABLE = "CREATE TABLE " + TABLE_NAME + " ( " +
            ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            TITLE + " TEXT ," +
            DATE + " TEXT , " +
            IS_NOTIFY + " INTEGER , " +
            NOTIFICATION_TIME + " TEXT ," +
            REPEAT + " INTEGER ," +
            REPEAT_DURATION + " INTEGER ," +
            REPETITIONS + " INTEGER ," +
            CERTAIN_DATE + " TEXT ," +
            NOTE + " TEXT ," +
            IS_SPOKEN + " INTEGER " +
            " );";

and this is the selecting statement

String selectQuery = "SELECT DISTINCT * FROM " + TABLE_NAME + " WHERE " + DATE + " = '" + targetDate
            + "' OR (( " + REPEAT + " = '1' AND " + REPEAT_DURATION + " = '0' ) AND " + DATE + " <= '" + targetDate + "')"
            + " OR( " + REPEAT + " = '1' AND " + REPEAT_DURATION + " = '3' ) AND " + DATE + " <= '" + targetDate + "' AND " + CERTAIN_DATE + " >= '" + targetDate + "'"
            + " OR (" + REPEAT + " = '1' AND " + REPEAT_DURATION + "= '2' ) AND " + targetDate + " >= '" + DATE + "' AND "+ targetDate+ " <= date('" + DATE + "','" + REPETITIONS + " days')";

Upvotes: 0

Views: 86

Answers (2)

forpas
forpas

Reputation: 164184

The variable REPETITIONS in this statement:

String selectQuery = "SELECT DISTINCT * FROM " + TABLE_NAME + " WHERE "
            + DATE + " = date('" + targetDate + "',' " + REPETITIONS + " day') ";

should be a number and not a column name.
The date function in SQLite has various syntaxes but you use this one:

SELECT date('2014-10-23','+7 day');

you must supply a number before day.

Edit try this: 

String selectQuery = "SELECT DISTINCT * FROM " + TABLE_NAME + " WHERE "
            + DATE + " = date('" + targetDate + "', " + REPETITIONS + " || ' day') ";

Upvotes: 1

laalto
laalto

Reputation: 152907

In sqlite date() function, the NNN days is a string modifier and not an expression. Therefore you cannot use column names or even || string concatenation there.

You can format the modifier string in your code and place it in the SQL. This requires additional query to the database, which is likely not what you want.

If you can assume each day is 24 hours (which is not always true, consider e.g. DST events), you could try something like

... date(strftime('%s', '" + targetDate + "', " + REPETITIONS + "*24*60*60, 'unixepoch') ...

where strftime('%s', ...) converts to seconds and date(..., 'unixepoch') converts back to datestamp.

But seriously I'd consider redesigning the schema to better support your functional requirements.

Upvotes: 1

Related Questions