Get one year date from max date in SQLite Python

I am trying to fetch one years of data based on the max(date) column in SQLite. My logic works for SQL Server which I have written below.

Code:

"select * from {} t where NAME in {} and t.DATE1 > " \
                    "(select date(year, -1, max(t1.DATE1)) from {} t1)".format(TABLE_NAME, list_of_names,
                                                                                  TABLE_NAME)

Any changes in query that fetch me from SQLite table?

>Solution :

In SQLite, the date manipulation functions differ from those in SQL Server, which explains why your existing query does not work directly. SQLite uses the strftime function for date manipulation. To fetch one year’s worth of data based on the maximum date in a column, you need to adjust your query to use SQLite’s date functions.

Here is how you can modify your query for SQLite:

SELECT *
FROM {} t
WHERE NAME IN {} AND t.DATE1 > 
    (SELECT date(max(t1.DATE1), '-1 year') FROM {} t1)

This query does the following:

1. SELECT * FROM {} t: Selects all columns from your table (replacing {} with TABLE_NAME).

2. WHERE NAME IN {}: Filters the rows where the NAME column matches the values in list_of_names (replacing {} with your list of names).

3. AND t.DATE1 > (SELECT date(max(t1.DATE1), '-1 year') FROM {} t1): This subquery finds the maximum date in the table and subtracts one year from it. The outer query then selects rows where the DATE1 column is greater than this calculated date.

Make sure to format list_of_names correctly for the SQL query. If it’s a Python list, you’ll need to convert it to a string format suitable for SQL, such as ('name1', 'name2', ...).

Your final query in Python would look something like this:

query = "SELECT * FROM {} t WHERE NAME IN {} AND t.DATE1 > (SELECT date(max(t1.DATE1), '-1 year') FROM {} t1)".format(TABLE_NAME, tuple(list_of_names), TABLE_NAME)

Ensure that TABLE_NAME and list_of_names are correctly formatted and sanitized to prevent SQL injection attacks, especially if they come from user inputs.