数据库中对时间的操作(mySql、Oracle、pgSql)

2023-12-14 16:41:25

目录

mySql

PGSQL

oracle


两个日期年数差

月数差

天数差

小时差

加一年

加一月

加一天

加一小时

加一分钟

加一秒

mySql

-- %Y-%m-%d %H:%i:%s 区分大小写 m ?d i?? ?s小写
-- 两个日期年数差

SELECT TIMESTAMPDIFF(YEAR, STR_TO_DATE('2000-12-12','%Y-%m-%d'), STR_TO_DATE('2023-12-12','%Y-%m-%d')) AS YEAR_DIFF;


-- 两个日期年数差

SELECT TIMESTAMPDIFF(YEAR, DATE_FORMAT('2000-12-12','%Y-%m-%d'), DATE_FORMAT('2023-12-12','%Y-%m-%d')) AS YEAR_DIFF;


-- 两个日期年数差

SELECT TIMESTAMPDIFF(YEAR, '2000-12-12', '2023-12-12') AS YEAR_DIFF;


-- 两个日期月数差

SELECT TIMESTAMPDIFF(MONTH, '2022-12-12', '2023-12-12') AS YEAR_DIFF;


-- 两个日期天数差

SELECT DATEDIFF('2023-12-12', '2022-12-12') AS DAY_DIFF;


-- 两个日期小时数差

SELECT TIMESTAMPDIFF(HOUR, '2023-12-12 00:00:00', '2023-12-12 06:00:00') AS HOUR_DIFF;


-- 两个日期小时数差

SELECT TIMESTAMPDIFF(HOUR,STR_TO_DATE('2023-12-12 00:00:00','%Y-%m-%d %H:%i:%s'), STR_TO_DATE('2023-12-12 06:00:00','%Y-%m-%d %H:%i:%s'));


-- 加一年

SELECT DATE_ADD('2023-12-12 00:00:00', INTERVAL 1 YEAR) AS ADDED_DATE;


-- 加一月

SELECT DATE_ADD('2023-11-12 00:00:00', INTERVAL 1 MONTH) AS ADDED_DATE;


-- 加一天

SELECT DATE_ADD('2023-12-12 00:00:00', INTERVAL 1 DAY) AS ADDED_DATE;


-- 加一小时

SELECT DATE_ADD('2023-12-12 00:00:00', INTERVAL 1 HOUR) AS ADDED_DATE;


-- 加一分钟

SELECT DATE_ADD('2023-12-12 00:00:00', INTERVAL 1 MINUTE) AS ADDED_DATE;


-- 加一秒

SELECT DATE_ADD('2023-12-12 00:00:00', INTERVAL 1 SECOND) AS ADDED_DATE;

PGSQL

-- `age`函数返回的是两个日期之间的时间间隔,以年、月、日的形式表示。当计算两个日期之间的月数差值时,PGSQL会将年份差值乘以12,并加上月份差值。
-- 年数差
SELECT EXTRACT(YEAR FROM AGE('2023-12-12', '2000-12-12')) AS YEAR_DIFF;
-- 月数差
SELECT (EXTRACT(YEAR FROM age('2023-12-12', '2023-02-12')) * 12) + EXTRACT(MONTH FROM age('2023-12-12', '2023-02-12')) AS TOTAL_MONTH_DIFF;
-- 天数差
SELECT (EXTRACT(YEAR FROM age('2023-12-12', '2022-12-10')) * 365) + EXTRACT(DAY FROM age('2023-12-12', '2022-12-10')) AS TOTAL_DAY_DIFF;
-- 使用`CEIL`函数向上取整,使用`FLOOR`函数向下取整。
-- `EXTRACT(EPOCH FROM ...)`将时间间隔转换为秒数,然后除以86400(一天的秒数)
-- 天数差 不满一天算一天 不满一天的部分会向上取整。例如,如果时间间隔是23小时,则会被计算为2天。
SELECT CEIL(EXTRACT(EPOCH FROM age('2023-12-11 12:00:00', '2023-12-10 10:30:00')) / 86400) AS TOTAL_DAY_DIFF;
-- 天数差 不满一天的部分会被舍去
SELECT FLOOR(EXTRACT(EPOCH FROM age('2023-12-12 00:00:00', '2023-12-10 01:00:00')) / 86400) AS TOTAL_DAY_DIFF;
-- 小时差
SELECT (EXTRACT(DAY FROM AGE('2023-12-13 23:00:00', '2023-12-12 00:00:00')) * 24) + EXTRACT(HOUR FROM AGE('2023-12-13 23:00:00', '2023-12-12 00:00:00')) AS TOTAL_HOUR_DIFF;
-- 小时差  算上分钟秒数的话,可以用`CEIL`函数向上取整,`FLOOR`函数向下取整。
SELECT EXTRACT(EPOCH FROM age('2023-12-13 23:00:00', '2023-12-12 01:00:00')) / 3600 AS TOTAL_HOUR_DIFF;
-- 加一年
SELECT NOW() + INTERVAL '1 YEAR' AS ADDED_DATE;
-- 加一月
SELECT NOW() + INTERVAL '1 MONTH' AS ADDED_DATE;
-- 加一天
SELECT NOW() + INTERVAL '1 DAY' AS ADDED_DATE;
-- 加一小时
SELECT NOW() + INTERVAL '1 HOUR' AS ADDED_DATE;
-- 加一分钟
SELECT NOW() + INTERVAL '1 MINUTE' AS ADDED_DATE;
-- 加一秒
SELECT NOW() + INTERVAL '1 SECOND' AS ADDED_DATE;

oracle

--两个日期年数差
SELECT TRUNC(MONTHS_BETWEEN(TO_DATE('2023-09-01', 'YYYY-MM-DD'),
                            TO_DATE('2000-01-01', 'YYYY-MM-DD')) / 12) AS year_diff
  FROM DUAL;

--两个日期月数差
SELECT MONTHS_BETWEEN(TO_DATE('2023-08-08', 'YYYY-MM-DD'),
                      TO_DATE('2023-07-08', 'YYYY-MM-DD')) AS MONTHS_BETWEEN
  FROM DUAL;

--两个日期天数差
SELECT CEIL(TO_DATE('2023-08-08 00:00:00', 'YYYY-MM-DD HH24-MI-SS') -
            TO_DATE('2023-07-08 00:00:00', 'YYYY-MM-DD HH24-MI-SS')) AS CEIL
  FROM DUAL;

--两个日期小时差
SELECT ROUND((TO_DATE('2023-08-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS') -
             TO_DATE('2023-07-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * 24) AS HOUR_DIFF
  FROM DUAL;

--加1年
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 12) FROM DUAL;

--加1月
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 1) FROM DUAL;

--加1天
SELECT SYSDATE, TO_CHAR(SYSDATE + 1, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

--加1小时
SELECT SYSDATE, TO_CHAR(SYSDATE + 1 / 24, 'YYYY-MM-DD HH24:MI:SS')
  FROM DUAL;

--加1分钟
SELECT SYSDATE, TO_CHAR(SYSDATE + 1 / 24 / 60, 'YYYY-MM-DD HH24:MI:SS')
  FROM DUAL;

--加1秒
SELECT SYSDATE,
       TO_CHAR(SYSDATE + 1 / 24 / 60 / 60, 'YYYY-MM-DD HH24:MI:SS')
  FROM DUAL;
-- BETWEEN ....  AND 或 NOT BETWEEN ....  AND
SELECT T.* FROM YOUR_TABLE T WHERE T.CREATED_DATE BETWEEN TO_DATE('2023-12-14','YYYY-MM-DD') AND SYSDATE ORDER BY T.CREATED_DATE DESC;

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;  --格式化/显示当前时间

SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL; --截取到年(本年的第一天)

SELECT TRUNC(SYSDATE,'Q') FROM DUAL; --截取到季度(本季度的第一天)

SELECT TRUNC(SYSDATE,'MONTH') FROM DUAL; --截取到月(本月的第一天)

SELECT TRUNC(SYSDATE,'') FROM DUAL; --为空

SELECT TRUNC(SYSDATE) FROM DUAL; --截取到日

SELECT TRUNC(SYSDATE,'DD') FROM DUAL; --截取到日

SELECT TRUNC(SYSDATE,'DAY') FROM DUAL; --截取到周(本周第一天,即上周日)

SELECT TRUNC(SYSDATE,'IW') FROM DUAL; --本周第2天,即本周一

SELECT TRUNC(SYSDATE,'HH24') FROM DUAL;  --截取到小时(当前小时,零分零秒)

SELECT TRUNC(SYSDATE,'MI') FROM DUAL; --截取到分(当前分,零秒)

SELECT TRUNC(SYSDATE,'SS') FROM DUAL ;--报错,没有精确到秒的格式

文章来源:https://blog.csdn.net/weixin_59383491/article/details/134996066
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。