Sqlite Database Default Time Value 'Now'

sqlite database default time value 'now'

i believe you can use

CREATE TABLE test (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  t TIMESTAMP
  DEFAULT CURRENT_TIMESTAMP
);

as of version 3.1 (source)

Create a datetime column with default current time in sqlite

Try this:

public DateTime test { get; set; } = DateTime.Now;

This will only work if exactly as above. If you write custom get and set then I would think you can set the default for the backing field, e.g.:

DateTime _time = DateTime.Now;
public DateTime Time { 
    get{
        return _time;
    } 
    set {
        if (_time != value) {
            _time = value;

        }
    } 
}

How to set default date value in SQLite during table creation

I think you are merely missing the two ''s in:

collected_date TEXT DEFAULT '{}'

Code below runs without error:

from datetime import datetime
import sqlite3

conn = sqlite3.connect(':memory:')
cur = conn.cursor()

current_date = datetime.now().strftime('%Y %m %d')
command = """CREATE TABLE tally_file (
        collected_date TEXT DEFAULT '{}'
        )""".format(current_date)

cur.execute(command)
conn.commit()
conn.close()

There is a similar answer here

In general, it is now such a good decision to default the database with today's date at schema creation, un less it is a very speciifc task (eg. the database lives very shortly). You get a cleaner result with default value on insert, I guess.

Sqlite - default timestamp to be `now + a few days`

Yes it can be done as in the following example:

sqlite> create table foo (i int, j text default (datetime('now', '+5 days')));
sqlite> insert into foo (i) values (1);
sqlite> select * from foo;
1|2012-04-11 07:49:04
sqlite> insert into foo (i) values (2);
sqlite> select * from foo;
1|2012-04-11 07:49:04
2|2012-04-11 07:49:14

If you only want to store the date part, use date instead of datetime. Here I use datetime to show that the default expression is evaluated when inserting in the table, not when the table is created.

sqlite3.OperationalError: default DATE value of column is not constant

Use single quotes (') for the datetime options. As mentioned in the comments, they will have to be escaped (because the query is delimited with single quotes).

How to create a datetime column with default value in sqlite3?

Try this:

create table tbl1(id int primary key, dt datetime default current_timestamp);

Background:

The DEFAULT constraint specifies a
default value to use when doing an
INSERT. The value may be NULL, a
string constant, a number, or a
constant expression enclosed in
parentheses. The default value may
also be one of the special
case-independant keywords
CURRENT_TIME, CURRENT_DATE or
CURRENT_TIMESTAMP. If the value is
NULL, a string constant or number, it
is inserted into the column whenever
an INSERT statement that does not
specify a value for the column is
executed. If the value is
CURRENT_TIME, CURRENT_DATE or
CURRENT_TIMESTAMP, then the current
UTC date and/or time is inserted into
the columns. For CURRENT_TIME, the
format is HH:MM:SS. For CURRENT_DATE,
YYYY-MM-DD. The format for
CURRENT_TIMESTAMP is "YYYY-MM-DD
HH:MM:SS".

From http://www.sqlite.org/lang_createtable.html

How to have an automatic timestamp in SQLite?

Just declare a default value for a field:

CREATE TABLE MyTable(
    ID INTEGER PRIMARY KEY,
    Name TEXT,
    Other STUFF,
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

However, if your INSERT command explicitly sets this field to NULL, it will be set to NULL.

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

---

Related Topics