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

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 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

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

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 gt   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

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

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

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

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 gt   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

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

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

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