sqlite日期操作方法

SQLite的日期操作:
SQLite支持五中日期时间函数, 如下:

1. date(timestring, modifier, modifier, …)
2. time(timestring, modifier, modifier, …)
3. datetime(timestring, modifier, modifier, …)
4. julianday(timestring, modifier, modifier, …)
5. strftime(format, timestring, modifier, modifier, …)

strftime时间字串格式化

%d day of month: 00
%f fractional seconds: SS.SSS
%H hour: 00-24
%j day of year: 001-366
%J Julian day number
%m month: 01-12
%M minute: 00-59
%s seconds since 1970-01-01
%S seconds: 00-59
%w day of week 0-6 with Sunday==0
%W week of year: 00-53
%Y year: 0000-9999

%%%

以下结果是等价的
date(…) <—> strftime(‘%Y-%m-%d’, …)
time(…) <—> strftime(‘%H:%M:%S’, …)
datetime(…) <—> strftime(‘%Y-%m-%d %H:%M:%S’, …)
julianday(…) <—> strftime(‘%J’, …)

再看看修饰符:

1. NNN days --加或减N天
2. NNN hours ----加或减N小时
3. NNN minutes --加或减N分钟
4. NNN.NNNN seconds ----加或减N秒
5. NNN months --加或减N月
6. NNN years --加或减N年
7. start of month --一月的开始的时间
8. start of year --一年开始的时间
9. start of day --一天开始的时间
10. weekday N --查看本周礼拜N是那天, 1,2,3,4,5,6,0分别代表礼拜一到礼拜天,礼拜天最大
11. unixepoch
12. localtime --取本地时间
13. utc

例子:
Compute the current date.

SELECT date(‘now’);

Compute the last day of the current month.

SELECT date(‘now’,‘start of month’,‘+1 month’,‘-1 day’);

----这个是 加一个月，减去一天
Compute the date and time given a unix timestamp 1092941466.

SELECT datetime(1092941466, ‘unixepoch’);

Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.

SELECT datetime(1092941466, ‘unixepoch’, ‘localtime’);

Compute the current unix timestamp.

SELECT strftime(‘%s’,‘now’);

Compute the number of days since the signing of the US Declaration of Independence.

SELECT julianday(‘now’) - julianday(‘1776-07-04’);

Compute the number of seconds since a particular moment in 2004:

SELECT strftime(‘%s’,‘now’) - strftime(‘%s’,‘2004-01-01 02:34:56’);

Compute the date of the first Tuesday in October for the current year.

SELECT date(‘now’,‘start of year’,‘+9 months’,‘weekday 2’);

Compute the time since the unix epoch in seconds (like strftime(‘%s’,‘now’) except includes fractional part):

SELECT (julianday(‘now’) - 2440587.5)*86400.0;

我的经典操作方法
获取当前第二天

DATETIME(date(datetime(e.JiechuShijian,'+1 day'))//当前时间的第二天的0点

获取当前两个时间差

(julianday(datetime('now','localtime')) - julianday( DATETIME(date(datetime(e.JiechuShijian,'+1 day'))//操作时间相减的到天数 （小数点方式）