selECT julianday( datetime('2015-01-05 00:00:00') )- julianday( strftime('%Y-%m-%d %H:%M:%S',recordDate ) ) from weather
julianday()函数精确到秒,所以要 添加Time小时和秒
DATEDIFF(w1.recordDate, w2.recordDate) = 1;
写法为
julianday( strftime('%Y-%m-%d %H:%M:%S', w1.recordDate ))- julianday( strftime('%Y-%m-%d %H:%M:%S', w2.recordDate ))=1
相差多少个月
SELECT (strftime('%Y', '2024-05-01', 'localtime') - strftime('%Y', recordDate, 'localtime')) * 12 + (strftime('%m', '2024-05-01', 'localtime') - strftime('%m', recordDate, 'localtime'))
相差多少年
SELECT (strftime('%Y', '2024-05-01', 'localtime') - strftime('%Y', recordDate, 'localtime'))