CODEWITHSUNDEEP
datedatetimetimesqlite
J0ANMM
J0ANMM

Reputation: 8551

Operating with datetimes in SQLite

I'm interested in knowing the different possibilities to operate with datetimes in SQLite and understand its pros and cons. I did not find anywhere a detailed explanation of all the alternatives.

So far I have learned that

SQLite doesn't actually have a native storage class for timestamps / dates. It stores these values as NUMERIC or TEXT typed values depending on input format. Date manipulation is done using the builtin date/time functions, which know how to convert inputs from the other formats.

(quoted from here)

When any operation between datetimes is needed, I have seen two different approaches:

julianday function

SELECT julianday(OneDatetime) - julianday(AnotherDatetime) FROM MyTable;

Number of days is returned, but this can be fractional. Therefore, you can also get some other measures of time with some extra operations. For instance, to get minutes:

SELECT CAST ((
    julianday(OneDatetime) - julianday(AnotherDatetime)
) * 24 * 60 AS INTEGER)

Apparently julianday could cause some problems:

Bear in mind that julianday returns the (fractional) number of 'days' - i.e. 24hour periods, since noon UTC on the origin date. That's usually not what you need, unless you happen to live 12 hours west of Greenwich. E.g. if you live in London, this morning is on the same julianday as yesterday afternoon.

More information in this post.

strftime function

SELECT strftime("%s", OneDatetime)-strftime("%s", AnotherDatetime) FROM MyTable;

Number of seconds is returned. Similarly, you can also get some other measures of time with some extra operations. For instance, to get minutes:

SELECT (strftime("%s", OneDatetime)-strftime("%s", AnotherDatetime))/60 FROM MyTable;

More information here.

My conclusion so far is: julianday seems easier to use, but can cause some problems. strftime seems more verbose, but also safer. Both of them provide only as results a single unit (either days or hours or minutes or seconds), but not a combination of many.

Question

1) Is there any other possibility to operate with datetimes?

2) What would be the best way to get directly the difference of two datetimes in time format (or date or datetime), where datetime would be formatted as 'YYYY-mm-dd HH:MM:SS', and the result would be something in the same format?

I would have imagined that something like the following would work, but it does not:

SELECT DATETIME('2016-11-04 08:05:00') - DATETIME('2016-11-04 07:00:00') FROM MyTable;
> 01:05:00

Upvotes: 1

Views: 2587

Answers (1)

CL.
CL.

Reputation: 180280

Julian day numbers are perfectly safe when computing differences. The only problem would be if you tried to convert them into a date by truncating any fractional digits; this would result in noon, not midnight. (The same could happen if you tried to store them in integer variables.) But that is not what you're doing here.

SQLite has no built-in function to compute date/time differences; you have to convert date/time values into some number first. Whether you use (Julian) days or seconds does not really matter from a technical point of view; use whatever is easier in your program.

If you started with a different format, you might want to convert the resulting difference back into that format, e.g.:

time(difference_value, 'unixepoch')  -- from seconds to hh:mm:ss
time(0.5 + difference_value)         -- from Julian days to hh:mm:ss

Upvotes: 3

Related Questions