CODEWITHSUNDEEP
javaandroidsqlsqlitedatetime
Dumbo
Dumbo

Reputation: 14122

Check if timestamp (milli) belongs to a specific day

In a table of my SQLite database there is a timestamp (millisec) column. This table is really huge and I want a efficent way of querying all the rows that theire timestamp column refers to a specific day (in this case I want to get all enteries that belong to TODAY).

I came up with this, but it does not work and its quite slow. Any ideas will be appriciated. (NO I can not make any change in the database such as adding a DATETIME column...I have to do it with the timestamp).

public boolean isTimestampInToday(long timestamp){

    //get a timestamp from today
    long todayStamp = System.currentTimeMillis();

    Calendar c = Calendar.getInstance();
    c.setTimeInMillis(todayStamp);
    c.setTimeZone(TimeZone.getDefault());

    c.set(Calendar.HOUR_OF_DAY, 0);
    c.set(Calendar.MINUTE, 0);
    c.set(Calendar.SECOND, 0);
    c.set(Calendar.MILLISECOND, 0);

    Date startDate = c.getTime(); //START OF DAY

    c.set(Calendar.HOUR_OF_DAY, 23);
    c.set(Calendar.MINUTE, 59);
    c.set(Calendar.SECOND, 59);
    c.set(Calendar.MILLISECOND, 999);

    Date endDate = c.getTime(); //END OF DAY

    Long startStamp = startDate.getTime();
    Long endStamp = endDate.getTime();

    if(timestamp >= startStamp && timestamp < endStamp) {
        return true;            
    } else {
        return false;
    }      
}

I appriciate all the answers from expirienced guys, but to be honest the one that I could understand was the answer from @iaune. Now I have done this inside the query itself:

Calendar gc = new GregorianCalendar();
gc.setTimeZone(TimeZone.getDefault());
long toBeg = gc.getTimeInMillis();
toBeg -= toBeg % (24*60*60*1000);
long toEnd = toBeg + 24*60*60*1000;


Cursor cursor = mDb.rawQuery(
        "SELECT * FROM "
                + DbHelper.TABLE_PA_DATA
                + " WHERE "
                + DbHelper.COLUMN_TIMESTAMP
                + " BETWEEN "
                + toBeg
                + " AND "
                + toEnd
        , null);

Is there anything wrong with this approach or can it be improved more?

UPDATE

I realised that using INTEGER for timestamp column type is wrong. I added a DATETIME column...I should now use direct SQLite query functions I guess. any ideas?

Here is what I get when I dump the table:

ID  DATETIME                    TIMESTAMP      STEPS    CALORIES
1   2014-07-06 07:18:55         169629539           3   0
2   2014-07-06 07:19:10         169644509           4   0
3   2014-07-06 08:15:36         173030229           1   0
4   2014-07-06 08:16:04         173058397           4   0
5   2014-07-06 08:31:39         173993598           2   0
6   2014-07-06 08:33:20         174094714           5   0
7   2014-07-06 09:31:54         177609142           1   0
8   2014-07-06 09:42:24         178238517           1   0
9   2014-07-06 10:37:37         181551849           1   0
10  2014-07-06 11:00:31         182925950           1   0
11  2014-07-06 12:17:42         187557035           1   0
12  2014-07-06 14:14:39         194573993           2   0

OK I managed to do it with some addition to @laune solution:

Calendar cal = Calendar.getInstance();
// offset to add since we're not UTC
long offset = cal.get(Calendar.ZONE_OFFSET) +
        cal.get(Calendar.DST_OFFSET);
long toBeg = cal.getTimeInMillis();
toBeg -= (toBeg + offset) % (24*60*60*1000);
long toEnd = (toBeg) + 24*60*60*1000;

Using this query:

Cursor cursor = mDb.rawQuery(
        "SELECT * FROM "
                + Database.TABLE_PA_DATA
                + " WHERE "
                + Database.COLUMN_TIMESTAMP
                + " BETWEEN "
                + toBeg
                + " AND "
                + toEnd, null
);

Upvotes: 1

Views: 1623

Answers (6)

Junaid Bashir
Junaid Bashir

Reputation: 172

DateUtils.isToday(timeStampInMilli)

You can check if the timestamp is today's using this method.

Upvotes: 1

Basil Bourque
Basil Bourque

Reputation: 339342

Half-Open

Date-time comparisons are better done with the "Half-Open" approach where the beginning of a span of time is inclusive and the ending is exclusive. For a single day that means you want query for date-times that are GREATER THAN OR EQUAL TO the first moment of the day in question AND LESS THAN the first moment of the day after. That is, up to bit not including the next day. This approach avoids the problem of infinitely splitting the last moment of the day.

Joda-Time

The java,util.Date and .Calendar classes are notoriously troublesome. Avoid them. Use the Joda-Time library instead. It works in Android.

Time Zone

Time zone is crucial. The definition of a day depends on the time zone.

Specify the desired time zone rather than rely on implicit default. Use proper time zone names, not the 3 or 4 letter codes.

If you really want to use the JVM’s default time zone, I suggest explicitly calling getDefault rather than rely on implicit default. Such reliance often causes confusion in date-time work.

Example Code

DateTimeZone timeZone = DateTimeZone.getDefault();  // Or DateTimeZone.forID( "Europe/Amsterdam" )
DateTime now = DateTime.now( timeZone );
DateTime todayStartOfDay = now.withTimeAtStartOfDay():
DateTime tomorrowStartOfDay = today.plusDays( 1 ).withTimeAtStartOfDay();

From there you can call the getMillis to get a long which you can use to construct a java.sql.Timestamp object for your SQL query.

SQL Logic

For Half-Open approach, we cannot use the SQL command BETWEEN. That command is inclusive on both comparators.

Instead, we need to write both our comparators, like this pseudo-code. Note >= versus < (no EQUALS SIGN).

SELECT * 
FROM some_table_ 
WHERE target >= todayStartOfDay.getMillis() 
AND target < tomorrowStartOfDay.getMillis();

Upvotes: 2

Evgeniy Dorofeev
Evgeniy Dorofeev

Reputation: 136062

I would do it this way

boolean isTimestampInToday(long timestamp) {
    Calendar c1 =  Calendar.getInstance();
    Calendar c2 =  Calendar.getInstance();
    c2.setTimeInMillis(timestamp);
    return c1.get(Calendar.DATE) == c2.get(Calendar.DATE);
}

Upvotes: 1

laune
laune

Reputation: 31300

Simple arithmetic operations can avoid the costlier string and formatting operations. Not sure whether David meant what I propose, but maybe code expresses it more clearly:

Calendar gc = new GregorianCalendar();
long toBeg = gc.getTimeInMillis();
toBeg -= toBeg % (24*60*60*1000);
long toEnd = toBeg + 24*60*60*1000;

long now = gc.getTimeInMillis();
if( toBeg <= now && now < toEnd ){
    System.out.println( "today" );
}

The values toBeg and toEnd, computed up front, can be used to run the query, according to the if. (To be precise, note the use of <= and <.)

Upvotes: 3

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79847

Use a PreparedStatement to select your data, with a where clause that includes

date_column between ? and ?

Once you've calculated the startDate and endDate - and the way you're doing it above is perfectly OK - set them to the parameters in the PreparedStatement and run it.

Upvotes: 3

Elliott Frisch
Elliott Frisch

Reputation: 201467

I would use a SimpleDateFormat and new Date(long) like so,

// four digit year, two digit month, two digit day. For example, 20140706
private final DateFormat sdf = new SimpleDateFormat("yyyyMMdd");
public boolean isTimestampInToday(final long timestamp){
  return sdf.format(new Date(timestamp)).equals(sdf.format(new Date()));
}

Upvotes: 3

Related Questions