前端之家收集整理的这篇文章主要介绍了
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;