Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sqlite

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

create table tbl1(id int primary key, dt datetime default current_timestamp); 
like image 816
NobodyMan Avatar asked Apr 10 '10 18:04

NobodyMan


People also ask

How do I create a date column in SQLite?

First, create a new table named datetime_real . Second, insert the “current” date and time value into the datetime_real table. We used the julianday() function to convert the current date and time to the Julian Day. Third, query data from the datetime_real table.

How do I change the default value in SQLite?

To set default value for a column in SQLite, use DEFAULT :CREATE TABLE customers ( id INTEGER PRIMARY KEY, store_code TEXT DEFAULT "store1" NOT NULL, name TEXT );

How is datetime stored in SQLite?

Date and Time Datatype. SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values: TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.


1 Answers

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

like image 94
Sky Sanders Avatar answered Oct 06 '22 11:10

Sky Sanders