CODEWITHSUNDEEP
javaandroidsqlsqliteandroid-database
Steven Smith
Steven Smith

Reputation: 121

SQLite delete rows by date

I have been searching and i still can not understand how this works.

I know that SQLite doesn't store Date type values and we have to format them to TEXT. I already did that, but my question is how do i delete them by date?

Because if I do DELETE * FROM TABLE WHERE COLUMN_DATE >= '09-05-2017' , I will be comparing Strings and not Dates, so i guess the comparison won't be right.

I have seen people doing these type of comparisons even if it is TEXT type. I doesn't make sense to me.

Upvotes: 1

Views: 3795

Answers (3)

Basil Bourque
Basil Bourque

Reputation: 339472

tl;dr

  • Store date-only values as strings in standard ISO 8601 fashion: YYYY-MM-DD
  • Query using ISO 8601 strings from the java.time classes, specifically LocalDate.

Example: 

LocalDate ld = LocalDate.of( 2017 , Month.JANUARY , 23 ) ; // Or `LocalDate.parse`, or `LocalDate.now()`.
String sql = "DELETE * FROM tbl WHERE col >= '" + ld.toString() + "' ;" ;

Details

Keep in mind that SQLite was never meant to be a heavy-duty database, just a step up from writing text to a file. So if you need sophisticated date-time support, you should switch to a more sophisticated database.

SQLite lacks proper data types, as you alluded in the Question. For storing date-time types, the documentation suggests three routes: (a) ISO 8601 strings, (b) Real number representing Julian day numbers (I don't recommend this), and (c) Integer for storing number of seconds since the epoch reference of first moment of 1970 in UTC as shown in Answer by Abakumov (though you should NOT be using Date class shown there).

You seem to be tracking date-only values, without a time of day. So I would use the ISO 8601 for text: YYYY-MM-DD.

The java.time types in Java use the standard ISO 8601 formats by default when parsing/generating date-time values. The date-only class in Java is LocalDate, lacking a time-of-day and lacking a time zone.

ZoneId z = ZoneId.of( "Africa/Tunis" ) ;
LocalDate today = LocalDate.now( z ) ;

Specify a date.

LocalDate ld = LocalDate.parse( "2017-09-05" ) ;  // Standard ISO 8601 format for input: YYYY-MM-DD.

The advantage of standard ISO 8601 format is that alphabetical order happens to also be chronological order.

So you can do text-based comparison using greater-than/less-than logic.

String sql = "DELETE * FROM tbl WHERE col >= '" + ld.toString() + "' ;" ;

Another big advantage over the other two approaches:

  • Trying to represent date-time with a fractional real number is messy.
  • Storing a count-from-epoch makes debugging and troubleshooting difficult as a human cannot readily read the meaning of the data.

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

Upvotes: 0

Alexander Abakumov
Alexander Abakumov

Reputation: 14569

A much more efficient and simpler in terms of SQLite side date comparisons/arithmetic is to store dates as INTs in SQLite.

Date in Android is effectively just a container for milliseconds-since-the-Epoch (milliseconds since Jan 1st 1970 at midnight UTC) which is time zone independent.

INT in SQLite is 1, 2, 4, 8 byte data type automatically chosen depending on values in the column.

In order to convert a Java Date into a SQLite INT value when storing data in DB, use getTime() method:

Date javaDate = ...;
long sqlDate = date.getTime();

To retrieve a date from SQLite, use Cursor's getLong() method and pass it to Date(long) constructor:

SQLiteDatabase db = ...;
Cursor c = db.query(..);
long sqlDate = c.getLong(...);
Date javaDate = new Date(sqlDate);

Upvotes: 0

varro
varro

Reputation: 2482

It will work if you use the canonical text format and use correct syntax, e.g.,

DELETE FROM TABLE WHERE COLUMN_DATE >= '2017-05-09'

Upvotes: 5

Related Questions