sqlite database default time value 'now'

Question

i believe you can use

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

as of version 3.1 (source)

according to dr. hipp in a recent list post:

CREATE TABLE whatever(
     ....
     timestamp DATE DEFAULT (datetime('now','localtime')),
     ...
);

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.

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.

It may be better to use REAL type, to save storage space.

Quote from 1.2 section of Datatypes In SQLite Version 3

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

CREATE TABLE test (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    t REAL DEFAULT (datetime('now', 'localtime'))
);

see column-constraint .

And insert a row without providing any value.

INSERT INTO "test" DEFAULT VALUES;