2

My Android app has an SQLite database table with entries as follows:

USER_ID | NAME | MESSAGE | TIMESTAMP

where the timestamp is stored in a String in format:

"2012-10-10T03:14:32+0000"

I'm looking for a way to delete entries older than 3 days.

The table is likely to have less than 3,000 rows at a time.

I'm new to SQL and my question is how can I approach this in SQL for SQLite? Is there a less expensive way to do this in SQL than to iterate all posts in Java?

Pierre Rymiortz
  • 1,127
  • 3
  • 15
  • 31
  • 1
    Maybe this can help you http://stackoverflow.com/questions/2930768/how-to-compare-sqlite-timestamp-values – user Oct 10 '12 at 14:50

1 Answers1

3

You can have a look at this doc.

Another way is to use the fact that the date format you use can be sorted directly as a string (lexicographical order).

  1. In java code, you build the string representing the datetime threshold (Now minus 3 days)

  2. And just compare the strings in SQL in the where clause

    SELECT * FROM MyTable WHERE TIMESTAMP >= 'The date I built in Java'

Most of the time, Timestamps and datetimes are stored as ticks in SQLite, which involves int comparaison (much faster).

Askolein
  • 3,250
  • 3
  • 28
  • 40