sqlite 时间

1. 简介

1.1 什么是 SQLite

SQLite 是一种嵌入式关系型数据库管理系统，是世界上最流行的轻量级数据库之一。它是一个自包含、无服务器的、无配置的、可依赖的、可运行在各种操作系统上的 C 库。SQLite 的设计目标是将数据库引擎嵌入到应用程序中，使得应用程序可以直接读写数据库文件，而不需要与一个单独的服务器进程进行通信。由于其轻量级和易用性，SQLite 被广泛应用于移动设备、嵌入式系统和小型应用程序中。

1.2 SQLite 的日期和时间

SQLite 支持多种日期和时间数据类型，包括以下几种常用类型：

• 文本格式：包括 YYYY-MM-DD、YYYY-MM-DD HH:MM、YYYY-MM-DD HH:MM:SS 等格式。
• 整数格式：Unix 时间戳，以秒为单位的整数值。
• 实数格式：Julian day 格式和 Unix 时间戳，以秒为单位的实数值。

在 SQLite 中，日期和时间的处理是非常灵活的，支持对日期和时间进行存储、计算和格式化，下面将详细介绍 SQLite 中日期和时间的使用方法。

2. 函数和指令

2.1 日期和时间函数

SQLite 提供了许多内置函数来处理日期和时间数据，以下是一些常用的日期和时间函数：

• date()：返回当前日期，格式为 YYYY-MM-DD。
• time()：返回当前时间，格式为 HH:MM:SS。
• datetime()：返回当前日期和时间，格式为 YYYY-MM-DD HH:MM:SS。
• julianday()：将日期时间转换为 Julian day 格式的实数值。
• strftime()：根据指定的格式将日期时间格式化为字符串。

2.2 数据库指令

在 SQLite 中，我们可以使用 SQL 语句来操作数据库中的表和数据。下面是一些常用的日期和时间相关的 SQL 指令：

• CREATE TABLE：创建表格。
• ALTER TABLE：修改表格。
• INSERT INTO：向表格中插入数据。
• UPDATE：更新表格中的数据。
• DELETE FROM：删除表格中的数据。
• SELECT：从表格中查询数据。

3. 日期和时间的存储和格式化

3.1 存储日期和时间

在 SQLite 中存储日期和时间有多种方式，可以通过文本格式、整数格式和实数格式进行存储。

3.1.1 文本格式

可以使用文本格式来存储日期和时间，例如将日期存储为 YYYY-MM-DD 的格式，将日期和时间存储为 YYYY-MM-DD HH:MM:SS 的格式。在创建表格时，可以将日期和时间字段的类型定义为 TEXT。

示例代码如下：

CREATE TABLE data (
    id INTEGER PRIMARY KEY,
    date TEXT,
    time TEXT
);

INSERT INTO data (date, time) VALUES ('2022-01-01', '12:00:00');

3.1.2 整数格式

SQLite 支持将日期和时间存储为整数格式，一种常用的整数格式是 Unix 时间戳，以秒为单位的整数值。在创建表格时，可以将日期和时间字段的类型定义为 INTEGER。

示例代码如下：

CREATE TABLE data (
    id INTEGER PRIMARY KEY,
    timestamp INTEGER
);

INSERT INTO data (timestamp) VALUES (1640995200);

3.1.3 实数格式

SQLite 也支持将日期和时间存储为实数格式，一种常用的实数格式是 Julian day 格式和 Unix 时间戳，以秒为单位的实数值。在创建表格时，可以将日期和时间字段的类型定义为 REAL。

示例代码如下：

CREATE TABLE data (
    id INTEGER PRIMARY KEY,
    julian_day REAL
);

INSERT INTO data (julian_day) VALUES (2459590.5);

3.2 格式化日期和时间

SQLite 提供了函数 strftime() 来将日期和时间格式化为指定的字符串。strftime() 函数的第一个参数是格式化字符串，用于指定输出的格式，其中特定的占位符将被替换为日期和时间的值。

常用的格式化占位符有：

• %Y：四位数的年份
• %m：两位数的月份
• %d：两位数的天数
• %H：两位数的小时数（24 小时制）
• %M：两位数的分钟数
• %S：两位数的秒数

示例代码如下：

SELECT strftime('%Y-%m-%d', 'now'); -- 输出当前日期，格式为 YYYY-MM-DD
SELECT strftime('%H:%M:%S', 'now'); -- 输出当前时间，格式为 HH:MM:SS
SELECT strftime('%Y-%m-%d %H:%M:%S', 'now'); -- 输出当前日期和时间，格式为 YYYY-MM-DD HH:MM:SS

4. 日期和时间的计算

4.1 计算差值

在 SQLite 中，可以使用减法操作符 - 来计算两个日期或时间之间的差值，得到的结果是以天、小时、分钟或秒为单位的整数值。

示例代码如下：

SELECT julianday('2022-01-01') - julianday('2021-01-01'); -- 计算日期之差，得到的结果是以天为单位的整数值
SELECT (julianday('2022-01-01') - julianday('2021-01-01')) * 24; -- 计算小时之差，得到的结果是以小时为单位的整数值
SELECT (julianday('2022-01-01') - julianday('2021-01-01')) * 24 * 60; -- 计算分钟之差，得到的结果是以分钟为单位的整数值
SELECT (julianday('2022-01-01') - julianday('2021-01-01')) * 24 * 60 * 60; -- 计算秒之差，得到的结果是以秒为单位的整数值

4.2 添加/减去时间间隔

在 SQLite 中，可以使用 strftime() 函数和 datetime() 函数来添加或减去指定的时间间隔。

示例代码如下：

SELECT datetime('now', '+1 year'); -- 当前日期加上一年
SELECT datetime('now', '-1 month'); -- 当前日期减去一个月
SELECT datetime('now', '+1 day'); -- 当前日期加上一天
SELECT datetime('now', '-1 hour'); -- 当前时间减去一个小时
SELECT datetime('now', '+1 minute'); -- 当前时间加上一分钟
SELECT datetime('now', '-1 second'); -- 当前时间减去一秒

5. 示例

下面通过一个示例来演示 SQLite 中日期和时间的使用方法。

我们创建一个 sales 表，其中包括 id、product、price、sale_date 四个字段。id 是唯一标识符，product 是产品名称，price 是产品价格，sale_date 是销售日期。

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product TEXT,
    price REAL,
    sale_date TEXT
);

插入一些销售记录：

INSERT INTO sales (product, price, sale_date)
VALUES ('手机', 1000, '2022-01-01');
INSERT INTO sales (product, price, sale_date)
VALUES ('电视', 2000, '2022-01-02');
INSERT INTO sales (product, price, sale_date)
VALUES ('电脑', 3000, '2022-01-03');

查询销售日期为当前日期的销售记录：

SELECT * FROM sales WHERE sale_date = date('now');

查询销售日期在某个时间范围内的销售记录：

SELECT * FROM sales WHERE sale_date BETWEEN '2022-01-01' AND '2022-01-03';

计算每个产品的销售总额：

SELECT product, SUM(price) FROM sales GROUP BY product;

更新某个销售记录的销售日期：

UPDATE sales SET sale_date = '2022-02-01' WHERE id = 1;

删除某个销售记录：

DELETE FROM sales WHERE id = 2;

结论

SQLite 提供了丰富的日期和时间函数，可以方便地存储、计算和格式化日期和时间数据。开发者可以根据实际需求选择合适的存储格式，并使用相应的函数来操作日期和时间。通过灵活使用 SQLite 的日期和时间功能，可以更高效地管理和处理相关数据。