How to get smallest time from database table

Question

I have a table that has time column in yyyy-MM-dd:HH:mm format .I want to get the the time value that is nearest to current time and date.I am able to get the time value that is nearest to current time but not the date.

This is what I am doing

    SimpleDateFormat mSDFmSDF = new SimpleDateFormat("HH:mm");//yyyy-MM-dd:HH:mm not working
    Calendar calSet = Calendar.getInstance();
    TimeZone tz = TimeZone.getDefault();
    mSDF.setTimeZone(tz);
    String time = mSDF.format(calSet.getTime());
    Cursor selectedtime = mDB.rawQuery("SELECT " + ALARM_TIME + " FROM "
            + ALARM_TABLE + " WHERE " + ALARM_TIME + " >=  '" + time
            + "' ORDER BY " + ALARM_TIME + " LIMIT 1 ;", null);

Every suggestions are welcomed please help me

I would consider making the time a timestamp instead of String representation, much easier to query against. — Carnal, Oct 22 '13 at 09:23
@Shakeeb Shaheen pls see this link get current time only http://stackoverflow.com/questions/3611059/how-to-get-the-current-time/3611122#3611122 — Karthikeyan Karthik, Oct 22 '13 at 09:28
@Carnal can u put it into small demo code for timeStamp to show how to use it for date and time sorting — Shakeeb Ayaz, Oct 22 '13 at 09:42
@KarthikeyanKarthik time is not a problem a Can get it easily problem is date and time together — Shakeeb Ayaz, Oct 22 '13 at 09:43
@Shakeeb Shaheen: What I mean is that in your SQLite, store the ALARM_TIME as a timestamp instead of a String. Then you can use System.currentTimeInMillis to get the next alarm. — Carnal, Oct 22 '13 at 09:49

score 1 · Answer 1 · answered Oct 22 '13 at 09:57

To perform arithmetic on date/time, SQLite provides JULIANDAY function which will convert your date into an FLOAT.

So,

SELECT ABS(JULIANDAY(SUBSTR(alarm_time,1,5)||' '||SUBSTR(alarm_time,-5))-JULIANDAY('NOW')), * FROM alarm_table;

will return time diferences (in days) from alarm_time to current time.

However, if you only need nearest time in future, simple comparison will suffice:

SELECT * FROM alarm_table WHERE alarm_time>=STRFTIME('%Y-%m-%d:%H:%M','NOW')
ORDER BY alarm_time LIMIT 1;

How to get smallest time from database table

1 Answers1