CODEWITHSUNDEEP
sqlite
Chris Jensen
Chris Jensen

Reputation: 89

Sqlite - Calculation of time period

I need to calculate the time between a 'fixed' time (usually a few days ago) and now.

Therefore I have googled me to this sql (http://www.sqlite.org/lang_datefunc.html): 

SELECT strftime ('%s', '1423143532') - strftime ('%s', 'now') as timediff;

It gives a weird result (122747310831610), and I have no idea why !? :-/

Should I use a different method of calculation, is that where the problem is? If yes, which one would give me the correct result? :)

Upvotes: 2

Views: 207

Answers (1)

CL.
CL.

Reputation: 180020

A bare number is interpreted as a Julian day number:

> SELECT datetime('1423143532');
3891722-46318118-31 12:00:00
> SELECT datetime(2457064);
2015-02-10 12:00:00

To interpret a number as the number of seconds since 1970, you must use the unixepoch modifier:

> SELECT datetime(1423143532, 'unixepoch');
2015-02-05 13:38:52
> SELECT strftime ('%s', 1423143532, 'unixepoch');
1423143532

As you can see, passing this number through strftime is pointless. Just use it directly:

> SELECT 1423143532 - strftime('%s', 'now') as timediff;
-436916

Upvotes: 1

Related Questions