I'm confused about how can I insert DateTime.now in the SQLite database, I want to use and insert DateTime, the most samples used ContentValues but I want to insert through using SQLIteStatement like the example below, is that possible?

Declaring database exist The DateTime is on the last column

sqLiteHelper.queryData("CREATE TABLE IF NOT EXISTS CgList(Id INTEGER PRIMARY KEY AUTOINCREMENT, cash_card_actual_no VARCHAR, hh_number VARCHAR,series_number VARCHAR, cc_image BLOB , id_image BLOB, cash_card_scanned_no VARCHAR , card_scanning_status VARCHAR, date_insert DATETIME DEFAULT CURRENT_TIMESTAMP)");

I want to add another column of DateTime when inserting data it will automatically save the date.

public void insertScannedCashCard(String scannedCashCard,byte[] cc_image){

    try {
        SQLiteDatabase database = getWritableDatabase();
        String sql = "INSERT INTO CgList VALUES (NULL,?,?,?,?,?,?,0)";
        SQLiteStatement statement = database.compileStatement(sql);
        statement.clearBindings();
        statement.bindString(1, "");
        statement.bindString(2, "");
        statement.bindString(3, "");
        statement.bindBlob(4, cc_image);
        statement.bindString(5,"");
        statement.bindString(6, scannedCashCard);
        statement.executeInsert();
    }
    catch(Exception e){
        Log.v(TAG,e.toString());
    }
}

This is what I've tried

    public void insertScannedCashCard(String scannedCashCard,byte[] cc_image){

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String strDate = sdf.format(new Date());
    try {
        SQLiteDatabase database = getWritableDatabase();
        String sql = "INSERT INTO CgList VALUES (NULL,?,?,?,?,?,?,0,?)";
        SQLiteStatement statement = database.compileStatement(sql);
        statement.clearBindings();
        statement.bindString(1, "");
        statement.bindString(2, "");
        statement.bindString(3, "");
        statement.bindBlob(4, cc_image);
        statement.bindString(5,"");
        statement.bindString(6, scannedCashCard);
        statement.bindDateeeeee(7, strDate); // don't know the syntax of date
        statement.executeInsert();
    }
    catch(Exception e){
        Log.v(TAG,e.toString());
    }
}

CodePudding user response：

You can use either :-

public void insertScannedCashCard(String scannedCashCard,byte[] cc_image){

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String strDate = sdf.format(new Date());
    try {
        //SQLiteDatabase database = this.getWritableDatabase();
        String sql = "INSERT INTO CgList VALUES (NULL,?,?,?,?,?,?,?,?)";
        SQLiteStatement statement = database.compileStatement(sql);
        statement.clearBindings();
        statement.bindString(1, "");
        statement.bindString(2, "");
        statement.bindString(3, "");
        statement.bindBlob(4, cc_image);
        statement.bindString(5,"");
        statement.bindString(6, scannedCashCard);
        statement.bindString(7, strDate); // don't know the syntax of date
        statement.executeInsert();
    }
    catch(Exception e){
        Log.v(TAG,e.toString());
    }
}

• i.e. the date is a String.

or if you want the DEFAULT value to be used i.e. CURRENT_TIMESTAMP then use an insert that specifies only the columns not to use the default (i.e. omit the column(s) to use the defined DEFAULT).

So to use the DEFAULT value for both the id and the date_insert columns you could use :-

public void insertScannedCashCardV2(String scannedCashCard,byte[] cc_image){

    try {
        //SQLiteDatabase database = this.getWritableDatabase();
        String sql = "INSERT INTO CgList (cash_card_actual_no,hh_number,series_number,cc_image,id_image,cash_card_scanned_no,card_scanning_status) VALUES (?,?,?,?,?,?,0)";
        SQLiteStatement statement = database.compileStatement(sql);
        statement.clearBindings();
        statement.bindString(1, "");
        statement.bindString(2, "");
        statement.bindString(3, "");
        statement.bindBlob(4, cc_image);
        statement.bindString(5,"");
        statement.bindString(6, scannedCashCard);
        statement.executeInsert();
    }
    catch(Exception e){
        Log.v(TAG,e.toString());
    }
}

In regard to types. SQLite is unique or at least unusual in how it handles columns. The type affinity will be one of :-

• TEXT
• INTEGER
• BLOB
• REAL
• NUMERIC

However, you can specify virtually anything (some restrictions apply) or even nothing and SQLite will assign a type affinity according to a set of rules.

SQLite has no specific date/time types but specifying DATETIME as a column type results in that column having the type affinity of NUMERIC according to the type affinity rules (i.e. the last catch-all rule is applied).

• e.g. cash_card_actual_no VARCHAR because the first rule that is matched is the 2nd rule:-
• If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
• you can even have a column type of rumplestiltskin, the rules drop through and the type affinity is the catch-all NUMERIC.

However, SQLite's flexibility allows, with one exception, any column to store any type of data. The exception is the special rowid column or an alias of the rowid column. An alias of the rowid column is where you specify INTEGER PRIMARY KEY (with or without AUTOINCREMENT), in which case the value MUST be an integer or NULL, In the case of NULL the value will be determined (automatically generated) by SQLite (typically 1 greater than the highest value, AUTOINCREMENT applies a further rule in that the value MUST be greater than the highest value that has ever been used)

• see