How to create a datetime column with default value in sqlite3?
SqliteSqlite Problem Overview
Is there a way to create a table in sqlite3 that has a datetime column that defaults to 'now'?
The following statement returns a syntax error:
create table tbl1(id int primary key, dt datetime default datetime('now'));
Update: Here's the correct ddl courtesy of https://stackoverflow.com/users/242897/sky-sanders">Sky Sanders:
create table tbl1(id int primary key, dt datetime default current_timestamp);
Sqlite Solutions
Solution 1 - Sqlite
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".
Solution 2 - Sqlite
... default (datetime(current_timestamp))
The expression following default
must be in parentheses. This form is useful if you want to perform date arithmetic using SQLite date and time functions or modifiers.
Solution 3 - Sqlite
CURRENT_TIMESTAMP
is a literal-value just like 'mystring'
column-constraint:
literal-value:
Solution 4 - Sqlite
you can use the following query for using current date value in your table
create table tablename (date_field_name Created_on default CURRENT_DATE);