11

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.

Infinite Recursion
  • 6,511
  • 28
  • 39
  • 51
Elan
  • 6,084
  • 12
  • 64
  • 84

3 Answers3

23

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)
MrAnonymous
  • 717
  • 3
  • 7
  • The timestamp output from The Easy Way doesn't seem valid for a couple reasons - it has an extra decimal point and it has the clock seconds sandwiched in between the epoch seconds and the milliseconds. For my purposes, I need to compare 'now' to timestamps that are already stored in the database in millisecond, not decimal seconds. But the millisecond precision isn't actually important so I ended up using `strftime('%s000','now')` instead. – spaaarky21 Sep 06 '13 at 21:47
4

After further study of the SQLite documentation and other information found on date number conversions, I have come up with the following formula, which appears to produce correct results:

INSERT INTO TestDate(LastModifiedTime)
    VALUES(CAST((((JulianDay('now', 'localtime') - 2440587.5)*86400.0) + 62135596800) * 10000000 AS BIGINT))

Seems like a painful way to produce something that I would expect to be available as a built-in datetime format, especially that the database supports the storing of datetime values in ticks. Hopefully, this becomes useful for others too.

Update:

The above formula is not perfect when it comes to daylight savings. See section Caveats And Bugs in SQLite docs regarding local time calculation.

Elan
  • 6,084
  • 12
  • 64
  • 84
  • 1
    Your formula is incorrect because it doesn't account for Daylight Savings time changes. The LastModifiedTime metadata in NTFS is stored as UTC, therefore your conversion has a bug. – JasDev May 30 '11 at 12:04
  • 1
    That is true. I added a note regarding local time calculations. Do you have any suggestions on improving the formula? – Elan May 30 '11 at 21:52
1

The following will return the number of milliseconds since the UNIX Epoch:

SELECT (strftime('%s', 'now') - strftime('%S', 'now') + strftime('%f', 'now')) * 1000 AS ticks

It works by grabbing the number of seconds since the Unix Epoch (%s), subtracting the number of seconds in the current time (%S), adding the number of seconds with decimal places (%f), and multiplying the result by 1000 to convert from seconds to milliseconds.

The subtraction and addition are to add precision to the value without skewing the result. As stated in the SQLite Documentation, all uses of 'now' within the same step will return the same value.

Rob
  • 141
  • 2
  • 7