sqlite database default time value 'now'
according to dr. hipp in a recent list post:
CREATE TABLE whatever(
....
timestamp DATE DEFAULT (datetime('now','localtime')),
...
);
This is a full example based on the other answers and comments to the question. In the example the timestamp (created_at
-column) is saved as unix epoch UTC timezone and converted to local timezone only when necessary.
Using unix epoch saves storage space - 4 bytes integer vs. 24 bytes string when stored as ISO8601 string, see datatypes. If 4 bytes is not enough that can be increased to 6 or 8 bytes.
Saving timestamp on UTC timezone makes it convenient to show a reasonable value on multiple timezones.
SQLite version is 3.8.6 that ships with Ubuntu LTS 14.04.
$ sqlite3 so.db
SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
sqlite> .headers on
create table if not exists example (
id integer primary key autoincrement
,data text not null unique
,created_at integer(4) not null default (strftime('%s','now'))
);
insert into example(data) values
('foo')
,('bar')
;
select
id
,data
,created_at as epoch
,datetime(created_at, 'unixepoch') as utc
,datetime(created_at, 'unixepoch', 'localtime') as localtime
from example
order by id
;
id|data|epoch |utc |localtime
1 |foo |1412097842|2014-09-30 17:24:02|2014-09-30 20:24:02
2 |bar |1412097842|2014-09-30 17:24:02|2014-09-30 20:24:02
Localtime is correct as I'm located at UTC+2 DST at the moment of the query.
i believe you can use
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
t TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
);
as of version 3.1 (source)
It's just a syntax error, you need parenthesis: (DATETIME('now'))
If you look at the documentation, you'll note the parenthesis that is added around the 'expr' option in the syntax.