sqlite database default time value now

Question

Is it possible in a sqlite database to craete a table that has a timestamp column that default to DATETIME  now      Like this   CREATE TABLE test       id INTEGER PRIMARY KEY AUTOINCREMENT       t TIMESTAMP DEFAULT DATETIME  now        This gives an error    How to resolve

User · Answer

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.

User · Answer

i believe you can use  CREATE TABLE test     id INTEGER PRIMARY KEY AUTOINCREMENT    t TIMESTAMP   DEFAULT CURRENT TIMESTAMP      as of version 3 1  source

User · Answer

This alternative example stores the local time as Integer to save the 20 bytes  The work is done in the field default  Update-trigger  and View  strftime must use   s   single-quotes  because   s   double-quotes  threw a  Not Constant  error on me   Create Table Demo      idDemo    Integer    Not Null Primary Key AutoIncrement    DemoValue Text       Not Null Unique    DatTimIns Integer 4  Not Null Default  strftime   s   DateTime  Now    localtime     -- get Now UTC  convert to local  convert to string Unix Time  store as Integer 4     DatTimUpd Integer 4      Null     Create Trigger trgDemoUpd After Update On Demo Begin   Update Demo Set     DatTimUpd                             strftime   s   DateTime  Now    localtime     -- same as DatTimIns   Where idDemo   new idDemo  End   Create View If Not Exists vewDemo As Select -- convert Unix-Times to DateTimes so not every single query needs to do so    idDemo    DemoValue    DateTime DatTimIns   unixepoch   As DatTimIns -- convert Integer 4   treating it as Unix-Time     DateTime DatTimUpd   unixepoch   As DatTimUpd --   to YYYY-MM-DD HH MM SS From Demo   Insert Into Demo  DemoValue  Values   One                         -- activate the field Default -- WAIT a few seconds --     Insert Into Demo  DemoValue  Values   Two                         -- same thing but with Insert Into Demo  DemoValue  Values   Thr                         --   later time values  Update Demo Set DemoValue   DemoValue      Upd  Where idDemo   1  -- activate the Update-trigger  Select   From    Demo                                             -- display raw audit values idDemo  DemoValue  DatTimIns   DatTimUpd ------  ---------  ----------  ---------- 1       One Upd    1560024902  1560024944 2       Two        1560024944 3       Thr        1560024944  Select   From vewDemo                                             -- display automatic audit values idDemo  DemoValue  DatTimIns            DatTimUpd ------  ---------  -------------------  ------------------- 1       One Upd    2019-06-08 20 15 02  2019-06-08 20 15 44 2       Two        2019-06-08 20 15 44 3       Thr        2019-06-08 20 15 44

User · Answer

according to dr  hipp in a recent list post   CREATE TABLE whatever                 timestamp DATE DEFAULT  datetime  now   localtime

User · Answer

i believe you can use  CREATE TABLE test     id INTEGER PRIMARY KEY AUTOINCREMENT    t TIMESTAMP   DEFAULT CURRENT TIMESTAMP      as of version 3 1  source

User · Answer

It is syntax error because you did not write parenthesis   if you write      Select datetime  now      then it will give you utc time but if you this write it query then you must add parenthesis before this    so  datetime  now    for UTC Time    for local time same   Select datetime  now   localtime     for query     datetime  now   localtime

User · Answer

If you want millisecond precision  try this  CREATE TABLE my table       timestamp DATETIME DEFAULT  strftime   Y- m- dT H  M  fZ    now        This will save the timestamp as text  though

User · Answer

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

User · Answer

i believe you can use  CREATE TABLE test     id INTEGER PRIMARY KEY AUTOINCREMENT    t TIMESTAMP   DEFAULT CURRENT TIMESTAMP      as of version 3 1  source

User · Answer

If you want millisecond precision  try this  CREATE TABLE my table       timestamp DATETIME DEFAULT  strftime   Y- m- dT H  M  fZ    now        This will save the timestamp as text  though

User · Answer

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

User · Answer

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

User · Answer

It is syntax error because you did not write parenthesis   if you write      Select datetime  now      then it will give you utc time but if you this write it query then you must add parenthesis before this    so  datetime  now    for UTC Time    for local time same   Select datetime  now   localtime     for query     datetime  now   localtime

User · Answer

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

User · Answer

according to dr  hipp in a recent list post   CREATE TABLE whatever                 timestamp DATE DEFAULT  datetime  now   localtime

User · Answer

according to dr  hipp in a recent list post   CREATE TABLE whatever                 timestamp DATE DEFAULT  datetime  now   localtime

User · Answer

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.

User · Answer

This alternative example stores the local time as Integer to save the 20 bytes  The work is done in the field default  Update-trigger  and View  strftime must use   s   single-quotes  because   s   double-quotes  threw a  Not Constant  error on me   Create Table Demo      idDemo    Integer    Not Null Primary Key AutoIncrement    DemoValue Text       Not Null Unique    DatTimIns Integer 4  Not Null Default  strftime   s   DateTime  Now    localtime     -- get Now UTC  convert to local  convert to string Unix Time  store as Integer 4     DatTimUpd Integer 4      Null     Create Trigger trgDemoUpd After Update On Demo Begin   Update Demo Set     DatTimUpd                             strftime   s   DateTime  Now    localtime     -- same as DatTimIns   Where idDemo   new idDemo  End   Create View If Not Exists vewDemo As Select -- convert Unix-Times to DateTimes so not every single query needs to do so    idDemo    DemoValue    DateTime DatTimIns   unixepoch   As DatTimIns -- convert Integer 4   treating it as Unix-Time     DateTime DatTimUpd   unixepoch   As DatTimUpd --   to YYYY-MM-DD HH MM SS From Demo   Insert Into Demo  DemoValue  Values   One                         -- activate the field Default -- WAIT a few seconds --     Insert Into Demo  DemoValue  Values   Two                         -- same thing but with Insert Into Demo  DemoValue  Values   Thr                         --   later time values  Update Demo Set DemoValue   DemoValue      Upd  Where idDemo   1  -- activate the Update-trigger  Select   From    Demo                                             -- display raw audit values idDemo  DemoValue  DatTimIns   DatTimUpd ------  ---------  ----------  ---------- 1       One Upd    1560024902  1560024944 2       Two        1560024944 3       Thr        1560024944  Select   From vewDemo                                             -- display automatic audit values idDemo  DemoValue  DatTimIns            DatTimUpd ------  ---------  -------------------  ------------------- 1       One Upd    2019-06-08 20 15 02  2019-06-08 20 15 44 2       Two        2019-06-08 20 15 44 3       Thr        2019-06-08 20 15 44

User · Answer

according to dr  hipp in a recent list post   CREATE TABLE whatever                 timestamp DATE DEFAULT  datetime  now   localtime

User · Answer

i believe you can use  CREATE TABLE test     id INTEGER PRIMARY KEY AUTOINCREMENT    t TIMESTAMP   DEFAULT CURRENT TIMESTAMP      as of version 3 1  source

[sql] sqlite database default time value 'now'

Examples related to sql

Examples related to sqlite