SQLite的日期操作

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

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

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; 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!