Cannot get results Between dates in SQLite using Java PreparedStatement

Question

I have been trying to get data from a table 'produccion' that has a date field called 'dia' using a between statement.

The SQL sentence I was aimig for is:

SELECT * FROM produccion WHERE julianday(dia) BETWEEN julianday('2023-11-01') AND julianday('2023-11-31');

The java code is quite hard to read, as I tried making the code valid for everything. I have enforced foreign keys on, but I am not going outside of the table so it should not matter.

    private ResultSet resultado;
    private PreparedStatement prepSentencia;

    public ResultSet pedirDatos (String[] campos, String tabla, String[][] join, String[] condicionColumna, Object[] condicionValor, String operador, String orden) throws SQLException {

        String select = "SELECT ";
        resultado=null;

        if (campos != null && tabla !=null) {
            for (int x=0; x<campos.length;x++) {
                select = select + campos[x];
                if (x+1<campos.length)
                    select = select + ", ";
            }
            select = select + " FROM "+tabla;
            if (join != null) {
                for (int tupla=0; tupla<join.length; tupla++) {
                    select = select + " INNER JOIN " + join[tupla][0] + " ON "+ tabla + "." + join[tupla][1] + " = " + join[tupla][0] + "." + join[tupla][2];
                }
            }
            // This is the where part
            if (condicionColumna!=null) {
                select = select + " WHERE " ;
                for (int i =0; i<condicionColumna.length;i++) {
                    // Usual where
                    if (condicionColumna[i] != "" && condicionColumna.length==condicionValor.length)
                        select = select + condicionColumna[i]+" = ? ";
                    // Between code goes trough here
                    else if (condicionColumna[i] != "" && condicionColumna.length==1 && condicionValor.length==2)
                        select = select+ condicionColumna[i]+" BETWEEN julianday(?) AND julianday(?) ";
                    // Verifico si hay más rotaciones y añado el and o el OR
                    if (((i+1)<condicionColumna.length))
                        select = select + " "+operador+" ";
                }
            }

            // Checks for orders
            if (!(orden==null || orden==""))
                select = select + " ORDER BY ? ";
            // Create the sentence
            prepSentencia = conexion.prepareStatement(select+";", PreparedStatement.RETURN_GENERATED_KEYS);
            
            // Fill the data
            if (condicionValor!=null ) {
                for (int i =0; i<condicionValor.length;i++) {
                    if (condicionValor[i] != "")
                        prepSentencia.setObject((i+1),condicionValor[i]);
                    if (((i+1)==condicionValor.length) && !(orden==null || orden==""))
                        prepSentencia.setString(i+2,orden);
                }
            } else if (!(orden==null || orden==""))
                prepSentencia.setString(1,orden);
            resultado = prepSentencia.executeQuery();
        }
        else
            throw new SQLException ("I need input data");
        return resultado;
    }

Data input:

pedirDatos(new String[] {"*"}, "produccion",null,new String[] {"julianday(dia)"},new String[] {"'2023-11-01'","'2023-11-31'"},"AND",null);

The table is formed before with a statement as:

regularstatement.execute("CREATE TABLE IF NOT EXISTS produccion(\n"
        + " id_produccion INTEGER PRIMARY KEY,\n"
        + " dia TEXT NOT NULL,\n"
        + " hora TEXT NOT NULL,\n"
        + " cantidad REAL NOT NULL,\n"
        + " num_animales INTEGER NOT NULL,\n"
        + " media_por_animal INTEGER,\n"
        + " incidencias TEXT \n"
        + ");");

I know there is date field in SQLite, so i made it text.

I was debugging and I found that the program goes properly trough all the code, and debugging the resultset contains the stmt part with this info:

SELECT * FROM produccion WHERE julianday(dia, 'utc') BETWEEN julianday(?) AND julianday(?) ; parameters=['2023-11-01', '2023-11-31']

I have verified that i have two rows in the table with the date 2023-11-17

So it seems that I am inyecting the data properly in the prepared statement, and I feel like the sentence is correct. I have no idea why i am not getting results as I have been using this with success in the rest of the program.

I have also tried using julianday(data,'utc'), strftime(), date() and using direclty the dates. I looked at https://sqlite.org/lang_datefunc.html and could not make it work.

What kills me, is that using an online viewer (https://inloop.github.io/sqlite-viewer/) i can make the SQL work, but when running java it returns no data when it should.

Edit: to clarify, I started having this problem when I tried SELECT * FROM 'produccion' WHERE dia BETWEEN '2023-11-01' AND '2023-11-30' and did not work.

Edit2: corrected julianday as forpas commented. Still no data returned. Added proof that there is data in the database file link to image

Answer 1

The ? placeholders in your query are replaced by the parameters julianday('2023-11-01') and julianday('2023-11-31') like this:

SELECT * FROM produccion 
WHERE julianday(dia) BETWEEN 'julianday('2023-11-01')' AND 'julianday('2023-11-31')';

(with the single quotes properly escaped) because you pass them as strings.

Instead you should write the query:

SELECT * FROM produccion 
WHERE julianday(dia) BETWEEN julianday(?) AND julianday(?);

and pass the parameters '2023-11-01' and '2023-11-30' (not '2023-11-31' because there are only 30 days in November).

But, why do you use julianday() in your query?
If your dates are properly formated as YYYY-MM-DD it is simpler to write the query like this:

SELECT * FROM produccion 
WHERE dia BETWEEN ? AND ?;

Edit by @PabloIglesias: I will clarify here as the coment with the solution is not visible by default and this leads to solution but can be missunderstood.

In order to pass a SQL parameter as '2023-11-01' when using putString, we should use putString("2023-11-01") and not putString("'2023-11-01'") as the method automatically includes the ', and was the original mistake made.