大家好，我是只谈技术不剪发的 Tony 老师。
今天我们来详细介绍一下 SQLite 中的日期和时间数据类型，以及相关的处理函数。
如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁
首先，SQLite 使用一种动态的数据类型系统，数据的类型只和数据本身有关，与字段定义的类型无关。例如，我们可以定义一个 INTEGER 类型的字段，然后用于存储字符串。这一点和其他数据库不同，通常数据库字段的类型决定了可以存储的数据类型。
其次，SQLite 没有提供专门用于存储日期和时间的数据类型。通常我们可以使用 TEXT、REAL 或者 INTEGER 存储日期和时间，SQLite 提供了支持这些数据类型的日期和时间函数。
- TEXT 存储日期和时间时使用 ISO8601 格式的字符串（”YYYY-MM-DD HH:MM:SS.SSS”）。
- REAL 存储日期时表示儒略日（Julian Day），也就是格里历（阳历）公元前 4714 年 11 月 24 日 UTC 正午到该时间的天数。
- INTEGER 存储日期和时间时表示 Unix 时间戳，也就是 1970-01-01 00:00:00 UTC 到该时间的秒数。
应用程序可以选择以上三种格式之一存储日期和时间，SQLite 内置函数可以支持它们之间的格式转换。
SQLite 提供了以下 5 种日期和时间函数：
以上函数都可以接收一个时间值作为参数，后面可以增加任意多个修饰符。strftime() 函数的第一个参数用于指定格式化字符串。
日期和时间函数使用 IS0-8601 日期和时间格式的一个子集。
date() 函数返回的日期格式为 YYYY-MM-DD，例如：
sqlite> select date();
2021-08-06
time() 函数返回的时间格式为 HH:MM:SS，例如：
sqlite> select time();
03:57:53
datetime() 函数返回的日期时间格式为 YYYY-MM-DD HH:MM:SS，例如：
sqlite> select datetime();
2021-08-06 03:58:39
julianday() 函数返回儒略日的天数，包括时间信息。例如：
sqlite> select julianday(), datetime();
2459432.66719566|2021-08-06 04:00:45
strftime() 函数返回日期和时间的格式由第一个参数中的格式化字符串决定。格式化字符串支持标准 C 代码库中最常用的控制符以及 %f 和 %J。以下是一个完整的 strftime() 控制符列表：
- %d 一月中的第几天：00-31
- %f 包含毫秒的秒数：SS.SSS
- %H 小时：00-24
- %j 一年中的第几天：001-366
- %J 儒略日的天数
- %m 月份：01-12
- %M 分钟：00-59
- %s 1970-01-01 以来的秒数
- %S 秒数：00-59
- %w 一周中的第几天：0-6，星期天为 0
- %W 一年中的第几周：00-53
- %Y 年份：0000-9999
- %% 字符 %
例如：
sqlite> select strftime('The date and time: %Y-%m-%d %H:%M:%S');
The date and time: 2021-08-06 06:34:09
实际上其他 4 个函数都可以使用 strftime() 函数表示：
- date(…) 等价于 strftime(‘%Y-%m-%d’, …)
- time(…) 等价于 strftime(‘%H:%M:%S’, …)
- datetime(…) 等价于 strftime(‘%Y-%m-%d %H:%M:%S’, …)
- julianday(…) 等价于 strftime(‘%J’, …)
例如：
sqlite> select datetime(), strftime('%Y-%m-%d %H:%M:%S');
2021-08-06 06:35:00|2021-08-06 06:35:00
提供这些函数主要是为了方便使用，提高效率。
时间值参数可以使用以下格式之一。时间值通常是一个字符串，只有在第 12 种格式时可以指定一个整数或者浮点数。
例如：
SELECT date('now');
SELECT strftime('%s','now');
SELECT julianday('now') - julianday('2000-01-01');
SELECT strftime('%s','now') - strftime('%s','2000-01-01 00:00:00');
SELECT (julianday('now') - 2440587.5)*86400.0;
第 5 种到第 7 种格式中的字符 T 用于分隔日期和时间，遵循 ISO-8601 标准。
第 8 种到第 10 种格式只包含时间信息，默认日期为 2000-01-01。
第 11 种格式中的字符串”now”表示当前日期和时间，时区为 UTC。
第 12 种格式是儒略日的天数，支持整型和浮点型数字值。
第 2 种到第 10 种格式可以增加一个时区标识符”[±]HH:MM”或者”Z”。日期和时间函数在内部使用 UTC 或者”zulu”时间，因此后缀”Z”没有实际作用。任何非零的”HH:MM”后缀都会从指定日期和时间中减去相应的时间，从而转化为 UTC 时间。例如，以下所有的时间值都等价：
2013-10-07 08:23:19.120
2013-10-07T08:23:19.120Z
2013-10-07 04:23:19.120-04:00
2456572.84952685
第 4、第 7 以及第 10 种格式中，小数秒 SS.SSS 可以包含一个或多个小数位数。示例中只显示了 3 位小数，因为只有前三位小数是有效数字，不过输入字符串中可以包含更少或更多小数，日期/时间函数可以正确处理这些情况。同样，第 12 中格式显示了 10 位数字，但是日期/时间函数可以接收能够表示为儒略日天数的任意位数的数字。
时间值参数的后面可以增加任意多个修饰符，用于改变日期和时间的数值。每个修饰符都会对其左侧的时间进行转换，修饰符从左至右依次应用。以下是可用的修饰符：
第 1 到第 6 个修饰符用于增加指定的时间。修饰符名称最后的字符”s”可以省略。例如：
sqlite> select date('2021-01-01', '+5 days');
2021-01-06
注意，”±NNN months”首先会将日期转化为 YYYY-MM-DD 格式，然后增加 ±NNN 月份，最后规范化结果。例如，日期 2001-03-31 使用修饰符 ‘+1 month’ 时首先产生 2001-04-31，由于 4 月份只有 30 天，因此最终规范化的结果为 2001-05-01。类似，如果原始数据是闰年中的 2 月 29 日，修饰符是 ±N years 并且 N 不是 4 的倍数，也会产生同样的效果。
第 7 到第 9 个修饰符用于将日期转换为当前月、年或者日的开始，例如：
sqlite> select date('now','start of month','+1 month','-1 day');
2021-08-31
第 10 个修饰符（weekday N）用于将日期转换为（如有必要）下一周中的指定日期。星期天为 0，星期一为 1，依次类推。如果被转换的日期已经是当前周中的指定日期，不做任何修改。例如
第 11 个修饰符（unixepoch）只能修改 DDDDDDDDDD 格式的时间值。该修饰符将 DDDDDDDDDD 转换为 Unix 时间戳（1970 年以来的秒数），而不是通常情况下的儒略日天数。例如：
sqlite> select datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06
如果 unixepoch 修饰符前面不是 DDDDDDDDDD 格式的时间值，或者它们之间存在其他修饰符，转换的结果不可预期。
第 12 个修饰符（localtime）将左侧的 UTC 时间值转换为本地时区对应的时间值。如果左侧的时间值不是 UTC 时区，转换的结果不可预期。例如：
sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
2004-08-20 02:51:06
第 13 个修饰符（utc）执行的转换操作和 localtime 正好相反，将左侧的本地时间转化为 UCT 时间。如果左侧的时间值不是本地时间，转换的结果不可预期。
Original: https://blog.csdn.net/horses/article/details/119256750
Author: 不剪发的Tony老师
Title: SQLite 中的日期和时间
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/815820/
转载文章受原作者版权保护。转载请注明原作者出处！