CODEWITHSUNDEEP
javaandroidsqlitedatecalendar
user2847219
user2847219

Reputation: 555

Android SQLite query day of month

I'm trying to recover some data from SQLite db that have a date less than today. I got the day from the calendar, it does not give the desired result:

Calendar cal = Calendar.getInstance(); 
    int dayofmonth = cal.get(Calendar.DAY_OF_MONTH);
String sql = "SELECT SUM(filed1) FROM Table1 WHERE "+Table1.DATE+"<= '%"+dayofmonth+"'";

Upvotes: 1

Views: 457

Answers (2)

Basil Bourque
Basil Bourque

Reputation: 338875

tl;dr

String sql =                                                  // Create the text of your SQL statement to be executed.
    "SELECT SUM( number_col ) FROM tbl WHERE date_col <= '"   // Include the name of the date column to be compared. Should be part of your string literal.
    + LocalDate.now( ZoneId.of( "Pacific/Auckland" ) )        // Capture the current date per the wall-clock time used by the people of a particular region (a time zone). 
        .toString()                                           // Generate a string in standard ISO 8601 to represent the value of this `LocalDate` value.
    + "' ;"                                                   // A proper SQL statement is terminated with a semicolon. Omitted from the Question’s example code.
;

java.time

The modern approach uses java.time classes that supplanted troublesome legacy classes Date, Calendar, and SimpleDateFormat.

Your comment says you store date-only values in SQLite as text in standard ISO 8601 format: YYYY-MM-DD.

For a date-only value in Java, use the LocalDate class. The LocalDate class represents a date-only value without time-of-day and without time zone.

A time zone is crucial in determining a date. For any given moment, the date varies around the globe by zone. For example, a few minutes after midnight in Paris France is a new day while still “yesterday” in Montréal Québec.

If no time zone is specified, the JVM implicitly applies its current default time zone. That default may change at any moment, so your results may vary. Better to specify your desired/expected time zone explicitly as an argument.

Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 3-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!). 

ZoneId z = ZoneId.of( "America/Montreal" ) ;  
LocalDate today = LocalDate.now( z ) ;

If you want to use the JVM’s current default time zone, ask for it and pass as an argument. If omitted, the JVM’s current default is applied implicitly. Better to be explicit, as the default may be changed at any moment during runtime by any code in any thread of any app within the JVM.

ZoneId z = ZoneId.systemDefault() ;  // Get JVM’s current default time zone.

Or specify a date. You may set the month by a number, with sane numbering 1-12 for January-December. 

LocalDate ld = LocalDate.of( 1986 , 2 , 23 ) ;  // Years use sane direct numbering (1986 means year 1986). Months use sane numbering, 1-12 for January-December.

Or, better, use the Month enum objects pre-defined, one for each month of the year. Tip: Use these Month objects throughout your codebase rather than a mere integer number to make your code more self-documenting, ensure valid values, and provide type-safety.

LocalDate ld = LocalDate.of( 1986 , Month.FEBRUARY , 23 ) ;

Generate a string in standard format by simply calling toString.

String dateText = LocalDate.now( z ).toString() ;

Add to your SQL string. Your SQL string was incorrect in that the name of the column should be part of the text of the SQL. It should be part of your string literal but you excluded which makes no sense in the context of Java.

String sql = "SELECT SUM( number_col ) FROM tbl WHERE date_col <= '" + dateText + "' ;" ;

This renders a string such as:

SELECT SUM( number_col ) FROM tbl WHERE when_col <= '2018-01-23' ;

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

laalto
laalto

Reputation: 152847

Since the format of your data is YYYY-MM-DD i.e. the date components are ordered from most significant to least significant and zero padded, you can use regular string comparisons here.

First create a string in the same format representing today, e.g.

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String today = sdf.format(new Date());

then use that string in your SQL, e.g.

String sql = ... " WHERE " + Table1.DATE + " < ?";
String[] bindArgs = new String[] { today };
Cursor c = db.rawQuery(sql, bindArgs);

Upvotes: 4

Related Questions