Difference between 2 dates in SQLite

Posted in :

我的需求是希望取得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

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *