How to apply SELECT date('now') query in android?

Question

I am working on a project and now I have been stuck on a weird stage.

I know that we can execute any query that has to do anything with database we can write that using:

Cursor cursor = db.rawQuery("SELECT * FROM table_name", null);

But now I want to execute this query:

"SELECT strftime('%s','now','-2 day')"

Generally the above query returns unixepoch time of one day before yesterday.

Does anyone know how can I get this done?

Nothing is wrong with the query. I just want to know how to apply this kind of query. Because when I apply using the above method it searches for the column name strftime('%s','now','-2 day'). You know what I am saying? — activesince93, Oct 01 '15 at 19:51

score 1 · Answer 1 · answered Oct 01 '15 at 20:45

1

Date date = new Date();

will return the current day, if you then subtract X numbers of days, then parse that Date to String with the required format. You can do this with the method SimpleDateFormat:

String newDate = new SimpleDateFormat(pattern).format(date);

where pattern can be something like this "dd-MM-yyyy"

Then, you can add said date to your query.

answered Oct 01 '15 at 20:45

Waclock

1,686
19
37

Since we can execute any SQLite query, I am just curious about this query, How can I execute these types of queries that doesn't have to do with database? You can check this link https://www.sqlite.org/lang_datefunc.html – activesince93 Oct 02 '15 at 06:10

score 1 · Accepted Answer · answered Oct 02 '15 at 07:45

You would handle this query like any other query:

Cursor cursor = db.rawQuery("SELECT strftime('%s','now','-2 day')", null);
try {
    if (cursor.moveToFirst()) {
        long seconds = cursor.getLong(0);
        ...
    } else {
        // cannot happen with this query
    }
} finally {
    cursor.close();
}

If you want to access the column by name (which is completely pointless if you have only a single column), you can give it an alias:

cursor = db.rawQuery("SELECT strftime(...) AS s", null);
cursor.getColumnIndexOrThrow("s");  // returns zero
...

Please note that for queries that return a single value, there is a helper function:

long seconds = DatabaseUtils.longForQuery(
                   db, "SELECT strftime('%s','now','-2 day')", null);

How to apply SELECT date('now') query in android?

2 Answers2