CODEWITHSUNDEEP
sqlitetimestamp
yayu
yayu

Reputation: 8098

SQLite storing default timestamp as unixepoch

When defining a relation, I want to update an attribute to the timestamp at insert. For example, a working table that I have right now

CREATE TABLE t1(
id INTEGER PRIMARY KEY AUTOINCREMENT,
time TIMESTAMP
DEFAULT CURRENT_TIMESTAMP,
txt TEXT);

This is updating a timestamp on insert, for example, insert into t1 (txt) values ('hello') adds the row 1|2012-07-19 08:07:20|hello|. However, I want to have this date formatted in unixepoch format.

I read the docs but this wasn't clear. For example, I modified the table relation to time TIMESTAMP DEFAULT DATETIME('now','unixepoch') but I get an error. Here, as in the docs, now was my time string and unixepoch was the modifier but it didn't work. Could someone help me how to format it as a unixepoch timestamp?

Upvotes: 32

Views: 30897

Answers (4)

Alexandre Dieulot
Alexandre Dieulot

Reputation: 544

SQLite 3.38.0 (2022-02-22) introduces unixepoch().

CREATE TABLE table_name(
  column_name INTEGER DEFAULT (unixepoch())
);

Reference: https://www.sqlite.org/lang_datefunc.html#uepch

The unixepoch() function returns a unix timestamp - the number of seconds since 1970-01-01 00:00:00 UTC.

Function Equivalent strftime()
unixepoch(...) CAST(strftime('%s', ...) as INT)

Upvotes: 6

Melroy van den Berg
Melroy van den Berg

Reputation: 3215

Indeed strftime, which can also be used like so:

SELECT strftime('%s', timestamp) as timestamp FROM ... ;

Gives you:

1454521888

'timestamp' table column can be a text field even, using the current_timestamp as DEFAULT.

Without strftime:

SELECT timestamp FROM ... ;

Gives you:

2016-02-03 17:51:28

Upvotes: 3

user3704578
user3704578

Reputation: 256

Note 'timestamp' is not a data type known to SQLite (see list here). The default value generated by strftime() would actually be stored as Text.

If it is important to store the value as a number instead of as a string, declare the field as an Integer and add a CAST() into the mix, like so:

create table t1(
...
ts_field integer(4) default (cast(strftime('%s','now') as int)),
...
);

Upvotes: 23

user647772
user647772

Reputation:

Use strftime:

sqlite> select strftime('%s', 'now');
1342685993

Use it in CREATE TABLE like this:

sqlite> create table t1 (
   ...> id integer primary key,
   ...> time timestamp default (strftime('%s', 'now')),
   ...> txt text);
sqlite> insert into t1 (txt) values ('foo');
sqlite> insert into t1 (txt) values ('bar');
sqlite> insert into t1 (txt) values ('baz');
sqlite> select * from t1;
1|1342686319|foo
2|1342686321|bar
3|1342686323|baz

See https://www.sqlite.org/lang_createtable.html#tablecoldef

If the default value of a column is an expression in parentheses, then the expression is evaluated once for each row inserted and the results used in the new row.

Upvotes: 61

Related Questions