CODEWITHSUNDEEP
androidsqlsqlite
Pierre Rymiortz
Pierre Rymiortz

Reputation: 1127

How delete entries in sqlite database based on timestamp on android

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?

Upvotes: 2

Views: 1670

Answers (1)

Askolein
Askolein

Reputation: 3378

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).

Upvotes: 3

Related Questions