首页 > 代码库 > SQLite时间处理
SQLite时间处理
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 whereLogTime=‘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 * fromLog 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开始算起的秒数;第十三个修正符表示返回本地时间。
下面举一些例子: - 计算机当前时间 SELECTdate(‘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‘ orderby pubtime asc Limit 1 Offset 0
- 小于指定时间的第一条select title,pubtime from article where pubtime<‘2008-06-15 03:35:28‘ orderby 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 WHEREdate(CHANGE_DATE,‘Localtime‘)=Date(‘now‘,‘Localtime‘)
SQLite 时间的保存与查询
一 时间的保存
经过GOOGLE发现大多数的解决方法为datetime.ToString("s")来解决的,经过测试此方法虽然解决的问题,但还不够完美。
因为这样格式化出来的时间在用工具SQLite Developer 查看时显示的时间看起来很怪,不直观。而且如果在SQLite Developer
手动修改了时间,在程序中会报错,因为这个时候保存的时间格式发现了改变。经过测试发现datetime.ToString("yyyy-MM-dd
hh:mm:ss")可以很好的解决这个问题。
二 时间的查询
如果你用SQLite作开发,一定少不了时间的查询,一定会让你动不少脑精。因为和别的数据库不一样,就如要查询2009.3.20
11:00:00领取工资的有多少人的SQL怎么写呢,你一定会写成:
select count(*) from T where statue=‘1‘ and [date]=‘2009-03-2011:00:00‘
仔细查看会发现有问题,因为没有结果,实际表中是有结果的,这是为什么,其实我也不没有搞清楚。这个问题还是在国外的一个
论坛发现解决方法的。只要改一下上面的语句就可以了
select count(*) from T where statue=‘1‘ anddatetime([date])=datetime(‘2009-03-20 11:00:00‘)
or
select count(*) from T where statue=‘1‘ and datetime([date])=‘2009-03-2011:00:00‘
记住2009-03-20不能写成为2009-3-20.
以上方法经过测目前没有发现问题,当然我也是初次使用SQLite来开发一个小项目,也许还有问题没有发现出来,请各位指教!
Sqlite 查询时间格式处理
Windows 下没有strptime,sqlite查询时间戳字段后得出的是char*,要转成time_t很不爽。临时解决方案如下:
1、sql语句需要格式化时间戳字段:
string strSql = "select strftime(‘%%Y%%m%%d%%H%%M%%S‘,mytime) as mytimefrom mytable“;
2、写一个C函数进行转换:
long TimeStamp2Time_t(char* pszTimeStamp /*"YYYYMMDDhhmmss"*/)
{
int ttt[6][4]={{0,5,4,1900}
,{4,4,2,1}
,{6,3,2,0}
,{8,2,2,0}
,{10,1,2,0}
,{12,0,2,0}};
time_t t=time(NULL); struct tm* mytm = localtime(&t);
for(int i=0;i<6;i++)
{
char tmp[5]="";
memcpy(tmp,((char*)pszTimeStamp)+ttt[i][0],ttt[i][2]);
*(((int*)mytm)+ttt[i][1])=atoi(tmp)-ttt[i][3];
}
return mktime(mytm);
}
SQLite支持5个日期和时间函数如下:
S.N. | 函数 | 例子 |
1 | date(timestring, modifiers...) | This returns the date in this format: YYYY-MM-DD |
2 | time(timestring, modifiers...) | This returns the time as HH:MM:SS |
3 | datetime(timestring, modifiers...) | This returns YYYY-MM-DD HH:MM:SS |
4 | julianday(timestring, modifiers...) | This returns the number of days since noon in Greenwich on November 24, 4714 B.C. |
5 | strftime(timestring, modifiers...) | This returns the date formatted according to the format string specified as the first argument formatted as per formatters explained below. |
上述五个日期和时间函数时间字符串作为参数。后跟零个或多个修饰符的时间字符串。 strftime()函数还需要一个格式字符串作为其第一个参数。下面的部分将给予您详细的时间字符串和改性剂的不同类型。
5.1 时间字符串:
一时间字符串可以在任何采用以下格式:
S.N. | 时间字符串 | 例子 |
1 | YYYY-MM-DD | 2010-12-30 |
2 | YYYY-MM-DD HH:MM | 2010-12-30 12:10 |
3 | YYYY-MM-DD HH:MM:SS.SSS | 2010-12-30 12:10:04.100 |
4 | MM-DD-YYYY HH:MM | 30-12-2010 12:10 |
5 | HH:MM | 12:10 |
6 | YYYY-MM-DDTHH:MM | 2010-12-30 12:10 |
7 | HH:MM:SS | 12:10:01 |
8 | YYYYMMDD HHMMSS | 20101230 121001 |
9 | now | 2013-05-07 |
可以使用“T”作为一个文字字符分隔日期和时间。
5.2 修饰符
随后的时间字符串可以由零个或多个的修饰符将改变日期和/或任何上述五大功能返回时间。修饰符应用于从左侧到右侧和下面的修饰符可在SQLite使用:
- NNN days
- NNN hours
- NNN minutes
- NNN.NNNN seconds
- NNN months
- NNN years
- start of month
- start of year
- start of day
- weekday N
- unixepoch
- localtime
- utc
5.3 格式化:
SQLite 提供了非常方便的函数strftime() 来格式化任何日期和时间。可以使用以下替换格式化的日期和时间:
替代 | 描述 |
%d | Day of month, 01-31 |
%f | Fractional seconds, SS.SSS |
%H | Hour, 00-23 |
%j | Day of year, 001-366 |
%J | Julian day number, DDDD.DDDD |
%m | Month, 00-12 |
%M | Minute, 00-59 |
%s | Seconds since 1970-01-01 |
%S | Seconds, 00-59 |
%w | Day of week, 0-6 (0 is Sunday) |
%W | Week of year, 01-53 |
%Y | Year, YYYY |
%% | % symbol |
5.4 例子
让我们尝试不同的例子,现在使用SQLite的提示的。以下计算当前的日期:
sqlite> SELECT date(‘now‘);
2013-05-07
以下计算当前月份的最后一天:
sqlite> SELECT date(‘now‘,‘start of month‘,‘+1 month‘,‘-1 day‘);
2013-05-31
以下计算给定的日期和时间的UNIX时间戳1092941466:
sqlite> SELECT datetime(1092941466, ‘unixepoch‘);
2004-08-19 18:51:06
以下计算UNIX时间戳1092941466抵消本地时区的日期和时间:
sqlite> SELECT datetime(1092941466, ‘unixepoch‘, ‘localtime‘);
2004-08-19 11:51:06
以下计算当前的UNIX时间戳:
sqlite> SELECT datetime(1092941466, ‘unixepoch‘, ‘localtime‘);
1367926057
以下计算的美国“独立宣言”签署以来的天数:
sqlite> SELECT julianday(‘now‘) - julianday(‘1776-07-04‘);
86504.4775830326
以下一个特别的时刻在2004年以来的秒数计算:
sqlite> SELECT strftime(‘%s‘,‘now‘) - strftime(‘%s‘,‘2004-01-01 02:34:56‘);
295001572
以下计算日期为当年10月的第一个星期二:
sqlite> SELECT date(‘now‘,‘start of year‘,‘+9 months‘,‘weekday 2‘);
2013-10-01
以下计算时间自UNIX纪元秒(类似strftime(‘%s‘,‘now‘) ,除了包括小数部分):
sqlite> SELECT (julianday(‘now‘) - 2440587.5)*86400.0;
1367926077.12598
UTC与本地时间值之间进行转换,格式化日期时间,使用UTC或localtime修改如下:
sqlite> SELECT time(‘12:00‘, ‘localtime‘);
05:00:00
sqlite> SELECT time(‘12:00‘, ‘utc‘);
19:00:00
SQLite时间处理