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