Difference between 2 dates in SQLite

我的需求是希望取得user 的生日後，sqlite 可以直接顯示該user 的年齡。

解法，先把 string 轉 python datetime:

from datetime import datetime

birthday_object = datetime.strptime(birthday, '%Y-%m-%d')

然後把 birthday_object 直接餵進去 sqlite 裡，

sqlite 的 select 用：

SELECT julianday('now') - julianday(DateCreated)

答案就出來一半了，接下來可以用 python 處理，也可以用 sqlite, 如果是用 sqlite:

Difference In Days

Select Cast ((
    JulianDay(ToDate) - JulianDay(FromDate)
) As Integer)

Difference In Hours

Select Cast ((
    JulianDay(ToDate) - JulianDay(FromDate)
) * 24 As Integer)

Difference In Minutes

Select Cast ((
    JulianDay(ToDate) - JulianDay(FromDate)
) * 24 * 60 As Integer)

Difference In Seconds

Select Cast ((
    JulianDay(ToDate) - JulianDay(FromDate)
) * 24 * 60 * 60 As Integer)

 

from：

https://www.sqlite.org/lang_datefunc.html