The way to store dates in SQLite is: yyyy-mm-dd hh:mm:ss.sss

  • insert into myTable (name,mydate) Values ('fred','2009-01-02 13:22:15')
  • insert (update_time) values (datetime(CURRENT_TIMESTAMP,'localtime'))
  • CURRENT_TIME – Inserts only time
  • CURRENT_DATE – Inserts only date
  • CURRENT_TIMESTAMP – Inserts both time and date

SQLite 支援下列五種時間日期函數。日期時間的範圍為 0000-01-01 00:00:00 至 9999-12-31 23:59:59 (julidan day numbers 1721059.5 ~ 5373484.5)。

  1. strftime(format, timestring, modifier, modifier, ...)
    返回值格式則根據 format 所設定。
  2. date(timestring, modifier, modifier, ...)
    返回值格式為 YYYY-MM-DD。
    相等於 strftime('%Y-%m-%d', ...)。
  3. time(timestring, modifier, modifier, ...)
    返回值格式為 HH:MM:SS。
    相等於 strftime('%H:%M:%S', ...)
  4. datetime(timestring, modifier, modifier, ...)
    返回值格式為 YYYY-MM-DD HH:MM:SS。
    相等於 strftime('%Y-%m-%d %H:%M:%S', ...)
  5. julianday(timestring, modifier, modifier, ...)
    返回值為 Julian day,也就是計算從格林威治時間西元前 4714 年 11 月 24 日到 timestring 的日子。
    相等於 strftime('%J', ...)

format

strftime 函數中第一個參數 format 字串所支援的輸出格式如下所示。標準 C 函數庫 strftime 函數的輸出格式,strftime 函數支援大多數常用的,並且新增二種新的格式 %f 及 %J。

  1. %d(00) 一個月的第幾天。
  2. %f(SS.SSS) 精準至小數點第三位的秒。
  3. %H(00-24) 小時。
  4. %j(001-366) 一年的第幾天。
  5. %J 儒略日(Julian day)是指由公元前4713年1月1日,世界標準時間中午12時開始所經過的天數,多為天文學家採用,用以作為天文學的單一曆法,把不同曆法的年表統一起來。
  6. %m(01-12) 一年的第幾月。
  7. %M(00-59) 分鐘。
  8. %s 自西元 1970-01-01 開始的秒數。
  9. %S(00-59) 秒。
  10. %w(0-6) 一週的第幾天,星期日為0。
  11. %W(00-53) 一年的第幾週。
  12. %Y(0000-9999) 年份。
  13. %% 傳回 % 符號。

timestring

timestring 需符合下列的格式。

  1. now 傳回目前的日期與時間,使用 sqlite3_vfs 的 xCurrentTime 函數。傳回的時區是 UTC+0,而非台灣的 UTC+08,要注意加上 'localtime' modifier。
  2. DDDDDDDDDD Julian day。
  3. YYYY-MM-DD
  4. YYYY-MM-DD HH:MM
  5. YYYY-MM-DD HH:MM:SS
  6. YYYY-MM-DD HH:MM:SS.SSS
  7. YYYY-MM-DDTHH:MM
  8. YYYY-MM-DDTHH:MM:SS
  9. YYYY-MM-DDTHH:MM:SS.SSS
  10. HH:MM
  11. HH:MM:SS
  12. HH:MM:SS.SSS

7~9 中的字母 T 是用來分隔日期與時間的符號,ISO-8601 規定的。

10~12 的日期固定為 2000-01-01。

modifier

timestring 的日期時間可以透過 modifier 來變更。日期時間變更的順序是由左而右,所以 modifier 的位置很更重要。

  1. ±NNN hours 增加 ±NNN 小時。
  2. ±NNN minutes 增加 ±NNN 分鐘。
  3. ±NNN.NNNN seconds 增加 ±NNN 秒。
  4. ±NNN days 增加 ±NNN 天數。
  5. ±NNN months 增加 ±NNN 月,增加之後會重新計算日期,以避免不存在的日期。比如 2001-03-31 modifier '+1 month' 得到 2001-04-31,但 4 月沒有 31 日,所以會變成 2001-05-01。
  6. ±NNN years 增加 ±NNN 年。當 ????-02-29 增加 ±NNN 年時,日期會被重新計算,以避免不存在的日期。
  7. start of month 將日期時間移到 timestring 的年初,也就是 ????-01-01T00:00:00。
  8. start of year 將日期時間移到 timestring 的月初,也就是 ????-??-01T00:00:00。
  9. start of day 將日期時間移到 timestring 的日初,也就是 ????-??-??T00:00:00。
  10. weekday N 將日期移至下一個星期 N(0-6, 星期日為 0),相同星期N則不改變,也就是只能改變 6 日而已。
  11. unixepoch 只適用於 timestring=DDDDDDDDDD。把 DDDDDDDDDD 為 Julian day 改變成 Unix Time,也就是自 1970 年 1 月 1 日 00:00:00 以來的秒數。unixepoch 必須是第一個 modifier,藉此來改變 timestring 的性質。
  12. localtime 此修飾詞會先假設 timestring 的時區為 UTC+0,再調整時間為本地時間。
  13. utc 此修飾詞會先假設 timestring 的時區為本地時區,再調整時間為 UTC+0 時區。

example

  1. SELECT * FROM a WHERE q_date = '2012-06-04 05:06:00'
  2. SELECT * FROM table WHERE date BETWEEN "2010-01-01" AND "2013-01-01"
  3. SELECT * FROM table WHERE strftime('%s', date) BETWEEN strftime('%s', start_date) AND strftime('%s', end_date)
  4. SELECT * FROM table WHERE date > datetime('now', 'localtime')

參加原文網址:http://www.sqlite.org/lang_datefunc.html

台南小新 發表在 痞客邦 PIXNET 留言(0) 人氣()