Insert datetime.now SQLite in Android

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 Datatypes in SQLite for more.

So using the code above and :-

insertScannedCashCard("TheCard",new byte[]{0,1,2,3,4,5,6,7,8,9});
insertScannedCashCardV2("Next Card",new byte[]{9,8,7,6,5,4,3,2,1,0});

Then the result (as per Android Studio's App Inspection aka Database Inspector) you get :-