sqlite3使用日期、时间数据类型

S Sqlite3qlite3 使用日期使用日期、时间时间数据类型数据类型 （梁仲宏梁仲宏 - - 20172017- -0808- -1818 于深圳）于深圳） 1. sqlite3 支持如下日期支持如下日期、时间数据类型。时间数据类型。 date 包含了 年份、月份、日期。 time 包含了 小时、分钟、秒。 timestamp 包含了 年、月、日、时、分、秒、千分之一秒。 2. date、time 、timestamp 说明说明 sqlite3 对“date” ， “time” ， “timestamp”是作为字符串处理，但是这些字符格式比较特殊而已,，格式总结如下。 2.1. 对于“对于“date” ，合法格式是：” ，合法格式是： YYYY-MM-DD 2.2. 对于“对于“time” ，合法格式是：” ，合法格式是： HH:MM HH:MM:SS HH:MM:SS.SSS 2.3. 对于“对于“timestamp” ，合法格式是：” ，合法格式是： 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 3. sqlite3 内部内部日期日期、时间操作时间操作函数函数。 3.1. 内部函数内部函数 Sqlite3 内部提供了如下函数操作日期，时间。分别说明如下： 1. date(timestring, modifier, modifier, .) 功能：对输入时间 timestring，按 modifier 调整后输出日期字符串“YYYY-MM-DD” 。 注意：timestring 是必须的。modifier 可有可无。 2. time(timestring, modifier, modifier, .) 功能：对输入时间 timestring，按 modifier 调整后输出时间字符串“HH:MM:SS” 。 注意：timestring 是必须的。modifier 可有可无。 3. datetime(timestring, modifier, modifier, .) 功能：对输入时间 timestring，按 modifier 调整后输出日期，时间字符串“YYYY-MM-DD HH:MM:SS” 。 注意：timestring 是必须的。modifier 可有可无。 4. julianday(timestring, modifier, modifier, .) 功能：按格林威治时间计算，从公元前 4714 年 11 月 24 号开始算起到“timestring”之间的总天数。如果有“modifier”参数， 则是修正后的总天数。 5. strftime(format, timestring, modifier, modifier, .) 功能：对输入时间“timestring” ，按 modifier 调整后的时间按 format 格式化输出，它是最灵活的。 注意： 1：format 和 timestring 是必须的。modifier 可有可无。 2：格式化字符： %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 日开始按格林威治时区计算经过的秒数，即 UTC 时间。 %S ：秒 00-59 %w ：星期，0-6，0 是星期天 %W ：一年中的第几周 00-53 %Y ：年份 0000-9999 % ：%百分号 3.2. Time Strings 上面 5 个函数必须要有一个时间输入，可以是以下格式字符串。 1. YYYY-MM-DD 2. YYYY-MM-DD HH:MM 3. YYYY-MM-DD HH:MM:SS 4. YYYY-MM-DD HH:MM:SS.SSS 5. YYYY-MM-DDTHH:MM 6. YYYY-MM-DDTHH:MM:SS 7. YYYY-MM-DDTHH:MM:SS.SSS 8. HH:MM 9. HH:MM:SS 10. HH:MM:SS.SSS 11. now 12. DDDDDDDDDD 说明如下： 格式 2 到格式 10，默认是按格林威治时间计算的（0 时区）。 “YYYY-MM-DDTHH:MM”, “YYYY-MM-DDTHH:MM:SS”, “YYYY-MM-DDTHH:MM:SS.SSS”这里的字符“T”是日期和 时间分割符，它是“ ISO-8601”时间格式标准。 “HH:MM”, “HH:MM:SS” , “HH:MM:SS.SSS”默认日期是“2000-01-01” “now”读取系统时间，返回格林威治时间（0 时区）“YYYY-MM-DD HH:MM:SS.SSS” 。 “DDDDDDDDDD”得到公元前 4713 年 1 月 1 日到现在总天数。 “YYYY-MM-DD HH:MM:SS.SSS” ， “YYYY-MM-DDTHH:MM:SS.SSS” ， “HH:MM:SS.SSS”值输入 3 位小数即可，但更多小 数也无所谓，对计算结果无影响。 3.3. 修正参数修正参数“modifier”列表列表： 修正参数“modifier”可有可无，如果有多个修正参数“modifier” ，按从左到右原则依次修正，而且后修正参数是基于前修正 参数结果的再次修正。支持修正字符串列表如下： 1. NNN days 2. NNN hours 3. NNN minutes 4. NNN.NNNN seconds 5. NNN months 6. NNN years 7. start of month 8. start of year 9. start of day 10. weekday N 11. unixepoch 12. localtime 13. utc 说明如下： “NNN days”, “NNN hours”, “NNN minutes”, “NNN.NNNN seconds”, “NNN months”, “NNN years”是简单的常 量修正。如果 NNN 是正数，则在参考时间基础上加上这个常量对应的秒。如果 NNN 是负数，则在参考时间基础上减去这 个常量对应的秒。 “start of year”, 得到输入时间对应年的第一日。 “start of month”, 得到输入时间对应月的第一日。 “start of day” 。得到输入时间对应 00:00:00。 “weekday N” 得到输入日期对应周几。周日是 0、周一是 1 、依次类推、周六是 6。 “unixepoch” 按 1970-01-01 00:00:00 计算经历的秒数。 “localtime” 。输入时间是 UTC 时间，把 UTC 转换成本地时区时间，否则是格林威治时间（0 时区） 。如果输入时间不是 utc 的，则结果未知。 “utc” 。和“localtime”相对，把时间转为格林威治时间（0 时区） 。 3.4. 命令命令例子：例子： 为了演示如下例子，要创建一个临时数据库。在 linux 命令行下执行命令：$ sqlite3 tmp.db 进入 sqlite3 命令行模式。 在 sqlite3 命令行模式执行下面命令： select datetime(now); 结果：2006-10-17 12:55:54 SELECT date(now,start of month); 结果：2017-08-01 说明：得到当前日期所在月份第一天日期。 SELECT date(now,start of month,+1 month); 结果：2017-09-01 说明：得到当前日期所在月份的下一个月的第一天日期。 过程：两个修正参数，从左到右处理。第 1 步用start of month处理，得到 2017-08-01。第 2 步，在“2017-08-01”基础上 再用“+1 month”修正（加一个月） ，所以为“2017-09-01” 。 SELECT date(now,start of month,+1 month,-1 day); 结果：2017-08-31 说明：得到当前日期所在月份最后一天日期。 过程：三个修正参数，从左到右处理。第 1 步用start of month处理，得到 2017-08-01。第 2 步，在“2017-08-01”基础上 再用“+1 month”修正（加一个月）为“2017-09-01” 。最后用“-1 day”修正（减一天）为“2017-08-31” 。 SELECT strftime(%s,now); 结果：1503027014 说明：修正参数“now”得到系统当前 utc 时间，格式“%s”从 1970-1-1 0:0:0 开始到现在的秒数，即 UTC 时间。 SELECT datetime(1503027014, unixepoch); 结果：2017-08-18 03:30:14 说明：修正参数“unixepoch”就是把 utc（从 1970-1-1 0:0:0 开始算）转为格林威治时间。 SELECT datetime(1503027014, unixepoch,localtime); 结果：2017-08-18 11:30:14 说明：修正参数“unixepoch”就是把 utc（从 1970-1-1 0:0:0 开始算）转为当地时间（计算机为东 8 区时间） 。 SELECT julianday(now); 结果：2457983.6507128 说明：计算从公元前 4714 年 11 月 24 号到现在经过多少天。 SELECT julianday(now) - julianday(2017-08-17); 结果：1.14949641190469 说明：计算 2017-08-17 到现在经过几天。 SELECT strftime(%s,now) - strftime(%s,2004-01-01 02:34:56); 结果：430103048 说明：计算 2004-01-01 02:34:56 到现在经历了多少秒。两个 utc 差值。 SELECT date(now,start of year,+9 months,weekday 2); 结果：2017-10-03 说明：得到现在时间所在年的第 10 个月中第一个周二日期。 过程：三个修正参数，从左到右处理。第 1 步用 start of year 处理，得到 2017-01-01。第 2 步，在“2017-01-01”基础上再 用“+9 months”修正（加 9 个月）为“2017-10-01” 。最后用“weekday 2”修正为“2017-10-03” 。 select datetime(2006-10-17); 结果：2006-10-17 00:00:00 说明：输入时间无 hh:mm:ss。默认是“00:00:00” select datetime(2006-10-17 00:20:00,+1 hour,-12 minute); 结果：2006-10-17 01:08:00 过程： 两个修正参数， 从左到右处理。 第 1 步用 +1 hour 处理， 得到 2006-10-17 01:20:00。 第 2 步， 在 “2006-10-17 01:20:00” 基础上再用“-12 minute”修正（减 12 分）为“2006-10-17 01:08:00” 。 select date(2006-10-17,+1 day,+1 year); 结果：2007-10-18 过程：两个修正参数，从左到右处理。第 1 步用 +1 day 处理，得到 2006-10-18。第 2 步，在“2006-10-18”基础上再用“+1 year”修正（加一年）为“2007-10-18” 。 select datetime(now,start of year); 结果：2017-01-01 00:00:00 select datetime(now,start of day); 结果：2017-08-18 00:00:00 select datetime(now,+10 hour,start of day,+10 hour); 结果：2017-08-18 10:00:00 过程：三个修正参数，从左到右处理。第 1 步用 +10 hour 处理，第 2 步，用“start of day”修正时间一定为“00-00-00” 。 第 3 步用“+10 hour”修正为“10:00:00” 。 select datetime(now,+8 hour); 结果：2017-08-18 11:55:42 说明：读取现在时间，按格林威治时区转换成东八区输出。 select strftime(%Y-%m-%d %H:%M:%S,now,localtime); 结果：2017-08-18 11:57:36 说明：等价于 select datetime(now,localtime); select strftime(%Y-%m-%d,now,localtime); 结果：2017-08-18 说明：等价于 select date(now,localtime); select strftime(%H:%M:%S ,now,localtime); 结果：11:59:15 说明：等价于 select time(now,localtime); select date(2017-08-18T01:01:01,localtime); 结果：2017-08-18 说明：输入时间格式按ISO-8601格式。 select time(2017-08-18T01:01:01,localtime); 结果：09:01:01 说明：输入时间格式按ISO-8601格式。 select datetime(2017-08-18T01:01:01,localtime); 结果：2017-08-18 09:01:01 说明：输入时间格式按ISO-8601格式。 select datetime(2017-8-18T01:01:01,localtime); 结果：无任何输出 说明：输入格式不对。必须是“YYYY-MM-DD”格式。不存在的位置补“0”,应该是“2017-8-18” select time(2017-08-18T1:01:01,localtime); 结果：无任何输出 说明：输入格式不对。要符合“hh-mm-ss”格式。不存在的位置补“0”,应该是“2017-08-18T01:01:01” select time(2017-08-18T01:01,localtime); 结果：09:01:00 说明：无秒自动为 00。 select time(2017-08-18T01:1,localtime); 结果：无任何输出 说明：输入格式不对。要符合“hh-mm”格式。不存在的位置补“0”,应该是“2017-08-18T01:01” select time(2017-08-18 01:01:01,localtime); 结果：09:01:01 说明：时间为普通字符串格式： “YYYY-MM-DD hh:mm:ss” 。 “YYYY-MM-DD”和“hh:mm:ss”之间可多个空格 select date(2017-08-18 01:01:01,localtime); 结果：2017-08-18 select datetime(2017-08-18 01:01:01,localtime); 结果：2017-08-18 09:01:01 select datetime(2017-8-18 01:01:01,localtime); 结果：无任何输出 说明：输入格式不对。必须是“YYYY-MM-DD”格式。不存在的位置补“0”,应该是“2017-08-18” select datetime(2017-8-18 01:1:01,localtime); 结果：无任何输出 说明：输入格式不对。必须是“hh:mm:ss”格式。不存在的位置补“0”,应该是“2017-08-18 01:01:01” 3.5. 总结：总结： 通过 3.4 例子，strftime 是最基础，最基本函数。下面几个函数是 strftime 特例。它们可通过 strftime 配置 format 得到 date(.) 等价于：strftime(%Y-%m-%d, .) time(.) 等价于：strftime(%H:%M:%S, .) datetime(.) 等价于：strftime(%Y-%m-%d %H:%M:%S, .) julianday(.) 等价于：strftime(%J, .) 这几个函数执行速度和效率比strftime 快。 4. 数据库设计例子数据库设计例子 1 4.1. 第一步：建立数据库。第一步：建立数据库。 登录 linux，执行：$ sqlite3 date_time.db 进入 sqlite3 命令行模式如下。 SQLite version 3.8.6 2014-08-18 11:46:33 Enter .help for usage hints. sqlite 在“4.1” sqlite3 命令行模式执行如下 sqlite3 语句。 CREATE TABLE IF NOT EXISTS date_time (id INTEGER PRIMARY KEY AUTOINCREMENT,date_section date DEFAULT ( date(now,localtime) ),time_section time DEFAULT ( time(now,localtime) ),timestamp_section timestamp DEFAULT ( datetime(now,localtime) ) ); 输入.exit 退出 sqlite3 命令模式。 4.2. 分别执行如下分别执行如下命令命令加入数据加入数据 1. 默认值插入。因为建表时候，如果没有值，默认通过 sqlite3 的日期，时间函数得到它们值。 sqlite3 /tmp/date_time.db replace into date_time(id) values(NULL); 2. 普通字符串插入。 sqlite3 /tmp/date_time.db replace into date_time values(null,2017-08-15,08:11:15,2017-08-15 08:11:15); sqlite3 /tmp/date_time.db replace into date_time values(null,2017-08-16,09:12:16,2017-08-16 09:12:16); sqlite3 /tmp/date_time.db replace into date_time values(null,2017-08-17,10:13:17,2017-08-17 10:13:17); sqlite3 /tmp/date_time.db replace into date_time values(null,2017-08-18,11:14:18,2017-08-18 11:14:18); 3. 通过 sqlite3 日期，时间函数插入。注意时间函数一定要用小括号“()” 。 sqlite3 /tmp/date_time.db replace into date_time values(null,( date(now,localtime) ),( time(now,localtime) ), ( datetime(now,localtime) ) ) 4. 读取所有记录，执行如下命令。 sqlite3 -header -column /tmp/date_time.db select * from date_time; 5. 读取当天日期的记录。 sqlite3 -header -column /tmp/date_time.db select * from date_time where date_section=(date(now,localtime) 注意： 1 要加”localtime”参数转成本地时间，否则是 0 时区时间。 2 date 函数要用小括号“()” 。 6. 读取后一天的记录。 sqlite3 -header -column /tmp/date_time.db select * from date_time where date_section=(date(now,+1 day,localtime) 注意： 1 要加”localtime”参数转成本地时间，否则是 0 时区时间。 2 date 函数要用小括号“()” 。 3 参数“+1 day”向后一天。 7. 读取前一天的记录。 sqlite3 -header -column /tmp/date_time.db select * from date_time where date_section=(date(now,-1 day,localtime) 注意： 1 要加”localtime”参数转成本地时间，否则是 0 时区时间。 2 date 函数要用小括号“()” 。 3 参数“-1 day”向前一天。 8. 读取前两天的记录。 sqlite3 -header -column /tmp/date_time.db select * from date_time where date_section=(date(now,-2 day,localtime) 注意： 1 要加”localtime”参数转成本地时间，否则是 0 时区时间。 2 date 函数要用小括号“()” 。 3 参数“-2 day”向前两天。 9. 读取某天记录（根据输入的日期字符串） 。 sqlite3 -header -column /tmp/date_time.db select * from date_time where date_section=2017-08-17 注意： 日期格式一定要按 yyyy-mm-dd 格式。无高为的补零，不能空，因为 sqlite3 内部按字符串比较的。 执行 sqlite3 -header -column /tmp/date_time.db select * from date_time where date_section=2017-8-17无结果输出。 5. 数据库设计例子数据库设计例子 2 5.1. 第一步：建立数据库。第一步：建立数据库。 登录 linux，执行：$ sqlite3 wifi_events.db 进入 sqlite3 命令行模式如下。 SQLite version 3.8.6 2014-08-15 11:46:33 Enter .help for usage hints. sqlite 在“5.1” sqlite3 命令行模式执行如下 sqlite3 语句。 CREATE TABLE IF NOT EXISTS wifi_events ( mac varcha