sqlite数据库默认时间值'now'-sqlitedatabasedefaulttimevalue'now'

232  

i believe you can use

我相信你可以用

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

as of version 3.1 (source)

截至3.1版本(来源)

76  

according to dr. hipp in a recent list post:

hipp博士在最近的一篇文章中写道:

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

31  

It's just a syntax error, you need parenthesis: (DATETIME('now'))

它只是一个语法错误，您需要括号:(DATETIME('now'))

If you look at the documentation, you'll note the parenthesis that is added around the 'expr' option in the syntax.

如果查看文档，您将注意到语法中围绕“expr”选项添加的括号。

10  

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.

这是一个完整的例子，基于其他答案和对问题的评论。在本例中，时间戳(created_at-column)被保存为unix纪元UTC时区，并仅在必要时转换为本地时区。

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.

使用unix epoch保存存储空间- 4字节整数和24字节字符串存储为ISO8601字符串时，请参见数据类型。如果4字节不够，可以增加到6或8字节。

Saving timestamp on UTC timezone makes it convenient to show a reasonable value on multiple timezones.

在UTC时区上保存时间戳可以方便地在多个时区显示合理的值。

SQLite version is 3.8.6 that ships with Ubuntu LTS 14.04.

SQLite版本是3.8.6，附带了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.

Localtime是正确的，因为我在查询时位于UTC+2 DST。

3  

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')然后它会给你utc时间，但如果你写它查询，那么你必须在此之前添加括号(datetime('now'))。对于本地时间相同的选择datetime('now'，'localtime')用于查询。

(datetime('now','localtime'))

(datetime(“现在”、“作用”))

3  

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

最好是使用真实的类型，以节省存储空间。

Quote from 1.2 section of Datatypes In SQLite Version 3

引用SQLite版本3中的1.2节数据类型

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

SQLite没有存储日期和/或时间的存储类。相反，SQLite的内置日期和时间函数可以将日期和时间存储为文本、实值或整数值

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

see column-constraint .

看到column-constraint。

And insert a row without providing any value.

插入一行而不提供任何值。

INSERT INTO "test" DEFAULT VALUES;