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?
according to dr. hipp in a recent list post:
CREATE TABLE whatever(
....
timestamp DATE DEFAULT (datetime('now','localtime')),
...
);
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'))
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'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.
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.
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.
according to dr. hipp in a recent list post:
CREATE TABLE whatever(
....
timestamp DATE DEFAULT (datetime('now','localtime')),
...
);
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
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;
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'))
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 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
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;
according to dr. hipp in a recent list post:
CREATE TABLE whatever(
....
timestamp DATE DEFAULT (datetime('now','localtime')),
...
);
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.
according to dr. hipp in a recent list post:
CREATE TABLE whatever(
....
timestamp DATE DEFAULT (datetime('now','localtime')),
...
);
Source: Stackoverflow.com