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時間處理