Using local time for date calculations in SQLite

I have an SQLite database that I use to track approval of tasks. Every Monday, I need to generate a count of the number of tasks approved from the prior Tuesday through the Monday on which I'm creating the report. The approval dates are stored in the database as an integer and I enter them in the form 2018-05-14. I use the following SQL command in a Python script to determine the number I've approved in the last week:

sql = 'SELECT COUNT(*) FROM Tasks WHERE Approved >= date(CURRENT_DATE,"-6 day")'

But I noticed that the count didn't always include the tasks I approved on the prior Tuesday. E.g., when I ran the script tonight, May 14, the number reported was 5, yet I expected the result to be 7. E.g., when I issued the command below in SQLite, I saw the following results:

sqlite> SELECT Approved FROM Tasks WHERE Approved >= date('2018-05-08');
2018-05-08
2018-05-08
2018-05-09
2018-05-09
2018-05-09
2018-05-09
2018-05-14
sqlite>

So I issued the following commands in SQLite on my Macbook Pro laptop on May 14, 2018 to check the date value it was reporting:

sqlite> select date('Now');
2018-05-15
sqlite> select date(CURRENT_DATE);
2018-05-15
sqlite> select date(CURRENT_TIMESTAMP);
2018-05-15
sqlite>

I then realized that SQLite was using Coordinated Universal Time (UTC), aka Greenwich Mean Time (GMT), aka Zulu time. It was 10:00 PM on May 14 in my local time zone, which is currently 4 hours behind UTC time, but it was 0200Z, i.e., 2:00 AM, on May 15 in UTC time. So SQLite was using May 9 through May 15 for its determination rather than May 8 through 14 as I wanted. I was able to get the expected date, though, by including "localtime" in the SQL SELECT statements as shown below:

sqlite> select date(CURRENT_TIMESTAMP, 'localtime');
2018-05-14
sqlite> select date(CURRENT_DATE, 'localtime');
2018-05-14
sqlite> select date('Now', 'localtime');
2018-05-14
sqlite>

When I added the "localtime" value to the SELECT statement, I then saw a count of 7 returned as expected rather than 5.

sqlite> SELECT COUNT(*) FROM Tasks WHERE Approved >= date(CURRENT_DATE,"-6 day");
5
sqlite> SELECT COUNT(*) FROM Tasks WHERE Approved >= date(CURRENT_DATE,"-6 day","localtime");
7
sqlite>

Related articles:

  1. Using SQLite
  2. Counting SQLite records based on a specified date
  3. Running SQL commands from a text file using sqlite3