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

目录

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 ;--报错，没有精确到秒的格式