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

Question

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.

Answer 1

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)