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

i have sqlite database store dates ticks. not using default iso8601 format. let's have table defined follows:

create table testdate (lastmodifiedtime datetime) 

using sql, wish insert current date , time. if execute of below statements, end getting date , time stored string , not in ticks.

insert testdate (lastmodifiedtime) values(current_timestamp) insert testdate (lastmodifiedtime) values(datetime('now')) 

i have looked @ sqlite documenation, not seem find option obtain current timestamp in ticks.

i can of course define parameter in c# , store value system.datetime. result in datetime getting stored database in ticks.

what able insert , update current timestamp directly within sql statement. how this?

edit:

the reason want data stored ticks in database, dates stored in same format stored ado.net data provider, , when data queried using ado.net provider correctly retrieved system.datatime .net type.

this particular oddity of sqlite caused me anguish.

easy way - store , retrieve regular timestamp

create table testdate (         lastmodifiedtime datetime ); insert testdate (lastmodifiedtime) values (datetime('now')); select datetime(lastmodifiedtime), strftime('%s.%f', lastmodifiedtime)  testdate; 

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

painful way - store , retrieve unix timestamp

you can use strftime retrieve value in ticks. additionally, store unix timestamp (roughly equivalent ticks), can can surround number of seconds in single-quotes.

 insert testdate (lastmodifiedtime) values ('1305061354'); 

sqlite store internally other value not unix timestamp. on retrieval, need explicitly tell sqlite retrieve unix timestamp.

 select datetime(lastmodifiedtime, 'unixepoch') testdate; 

to store current date , time, use strftime('%s', 'now').

 insert testdate (lastmodifiedtime) values (strftime('%s', 'now')); 

full example:

create table testdate (         lastmodifiedtime datetime );       insert testdate (lastmodifiedtime) values (strftime('%s', 'now')); select datetime(lastmodifiedtime, 'unixepoch') testdate; 

when executed sqlite3, script print:

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