Creating generate column based on today's date in SQLite

Question

I very new to SQL and have been trying to create a generated column using the following code.

c.execute("""CREATE TABLE students (
  --snip--
  level integer,
  date_last_visit integer, 
  days_since_visit integer GENERATED ALWAYS AS (DATE('now') - date_last_visit),
  urgency_rating integer GENERATED ALWAYS AS (days_since_visit * level) VIRTUAL,
  --snip--
)""") 

days_since_visit is supposed to take the current date and subtract a date from it

I get this error when adding data to the column: sqlite3.OperationalError: non-deterministic use of date() in a generated column

Answer 1

You can't.

Per the documentation, in the limitations section:

The expression of a generated column may only reference constant literals and columns within the same row, and may only use scalar deterministic functions. The expression may not use subqueries, aggregate functions, window functions, or table-valued functions.

And from the documentation for deterministic functions:

The built-in date and time functions of SQLite are a special case. These functions are usually considered deterministic. However, if these functions use the string "now" as the date, or if they use the localtime modifier or the utc modifier, then they are considered non-deterministic. Because the function inputs are not necessarily known until run-time, the date/time functions will throw an exception if they encounter any of the non-deterministic features in a context where only deterministic functions are allowed.

What you can do, however, is use a view instead that adds those columns to an underlying real table. Something like:

CREATE TABLE student_data(level INTEGER, date_last_visit INTEGER, ...);
CREATE VIEW students AS
SELECT level, date_last_visit, DATE('now') - date_last_visit AS days_since_visit, ...
FROM student_data;

Though note that DATE() returns a string and subtracting an integer from a string isn't going to do what you want; I'm sure there's other SO questions about finding the number of days since a given time in sqlite.