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

server/2024/11/20 18:35:23/

项目场景

数据插入、更新、查询

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

具体需求如下:

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

环境介绍


问题描述及方法

根据时间查询

  • 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 用于解决已经插入的数据受到键值约束插入失败的问题。

http://www.ppmy.cn/server/143517.html

相关文章

使用OpenUI智能生成专业级网页UI实现远程高效前端开发新手指南

文章目录 前言1. 本地部署Open UI1.1 安装Git、Python、pip1.2 安装Open UI 2. 本地访问Open UI3. 安装Cpolar内网穿透4. 实现公网访问Open UI5. 固定Open UI 公网地址 前言 今天给大家带来一篇非常实用的技术分享&#xff0c;介绍如何在Windows系统本地部署OpenUI&#xff0c…

linux从0到1——shell编程5

声明&#xff01; 学习视频来自B站up主 **泷羽sec** 有兴趣的师傅可以关注一下&#xff0c;如涉及侵权马上删除文章&#xff0c;笔记只是方便各位师傅的学习和探讨&#xff0c;文章所提到的网站以及内容&#xff0c;只做学习交流&#xff0c;其他均与本人以及泷羽sec团队无关&a…

初识Arkts

创建对象&#xff1a; 类&#xff1a; 类声明引入一个新类型&#xff0c;并定义其字段、方法和构造函数。 定义类后&#xff0c;可以使用关键字new创建实例 可以使用对象字面量创建实例 在以下示例中&#xff0c;定义了Person类&#xff0c;该类具有字段name和surname、构造函…

如何恢復電腦IP地址的手動設置?

手動設置IP地址後&#xff0c;可能會遇到一些網路連接問題&#xff0c;或者需要恢復到之前的自動獲取狀態。這篇文章將詳細介紹如何恢復電腦的IP地址設置。 為什麼需要恢復IP地址設置&#xff1f; 網路連接問題&#xff1a;手動設置IP地址後&#xff0c;可能會導致與路由器或…

LDR6500:C to DP与DP to C视频转接线双向方案

在当前的数字时代&#xff0c;投屏技术和视频接口转换已成为连接不同设备、共享内容的常用手段。LDR6500&#xff0c;作为乐得瑞科技精心研发的USB Power Delivery&#xff08;PD&#xff09;协议芯片&#xff0c;凭借其卓越的性能和广泛的兼容性&#xff0c;在C-to-DP&#xf…

2020.7 官方综述-UMLS users and uses: a current overview

Liz Amos, David Anderson, Stacy Brody, UMLS users and uses: a current overview | Journal of the American Medical Informatics Association | Oxford Academic 问题 直接用户使用情况&#xff1a;探究统一医学语言系统&#xff08;UMLS&#xff09;直接用户的增长、现…

数据库课程设计全流程:方法与实例解析

--- ### 一、数据库课程设计概述 数据库课程设计是学习数据库理论知识的重要实践环节&#xff0c;旨在帮助学生掌握数据库设计和应用系统开发的完整流程&#xff0c;包括需求分析、数据库设计、功能实现以及性能优化。 #### **设计目标** 1. 掌握数据库设计的基本步骤和原则…

基于yolov8、yolov5的植物类别识别系统(含UI界面、训练好的模型、Python代码、数据集)

项目介绍 项目中所用到的算法模型和数据集等信息如下&#xff1a; 算法模型&#xff1a;     yolov8、yolov8 SE注意力机制 或 yolov5、yolov5 SE注意力机制 &#xff0c; 直接提供最少两个训练好的模型。模型十分重要&#xff0c;因为有些同学的电脑没有 GPU&#xff0…