Difference between two dates in SQLite

Question

How do I get the difference between two dates in SQLite? I already tried something like this:

SELECT Date('now') - DateCreated FROM Payment 

It returns 0 every time.

Answer 1

  SELECT julianday('now') - julianday(DateCreated) FROM Payment; 

Answer 2

Both answers provide solutions a little more complicated as they should be. Let's say a payment was created on January 6, 2013 . And we want to know the difference between this date today.

 sqlite> SELECT julianday() - julianday('2013-01-06'); 34.7978485878557 

The difference is 34 days. We can use julianday('now') for better clarity. In other words, we do not need date() or datetime() functions as parameters for the julianday() function.

Answer 3

Difference in days

 Select Cast (( JulianDay(ToDate) - JulianDay(FromDate) ) As Integer) 

Watch difference

 Select Cast (( JulianDay(ToDate) - JulianDay(FromDate) ) * 24 As Integer) 

Difference in minutes

 Select Cast (( JulianDay(ToDate) - JulianDay(FromDate) ) * 24 * 60 As Integer) 

Seconds difference

 Select Cast (( JulianDay(ToDate) - JulianDay(FromDate) ) * 24 * 60 * 60 As Integer) 

Answer 4

The SQLite wiki is a great link, and the DateAndTimeFunctions page is good for bookmarking. It is also useful to remember that it is quite easy to play with queries using the sqlite command line utility:

 sqlite> select julianday(datetime('now')); 2454788.09219907 sqlite> select datetime(julianday(datetime('now'))); 2008-11-17 14:13:55 

Answer 5

This answer is a little long, and the documentation will not tell you about it (because they assume that you save your dates as UTC dates in the database), but the answer to this question depends a lot on the time zone, the dates are saved. You also do not use Date('now') , but use the julianday() function to calculate both dates back to the total date, and then subtract the difference between these results from each other.

If your dates are stored in UTC:

 SELECT julianday('now') - julianday(DateCreated) FROM Payment; 

This is what has a top rating, and is also in the documentation . This is only part of the picture, and a very simplified answer if you ask me.

If your dates are stored in local time, using the above code will make your WRONG answer the number of hours of your GMT offset. If you are located in the Eastern state of the USA, like me, that is GMT-5, your result will contain 5 hours. And if you try to make DateCreated compatible with UTC, because julianday('now') goes against the GMT date:

 SELECT julianday('now') - julianday(DateCreated, 'utc') FROM Payment; 

This has an error in which it will add an hour for DateCreated , which is during daylight saving time (March-November). Say that "now" at noon per day is not related to DST, and you created something back in June (during DST) at noon, your result will be 1 hour, not 0 hours, for part of the hours. You will need to write a function in your application code that displays the result in order to change the result and subtract the hour from the DST dates. I did this until I realized that there is a better solution to this problem: SQLite vs. Oracle - calculating date differences - hours

Instead, as pointed out to me, for dates stored in local time, make both matches local:

 SELECT julianday('now', 'localtime') - julianday(DateCreated) FROM Payment; 

Or add 'Z' at local time:

 julianday(datetime('now', 'localtime')||'Z') - julianday(CREATED_DATE||'Z') 

Both of these seem to compensate and do not add an extra hour for DST dates and do a direct subtraction - so that an element created at noon on a DST day, when checked at noon on a day other than DST, will not receive an additional hour when performing the calculation.

And although I admit that most of them will say that they do not store local time dates in your database and store them in UTC so that you do not encounter this, not all applications have a worldwide audience, and not every programmer wants to go through EVERY conversion dates in their system in UTC and return again each time they do GET or SET in the database and understand if something is local or in UTC.

Answer 6

Just a note for writing time word functions. For those who are looking for hours of work, a very simple change has been hours, and minutes are shown as a percentage of 60, since most payroll companies want this.

CAST ((julianday(clockOUT) - julianday(clockIN)) * 24 AS REAL) AS HoursWorked

 Clock In Clock Out HoursWorked 2016-08-07 11:56 2016-08-07 18:46 6.83333332836628 

Answer 7

If you want time in the format 00:00: I solved it like this:

 select strftime('%H:%M',CAST ((julianday(FinishTime) - julianday(StartTime)) AS REAL),'12:00') from something 

Answer 8

If you want to record between days,

 select count(col_Name) from dataset where cast(julianday("now")- julianday(_Last_updated) as int)<=0; 

Answer 9

Given that your date format is as follows: "YYYY-MM-DD HH: MM: SS", if you need to find the difference between the two dates in the number of months:

(strftime('%m', date1) + 12*strftime('%Y', date1)) - (strftime('%m', date2) + 12*strftime('%Y', date2))

Answer 10

In my case, I have to calculate the difference in minutes, and julianday() does not give an exact value. Instead, I use strftime() :

SELECT strftime('%s', [UserEnd]) - strftime('%s', [UserStart]))/60

Both dates are converted to unixtime (seconds) and then subtracted to get the value in seconds between the two dates. Then divide it by 60.

https://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Answer 11

First, it’s not clear what date format you have. There is already an answer involving strftime("%s") .

I like to expand on this answer.

SQLite only has the following storage classes: NULL, INTEGER, REAL, TEXT, or BLOB. To simplify things, I am going to assume that the dates PRESENT contain seconds from 1970-01-01. Here is an example of a schema for which I will add the example data for December 1, 2018:

 CREATE TABLE Payment (DateCreated REAL); INSERT INTO Payment VALUES (strftime("%s", "2018-12-01")); 

Now let's decide on the difference in dates between "December 1, 2018" and now (now, when I write, it is noon December 12, 2018):

The difference in days:

 SELECT (strftime("%s", "now") - DateCreated) / 86400.0 FROM Payment; -- Output: 11.066875 

Date difference in hours:

 SELECT (strftime("%s", "now") - DateCreated) / 3600.0 FROM Payment; -- Output: 265.606388888889 

Date difference in minutes:

 SELECT (strftime("%s", "now") - DateCreated) / 60.0 FROM Payment; -- Output: 15936.4833333333 

The difference in date in seconds:

 SELECT (strftime("%s", "now") - DateCreated) FROM Payment; -- Output: 956195.0