How delete entries in sqlite database based on timestamp on android

Question

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?

Maybe this can help you stackoverflow.com/questions/2930768/… — user
– user, Commented Oct 10, 2012 at 14:50

Askolein · Accepted Answer · 2012-10-10 14:50:39Z

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

In java code, you build the string representing the datetime threshold (Now minus 3 days)
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).

answered Oct 10, 2012 at 14:50

Askolein

3,4283 gold badges33 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How delete entries in sqlite database based on timestamp on android

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related