sqlite数据库处理时间问题 和 日期时间函数

首先,sqlite数据库在时间处理上和sqlserver还有oracle不同,下面根据自己做过的实例总结一下.

创建了一个Log数据表:


LogID SourceID OperatorID LogType LogLevel LogTime LogContent
1 aaa.aspx 0 2 1 2011-08-18 16:44:32.000 aaaa
2 bbb.aspx 1 2 2 2011-08-18 16:38:32.000 bbbb
3 ccc.aspx 2 3 3 2011-09-02 cccc
4 ddd.aspx 3 1 4 2011-08-15 dddd
5 eee.aspx 4 1 3 2011-08-18 eee

普通的sqlserver的查询语句如下：select * from Log where LogTime='2011-09-02'可以查询出"'2011-09-02"的数据。

而在sqlite数据库中，写上面的语句，查询不到任何数据；说明sqlserver于sqlite对于时间处理上是不同的。那么如何写一条以时间为查询条件的sql语句才能在sqlite数据库中把想要的结果查询出来呢？请看：：：：

select * from Log where datetime(LogTime)=datetime('2011-08-18 16:38:32.000')

sqlite数据查询语句,必须对时间字段和传入的时间参数做转换.即加上datetime()转换.

执行了上面的语句，就可以得到要查找到结果。如果按照sqlserver的写法，那么是不会得到查询结果的。

另外，一些错误的查询语句如下：

1: select * from Log where datetime(LogTime)=datetime('2011-08-18')，这条语句只能查询到一条数据，就是对应LogID=5的那条，而不会得到LogID=1和LogID=2的数据。可见，sqlite对于时间是非常严格的。精确度非常高。

2：select * from Log where datetime(LogTime)=datetime('2011-8-18')，这条语句是查询不到任何结果，因为sqlite的时间要求是yyyy-MM-dd或者yyyy-MM-dd hh:mm:ss的。当月数为10以下，那么必须写成0x的形式（如：05）,所以在做开发的时候一定要对入库的时间做相应处理。

3： select * from Log where datetime(LogTime)=datetime('2011-08-9')，同样的，这条语句也查询不到任何结果，应该把9改成09，就可以得到查询结果了。


SQLite 日期时间函数
SQLite并没有datatime字段类型，但是可以在字符串类型字段中存储时间，
并提供了一些比较实用的日期时间操作函数

strftime(日期时间格式, 日期时间字符串, 修正符, 修正符, ……)
strftime( 日期时间格式, 日期时间字符串 ) 也就等价于AAuto中的:
time( 日期时间字符串,日期时间格式 ) ,sqlite与AAuto 使用的格式化语法也一样。
参考：http://www.aau.cn/doc/reference/libraries/kernel/time/time.html

strftime() 函数返回一个经过格式化的日期时间，
它可以用下面的符号对日期和时间进行格式化：

%d 一月中的第几天 01-31
%f 小数形式的秒，SS.SSSS
%H 小时 00-24
%j 一年中的第几天 01-366
%J Julian Day Numbers
%m 月份 01-12
%M 分钟 00-59
%s 从 1970-01-01日开始计算的秒数
%S 秒 00-59
%w 星期，0-6，0是星期天
%W 一年中的第几周 00-53
%Y 年份 0000-9999
%% % 百分号

date,time,datetime,julianday 函数

date(日期时间字符串, 修正符, 修正符, ……) 等价于 strftime(“%Y-%m-%d”,…)
time(日期时间字符串, 修正符, 修正符, ……) 等价于 strftime(“%H:%M:%S”,…)
datetime(日期时间字符串, 修正符, 修正符, ……) 等价于 strftime(“%Y-%m-%d %H:%M:%S”,…)
julianday(日期时间字符串, 修正符, 修正符, ……) 等价于 strftime(“%J”,…)

日期时间字符串
可以用以下几种格式：
格式有严格的要求 2008-06-15 03:35:28 日期只能用'-'分隔，时间只能用':' 分隔，不足二位数的必须补零


[*]YYYY-MM-DD
[*]YYYY-MM-DD HH:MM
[*]YYYY-MM-DD HH:MM:SS
[*]YYYY-MM-DD HH:MM:SS.SSS
[*]YYYY-MM-DDTHH:MM
[*]YYYY-MM-DDTHH:MM:SS
[*]YYYY-MM-DDTHH:MM:SS.SSS
[*]HH:MM
[*]HH:MM:SS
[*]HH:MM:SS.SSS
[*]now
[*]DDDD.DDDD

在第五种到第七种格式(ISO8601)中的“T”是一个分割日期和时间的字符；
第八种到第十种格式只代表2000-01-01日的时间，
第十一种格式的’now’表示返回一个当前的日期和时间，使用格林威治时间(UTC）；
第十二种格式表示一个 Julian Day Numbers。

修正符

日期和时间可以使用下面的修正符来更改日期或时间：


[*]NNN days
[*]NNN hours
[*]NNN minutes
[*]NNN.NNNN seconds
[*]NNN months
[*]NNN years
[*]start of month
[*]start of year
[*]start of week
[*]start of day
[*]weekday N
[*]unixepoch
[*]localtime
[*]utc
前六个修正符就是简单的增加指定数值的时间和日期；第七到第十个修正符表示返回当前日期的开始；第十一个修正符表示返回下一个星期是N的日期和时间；第十二个修正符表示返回从1970-01-01开始算起的秒数；第十三个修正符表示返回本地时间。

下面举一些例子：


[*]计算机当前时间
SELECT date(‘now’)
[*]计算机当前月份的最后一天
SELECT date(‘now’,’start of month’,’+1 month’,’-1 day’)
[*]计算UNIX 时间戳1092941466表示的日期和时间
SELECT datetime(‘1092941466’,’unixepoch’)
[*]计算 UNIX 时间戳1092941466 表示的本地日期和时间
SELECT datetime(‘1092941466’,’unixepoch’,’localtime’)
[*]计算机当前UNIX 时间戳
SELECT strftime(‘%s’,’now’)
[*]两个日期之间相差多少天
SELECT jolianday(‘now’)-jolianday(‘1981-12-23’)
[*]两个日期时间之间相差多少秒
SELECT julianday('now')*86400 - julianday('2004-01-01 02:34:56')*86400
[*]计算今年十月份第一个星期二的日期
SELECT date('now','start of year','+9 months','weekday 2');
[*]取大于现在时间的数据
select * from 表 where 日期字段>datetime('now','localtime')
[*]比较日期指定部份，举一反三，同样使用strftime格式式日期来对日、周、年比较
select * from 表 where strftime('%m',日期字段)=strftime('%m','now')
[*]大于指定时间的第一条
select title,pubtime from article where pubtime>'2008-06-15 03:35:28' order by pubtime asc Limit 1 Offset 0
[*]小于指定时间的第一条
select title,pubtime from article where pubtime<'2008-06-15 03:35:28' order by pubtime desc Limit 1 Offset 0

简单示例:
SELECT
datetime(CHANGE_DATE,'localtime'),
strftime('%Y-%m-%d',CHANGE_DATE,'localtime'),
datetime('now','localtime'),
strftime('%Y-%m-%d','now','localtime'),
DATE('now','localtime'),
time('now','Localtime'),
time('2010-11-27 01:12:21','Localtime','-8 hour') as Time
FROM SALARY_HISTORY ;

SELECT * FROM SALARY_HISTORY WHERE date(CHANGE_DATE,'Localtime')=Date('now','Localtime')