Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

datetime - How can I store the current timestamp in SQLite as ticks?

I have a SQLite database where I store the dates as ticks. I am not using the default ISO8601 format. Let's say I have a table defined as follows:

CREATE TABLE TestDate (LastModifiedTime DATETIME)

Using SQL, I wish to insert the current date and time. If I execute any of the below statements, I end up getting the date and time stored as a string and not in ticks.

INSERT INTO TestDate (LastModifiedTime) VALUES(CURRENT_TIMESTAMP)
INSERT INTO TestDate (LastModifiedTime) VALUES(DateTime('now'))

I have looked at the SQLite documenation, but I do not seem to find any option to obtain the current timestamp in ticks.

I can of course define a parameter in C# and store the value as a System.DateTime. This does result in the datetime getting stored to the database in ticks.

What I would like to do is be able to insert and update the current timestamp directly from within the SQL statement. How would I do this?

Edit:

The reason I want the data stored as ticks in the database, is that the dates are stored in the same format as stored by the ADO.Net data provider, and so that when the data is also queried using the ADO.Net provider it is correctly retrieved as a System.DataTime .Net type.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

This particular oddity of SQLite caused me much anguish.

Easy way - store and retrieve as regular timestamp

create table TestDate (
        LastModifiedTime datetime
);
insert into TestDate (LastModifiedTime) values (datetime('now'));
select datetime(LastModifiedTime), strftime('%s.%f', LastModifiedTime)  from TestDate;

Output: 2011-05-10 21:34:46|1305063286.46.000

Painful way - store and retrieve as a UNIX timestamp

You can use strftime to retrieve the value in ticks. Additionally, to store a UNIX timestamp (roughly equivalent to ticks), you can can surround the number of seconds in single-quotes.

 insert into TestDate (LastModifiedTime) values ('1305061354');

SQLite will store this internally as some other value that is not a UNIX timestamp. On retrieval, you need to explicitly tell SQLite to retrieve it as a UNIX timestamp.

 select datetime(LastModifiedTime, 'unixepoch') FROM TestDate;

To store the current date and time, use strftime('%s', 'now').

 insert into TestDate (LastModifiedTime) VALUES (strftime('%s', 'now'));

Full example:

create table TestDate (
        LastModifiedTime datetime
);      
insert into TestDate (LastModifiedTime) values (strftime('%s', 'now'));
select datetime(LastModifiedTime, 'unixepoch') from TestDate;

When executed by sqlite3, this script with print:

2011-05-10 21:02:34 (or your current time)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...