Android Sqlite對Date類型操作總結

Android Sqlite對Date類型操作總結
首先,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')