SQLite时间处理

最新推荐文章于 2021-08-06 16:29:54 发布

weixin_30446613

最新推荐文章于 2021-08-06 16:29:54 发布

阅读量555

点赞数 1

原文链接：http://www.cnblogs.com/wxc7009/p/5856271.html

版权

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')

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-20 11:00:00'

仔细查看会发现有问题，因为没有结果，实际表中是有结果的，这是为什么，其实我也不没有搞清楚。这个问题还是在国外的一个

论坛发现解决方法的。只要改一下上面的语句就可以了

select count(*) from T where statue='1' and datetime([date])=datetime('2009-03-20 11:00:00')

or

select count(*) from T where statue='1' and datetime([date])='2009-03-20 11: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 mytime from 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

转载于:https://www.cnblogs.com/wxc7009/p/5856271.html

weixin_30446613

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQLite时间处理

sqlite数据库处理时间问题和日期时间函数首先,sqlite数据库在时间处理上和sqlserver还有oracle不同,下面根据自己做过的实例总结一下.创建了一个Log数据表: LogID 　　SourceID 　　OperatorID 　　LogType LogLevel 　　LogTime 　　　　　　LogContent 1 　　　　aaa....
复制链接

扫一扫