Datetime

Quite often, I need to save a date that has an expiry. It doesn’t make any sense to store both so I save the from date and then let SQLite calculate the to date. Oh, and for good measure, I’ll usually need to know the difference between the from and to fields. Behold:

CREATE VIEW cert_view as
with cert as (
select supplier, uid, cert_date, cert_desc, cert_link,
(select date(cert_date, '+'||cert_valid||' days')) expires,
(select julianday((select date(cert_date, '+'||cert_valid||' days'))) - julianday((select date('now')))) days
from supplier_cert)

Navigating Records

Another one I use a lot. A combination of common table expressions, window functions, all wrapped up in a view. This enables you to have access to the previous record, the next record and the first and last records. I think you need SQLite 3.27 as minimum as this was when common table expressions was added:

CREATE VIEW supplier_nav as
with cte as (
select code, name, lead(code) over() next, lag(code) over() prev,
first_value(code) over() first, last_value(code) over() last
from supplier where status is 1) 

select * from cte

To be continued…