How can I use strftime function with question mark in Java

Issue

I am planning to add 'Date' objects into the SQLite database. However, I am getting an error about the insertion being null. The error is this

org.sqlite.SQLiteException: [SQLITE_CONSTRAINT_NOTNULL]  A NOT NULL constraint failed (NOT NULL constraint failed: dates.Tarih)
    at org.sqlite.core.DB.newSQLException(DB.java:909)
    at org.sqlite.core.DB.newSQLException(DB.java:921)
    at org.sqlite.core.DB.execute(DB.java:825)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.execute(JDBC3PreparedStatement.java:53)

This is my code. I suspect from the question marks. Because when I remove them and place them with 'now'. It actually works. But, the following code throws the above error.

Insert method

public static void insert(Date date, Date date2)  {
       try{
           System.out.println(" date:"+date.toString());

           String query = "insert into dates(Tarih,Tarih2) values(strftime('%d-%m-%Y',?), strftime('%d-%m-%Y',?))";
           pst=conn.prepareStatement(query);
           pst.setObject(1,date);
           pst.setObject(2,date2);

           pst.execute();

       }catch (SQLException e){
           e.printStackTrace();
       }


    }

Solution

Probably you have defined the column Tarih as NOT NULL and your code is trying to insert a null value in the table.

The reason that you get null from strftime() is because you don't pass a valid date for SQLite.
For SQLite valid dates/datetimes are either strings in the format yyyy-MM-dd hh:mm:ss, or integer unix epoch times or floating point numbers representing julian days.

What you pass are Date objects and this is your mistake.

One way to solve the problem is to extract from each of the Date objects an integer representing unix epoch time and pass that to strftime():

public static void insert(Date date, Date date2)  {
    try{
        long d = date.toInstant().toEpochMilli() / 1000; 
        long d2 = date2.toInstant().toEpochMilli() / 1000;

        String query = "insert into dates(Tarih,Tarih2) values(strftime('%d-%m-%Y', ?, 'unixepoch'), strftime('%d-%m-%Y', ?, 'unixepoch'))";
        pst=conn.prepareStatement(query);
        pst.setLong(1, d);
        pst.setLong(2, d2);
        pst.execute();
    } catch (SQLException e){
        e.printStackTrace();
    }
}

Answered By - forpas