What is the correct way to insert datetimes into sqlite from java?

Question

I'm seeing unexpected results when I try to use sqlite3 to query dates inserted via JDBC. This java code:

try (Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db")) {
    conn.setAutoCommit(true);
    try (Statement statement = conn.createStatement()) {
        statement.executeUpdate("create table dates (date DATETIME);");
    }

    try (PreparedStatement insert = conn
            .prepareStatement("insert into dates values (?)")) {
        insert.setTimestamp(1, Timestamp.valueOf(LocalDateTime.now()));
        insert.executeUpdate();
    }
}

Doesn't seem to insert a correct datetime - when I query it via sqlite3, I can't get a human-readable value:

sqlite3 test.db "select date, datetime(date, 'unixepoch') from dates;"
1507819362296|

This makes sense, since the sqlite docs say that the valid unix epoch range is "-62167219200 through 106751991167" (https://sqlite.org/lang_datefunc.html).

How should I be using dates with JDBC and Sqlite?

Answer 1

@Mark W We have been putting dates in the sqlite DB as Strings but they are not pretty looking strings like this 10-18-2017 they go in like this 10182017 and YES they are all 8 characters long. But when you pull them out they get all dressed up and transform back to this 10-18-2017. We have tested our searches and all seems to work as desired with the SELECT * FROM TABLE_NAME WHERE Col_Date >= "from" AND Col_Date <= "to" Below is the code for making the date not so pretty

    public void findByDate(View view){
    use= false;
    // custom dialog /* THIS R.style.DatePickerThem is mandatory to use res/values/styles */
    final Dialog dialog = new Dialog(MainActivity.this,R.style.DatePickerTheme);
    dialog.setContentView(R.layout.datepickerview);
    dialog.setTitle("");
    DatePicker picker = dialog.findViewById(R.id.datePicker);
    final Calendar c = Calendar.getInstance();
    mYear = c.get(Calendar.YEAR);
    mMonth = c.get(Calendar.MONTH);
    mDay = c.get(Calendar.DAY_OF_MONTH);
    //picker.updateDate(2017, 10, 13);//year month day
    picker.updateDate(mYear, mMonth, mDay);// Keeps Calendar initial view what ever today is!

    System.out.println("Month " + mMonth+1);

    picker.init(c.get(Calendar.YEAR), c.get(Calendar.MONTH), c.get(Calendar.DAY_OF_MONTH), new DatePicker.OnDateChangedListener() {
        //picker.init(Integer.valueOf(mMonth),Integer.valueOf(mDay),Integer.valueOf(mYear), new DatePicker.OnDateChangedListener() {
        @Override
        public void onDateChanged(DatePicker picker, int year, int monthOfYear, int dayOfMonth) {

            if (etFromDate.getText().toString().isEmpty()) {
                System.out.println("I am Not Empty");

                //etFromDate.setText(String.valueOf(monthOfYear+1 ) + String.valueOf(dayOfMonth) + String.valueOf(year));
                //v1 = Long.valueOf(etFromDate.getText().toString());
                //etFromDate.setText(String.valueOf(v1));
                //String theDATE = (monthOfYear+1) + "-" + (dayOfMonth) + "-" + (year);
                String searchFrom = (String.valueOf(monthOfYear+1))+(String.valueOf(dayOfMonth))+String.valueOf(year);
                if(searchFrom.length()==7){
                    StringBuilder str = new StringBuilder(searchFrom);
                    str.insert(2, '0');
                    etFromDate.setText(str);
                }else if (searchFrom.length() == 6){
                    StringBuilder str = new StringBuilder(searchFrom);
                    str.insert(0,'0');
                    str.insert(2,'0');
                    etFromDate.setText(str);
                }else {
                    etFromDate.setText(searchFrom);
                }


                //setDATE = etFromDate.getText().toString();
                dialog.dismiss();

Then here is a way to look at the same string 10182017 as a high fashion date

        helper = new DBHelper(this);
    dbList = new ArrayList<>();
    dbList = helper.getDataFromDB();

    if (dbList.size() > 0 && tORf.equalsIgnoreCase("false")) {

        btnSave.setVisibility(View.INVISIBLE);
        String NVrowid = String.valueOf(dbList.get(position).getRowid());
        String NVstation = dbList.get(position).getStation_Name();
        String NVpurchasedate = dbList.get(position).getDate_of_Purchase();
        String s = NVpurchasedate;
        String month = s.substring(0, 2);
        String day = s.substring(2, 4);
        String year = s.substring(4, 8);
        String date3 = month + "-" + day + "-" + year;
        String NVcost = dbList.get(position).getGas_Cost();
        etStation.setText(NVstation);
        etPurchaseDate.setText(date3);
        //etPurchaseDate.setText(NVpurchasedate);
        etCost.setText(NVcost);
    }

Answer 2

There is no formal date or timestamp type in SQLite, rather these are represented by strings. So if you wanted to insert the current timestamp into your table you could try the following:

try (Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db")) {
    conn.setAutoCommit(true);
    try (Statement statement = conn.createStatement()) {
        statement.executeUpdate("CREATE TABLE dates (date TEXT);");
    }

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    Timestamp timestamp = new Timestamp(System.currentTimeMillis());
    String ts = sdf.format(timestamp);

    try (PreparedStatement ps = conn
            .prepareStatement("INSERT INTO dates (date) VALUES (?)")) {
        ps.setString(1, ts);
        ps.executeUpdate();
    }
}