How to Insert Datetime Value into a SQLite Database

How do I insert datetime value into a SQLite database?

The format you need is:

'2007-01-01 10:00:00'

i.e. yyyy-MM-dd HH:mm:ss

If possible, however, use a parameterised query as this frees you from worrying about the formatting details.

How to insert datetime into sqlite3 database using Python?

You could use :-

rows = db.execute("INSERT INTO test (time) VALUES (datetime('now'))")

The column type is basically irrelevant as in SQLite you can store any type of value in any type of column.

With the exception of the special rowid column or an alias of the rowid column (if the column is defined using INTEGER PRIMARY KEY then it is an alias of the rowid column (or with the AUTOINCREMENT keyword)). An alias of the rowid column must be an integer up to 64 bit signed.

Working Example

import sqlite3
drop_sql = "DROP TABLE IF EXISTS test"
crt_sql = "CREATE TABLE IF NOT EXISTS test (time NUMERIC, time2 TEXT, time3 BLOB, time4 REAL, time5 INTEGER )"
db = sqlite3.connect("test.db")
rows = db.execute(drop_sql)
rows = db.execute(crt_sql)
rows = db.execute("INSERT INTO test VALUES(datetime('now'),datetime('now'),datetime('now'),datetime('now'),datetime('now'))")
cursor = db.cursor()
cursor.execute("SELECT * FROM test")
for row in cursor:
    print("Time is " + row[0], "\nTime2 is " + row[1],"\nTime3 is " + row[2], "\nTime4 is " + row[3],"\nTime5 is " + row[4])
db.commit()
db.close()

Result :-

Time is 2019-07-13 08:59:28 
Time2 is 2019-07-13 08:59:28 
Time3 is 2019-07-13 08:59:28 
Time4 is 2019-07-13 08:59:28 
Time5 is 2019-07-13 08:59:28

Inserting date into SQLite table

You need to use single quotation ' contain your date string.

date(timestring, modifier, modifier, ...)

So you need to pass DateTime string be the parameter.

insert into student values ('S01', 'Michael', 'Jordan', 'M', 'FINC',
                            '1962-03-10');
insert into student values ('S02', 'Charles', 'Barkley', 'M', null, 
                            '1964-09-12');

or just use Date string

insert into student values ('S01', 'Michael', 'Jordan', 'M', 'FINC',
                            '1962-03-10');
insert into student values ('S02', 'Charles', 'Barkley', 'M', null, 
                            '1964-09-12');

Query #1

select * from student;

| std_code | std_fname | std_lname | std_gend | maj_code | std_dob    |
| -------- | --------- | --------- | -------- | -------- | ---------- |
| S01      | Michael   | Jordan    | M        | FINC     | 1962-03-10 |
| S02      | Charles   | Barkley   | M        |          | 1964-09-12 |

---

View on DB Fiddle

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

Inserting datetime into a SQLite Database

You are trying to convert a Python method object into a string, which is defaulting to its representation, which you don't want. Try this instead:

from datetime import datetime
time = datetime.now().strftime("%B %d, %Y %I:%M%p")
cur.execute("insert into apple values (?,?,?)",(price,time,date))
con.commit()

#prints:
>>>('132.75', "Apr 27, 2015 09:38AM", 'Apr 27, 2015')

---

Related Topics