数据库管理-根据日期字段进行数据筛选更新数据

项目场景

数据插入、更新、查询

数据库中一张审计表格用来记录数据的操作包括数据的id，数据名称sjmc，数据状态sjzt，数据创建时间createtime，数据更新时间updatetime。

具体需求如下：

• 根据数据名称更新sjzt和updatetime。
• 根据更新时间选取数据修改sjzt。

环境介绍

• PostgreSQL
• 瀚高数据库（HighgoDB）

---

问题描述及方法

根据时间查询

• 1. 数据库中有一个审计表格audit，表格内更新时间updatetime（数据类型为timestamp），现在我想根据时间进行筛选

SELECT *
FROM audit
WHERE DATE(updatetime) = CURRENT_DATE;#或者SELECT *
FROM audit
WHERE DATE(updatetime) = '2024-11-19';#可以利用当天的起始时间（00:00:00）到结束时间（23:59:59）来确定一个时间范围，
SELECT *
FROM audit
WHERE updatetime BETWEEN CURRENT_DATE::timestamp AND (CURRENT_DATE + INTERVAL '1 DAY')::timestamp - INTERVAL '1 SECOND';

• DATE(updatetime) 会从 updatetime 列的每个 timestamp 值中提取出日期部分，比如 2024-11-19 10:30:00 这样的时间戳，会提取出 2024-11-19 这一日期值。
• CURRENT_DATE 是一个获取当前日期（不含时间部分）的函数，例如在 2024 年 11 月 19 日执行查询时，它就代表 2024-11-19 。
• CURRENT_DATE::timestamp 将当前日期转换为 timestamp 类型，实际上就是当天的起始时间点，例如 2024-11-19 00:00:00 。
• (CURRENT_DATE + INTERVAL ‘1 DAY’)::timestamp - INTERVAL ‘1 SECOND’ 先将当前日期加一天，然后转换为 timestamp 类型，再减去 1 秒，相当于获取到了当天最后时刻（2024-11-19 23:59:59），整体 BETWEEN 操作符就筛选出了 updatetime 列值在这个当天时间范围内的所有行。
• WHERE 子句则是筛选出 updatetime 列日期部分等于当前日期的所有行记录。

• 2. 选择updatetime 列的值与当前日期和指定时间（16:07）的数据

SELECT *
FROM audit
WHERE updatetime = CURRENT_DATE::timestamp + '16:07'::time;# 使用 DATE_TRUNC() 函数结合时间比较（按分钟截断）
SELECT *
FROM audit
WHERE DATE_TRUNC('minute', updatetime) = '2024-10-10 16:07:00'::timestamp;

• CURRENT_DATE::timestamp 首先获取当前日期，并将其转换为 timestamp 类型，这相当于得到了当天的起始时间点，例如在 2024 年 11 月 19 日执行查询时，就会得到 2024-11-19 00:00:00 这样的时间戳值。
• ‘16:07’::time 是将字符串表示的时间 16:07 转换为 time 类型。
• 通过加法操作将两者合并，就构造出了代表当天 16:07 这个具体时刻的时间戳（例如 2024-11-19 16:07:00 ），WHERE 子句利用这个构造好的时间戳去筛选出 updatetime 列与之完全相等的行记录。
• ‘2024-10-10 16:07:00’::timestamp 这个表达式是将字符串形式表示的时间 2024-10-10 16:07:00 转换为 timestamp 类型，以便可以和 updatetime 列中同样是 timestamp 类型的值进行比较。
• DATE_TRUNC(‘minute’, updatetime) 会按照分钟对 updatetime 列中的每个时间戳值进行截断操作，例如对于 2024-10-10 16:07:30 这样的时间戳，经过该函数处理后就会变为 2024-10-10 16:07:00 ，也就是舍去秒等更细的时间部分，只保留到分钟精度。

• 3.根据选取2024-10-10 16：07左右5分钟的数据

##可以使用获取以 2024-10-10 16:07 为中心，前后几分钟的数据，可以使用 BETWEEN 操作符结合时间的运算来实现SELECT *
FROM audit
WHERE updatetime BETWEEN ('2024-10-10 16:07:00'::timestamp - INTERVAL '5 minutes')AND ('2024-10-10 16:07:00'::timestamp + INTERVAL '5 minutes');##按照秒范围来选取（包含 16:07 前后30秒的数据）                      
SELECT *
FROM audit
WHERE updatetime BETWEEN ('2024-10-10 16:07:00'::timestamp - INTERVAL '30 seconds')AND ('2024-10-10 16:07:00'::timestamp + INTERVAL '30 seconds');#获取以 2024-10-10 16:07 为中心，前后各 2 小时这个时间区间内的数据
SELECT *
FROM audit
WHERE updatetime BETWEEN ('2024-10-10 16:07:00'::timestamp - INTERVAL '2 hours')AND ('2024-10-10 16:07:00'::timestamp + INTERVAL '2 hours');##使用比较运算符（>、<、>=、<=）组合（适用于更灵活的区间界定，比如不包含边界值等情况）
SELECT *
FROM audit
WHERE updatetime >= ('2024-10-10 16:07:00'::timestamp - INTERVAL '2 hours')AND updatetime <= ('2024-10-10 16:07:00'::timestamp + INTERVAL '2 hours');

• ‘2024-10-10 16:07:00’::timestamp 先将字符串形式表示的时间 2024-10-10 16:07:00 转换为 timestamp 类型，代表 2024-10-10 16:07 这个时刻的时间戳。
• INTERVAL ‘5 minutes’ 表示一个时间间隔为 5 分钟，通过减法和加法运算，分别得到了这个时刻往前推 5 分钟（2024-10-10 16:02:00 ）和往后推 5 分钟（2024-10-10 16:12:00 ）的时间戳。
• INTERVAL ‘30 seconds’ 表示 30 秒的时间间隔
• INTERVAL ‘2 hours’ 表示一个时间间隔为 2 小时
• BETWEEN 操作符则筛选出 updatetime 列中时间戳值处于这两个时间戳所界定的区间（即从 2024-10-10 16:02:00 到 2024-10-10 16:12:00 ）内的所有行记录。

---

• 4.插入数据时，当 id 已经存在就跳过插入操作，继续执行后面的语句，可以使用 ON CONFLICT 子句来实现

INSERT INTO tab_sjly("id", "sjmc")
VALUES('1c879377-991f-4b67-a78a-8bc0dd804657', 'XZQ_shi'),('e7a2ddec-775b-40b3-88db-331f60fb3931', 'XZQ_地级市行政中心'),('0749e643-b636-4ee3-960f-30c647a09b36', 'a_gsjyrk')
ON CONFLICT ("id") DO NOTHING;

• ON CONFLICT (“id”) 这部分表示当插入的数据在 “id” 列出现冲突时（也就是要插入的 id 值在表中已经存在了），进行后续的指定操作。
• DO NOTHING 就是指定的操作，表示在遇到 id 冲突的情况下，什么都不做，直接跳过当前这条插入语句对应的插入行为，然后继续执行后面的插入语句或者其他相关的后续语句（如果有的话）。

---

小结

1. 使用 BETWEEN 操作符（适用于包含边界值的区间查询）
2. 只查询大于起始时间且小于结束时间的数据（不包含边界值），查询大于等于起始时间且小于等于结束时间的数据（包含边界值）：使用比较运算符（>、<、>=、<=）组合（适用于更灵活的区间界定，比如不包含边界值等情况）。
3. 使用 DATE_TRUNC() 函数结合时间区间查询（常用于按照特定时间精度来界定区间，比如按天、按小时等）
4. ON CONFLICT (“id”) DO NOTHING 用于解决已经插入的数据受到键值约束插入失败的问题。