GNU Date: inconsistency calculating number of days between dates

I am trying to calculate the number of days between two dates like this:

$ echo $((($(date +%s -d 2016/11/22)-$(date +%s -d 2016/11/20))/(3600*24))) days
2 days

That is the expected answer and perfectly consistent with this:

$ date -d '2016/11/22 - 2 days'
Sun Nov 20 00:00:00 CET 2016

However, these two seem to be inconsistent:

$ echo $((($(date +%s -d 2020/06/28)-$(date +%s -d 2016/11/20))/(3600*24))) days
1315 days

$ date -d '2020/06/28 - 1315 days'
Mon Nov 21 00:00:00 CET 2016

Am I missing something? Why don’t I get 1316 days (instead of 1315) in the third command I used?

I have done a few more tests changing the month in the date 2020/06/28. It seems that up to March I get the expected answer (i.e., I get Nov 20 in the fourth command), but from April on the inconsistency is present (i.e., I get Nov 21 in the fourth command). Any hints?

Go to Source
Author: orelleiro

Query or filter the data when using date time format

Googlesheet: https://docs.google.com/spreadsheets/d/1hO_KatnkP58wuF6I7kwtlGgCI6xM6ubopgCwuYMXXIU/edit?usp=sharing

Sheet2

In cell A2 I have:
=QUERY(Sheet1!A1:Q, “Select * where B >= datetime ‘”&TEXT($A$1,”MM-DD-YYYY HH:mm:ss”)&”‘”)

In cell A1 I have:
6/17/2020 10:53:22 AM

Sheet1:
Column A (user) and B (date and time)
User1 6/17/2020 10:53:22 AM
User2 6/17/2020 10:53:22 AM
User3 6/17/2020 11:10:55 AM
User4 6/17/2020 11:33:57 AM
User5 6/24/2020 10:47:34 AM
User6 6/25/2020 4:50:39 AM
User7 6/24/2020 4:00:24 AM
User8 7/4/2020 5:44:25 AM
User9 7/4/2020 7:08:29 AM
User10 7/4/2020 8:49:24 AM
User11 7/3/2020 7:36:46 AM

The expected output in A2 Sheet2 is:
User1 6/17/2020 10:53:22 AM
User2 6/17/2020 10:53:22 AM

but the query I used outputs all of the data from Sheet 1.

Hope someone can help me…

Go to Source
Author: KB John