SQL语法基础-时间函数II

时间函数用于处理和转换日期和时间值。这些函数允许开发者进行日期的计算、格式化和转换等操作。
以下是一些常用的Oracle时间函数，以及每个函数的简要说明和使用示例：

一、SYSDATE

说明：返回当前日期和时间。

SELECT SYSDATE FROM dual;
--2024-04-19 14:15:17

二、CURRENT_DATE

说明：返回当前会话时区的日期。

SELECT CURRENT_DATE FROM dual;
-- 2024-04-19 14:16:38

三、CURRENT_TIMESTAMP

说明：返回当前会话时区的日期和时间，包含时区信息。

SELECT CURRENT_TIMESTAMP FROM dual;
--2024-04-19 14:16:54.152000 +08:00

四、EXTRACT

说明：从日期/时间值中提取年、月、日、时、分、秒等部分。

SELECT EXTRACT(YEAR FROM SYSDATE) AS Current_Year FROM dual;
-- 2024
SELECT EXTRACT(MONTH FROM SYSDATE) AS Current_Month FROM dual;
-- 4
SELECT EXTRACT(DAY FROM SYSDATE) AS Current_Day_Of_Month FROM dual;
-- 19

SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS Hour FROM dual;
-- 6
SELECT EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS Minute FROM dual;
-- 30
SELECT EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS Second FROM dual;
-- 43.211

P.S.：在使用时分秒的取值的时候，要注意该时间是否含有时间部分，不然会提示错误！

五、TO_DATE

说明：将字符串转换为日期格式。

SELECT TO_DATE('2023-04-19', 'YYYY-MM-DD') FROM dual;
-- 2023-04-19 00:00:00

SELECT TO_DATE('04 19, 2024 14:35:00', 'MM DD, YYYY HH24:MI:SS') AS Full_Date_Time FROM dual;
--2024-04-19 14:35:00

六、TO_CHAR

说明：将日期转换为字符串，可以指定格式。

--日期转换为格式化的字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS Date_Time_String FROM dual;
-- 2024-04-19 14:41:52

-- 日期转换为更具体的格式，显示星期名
SELECT TO_CHAR(SYSDATE, 'Day, DD Month YYYY') AS Full_Date FROM dual;
-- 星期五, 19 4月  2024

-- 显示上午或下午信息
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS AM') AS Time_AM_PM FROM dual;
-- 02:41:52 下午

-- 使用不同的语言（如法语）显示月份和星期名：
SELECT TO_CHAR(SYSDATE, 'Day, Month YYYY', 'NLS_DATE_LANGUAGE=FRENCH') AS Date_In_French FROM dual;
-- Vendredi, Avril     2024

TO_CHAR 函数非常强大，可以用来将日期和时间值格式化为字符串，并提取年、季度、月、星期、年中的天数、月中的天数、时、分、秒等。
下面是如何使用 TO_CHAR 函数来获取这些日期和时间部分的示例。

--1. 年份
SELECT TO_CHAR(SYSDATE, 'YYYY') AS Current_Year FROM dual;
--  2024

--2. 季度
SELECT TO_CHAR(SYSDATE, 'Q') AS Current_Quarter FROM dual;
-- 2

--3. 月份
SELECT TO_CHAR(SYSDATE, 'MM') AS Current_Month FROM dual;
-- 04

--4. 星期
SELECT TO_CHAR(SYSDATE, 'D') AS Current_Weekday FROM dual;  -- 返回1-7（根据NLS_TERRITORY，1可能代表星期日或星期一）
-- 6

--5. 年中的天数
SELECT TO_CHAR(SYSDATE, 'DDD') AS Current_Day_of_Year FROM dual;
-- 110

--6. 月中的天数
SELECT TO_CHAR(SYSDATE, 'DD') AS Current_Day_of_Month FROM dual;
-- 19

--7. 时
SELECT TO_CHAR(SYSDATE, 'HH24') AS Current_Hour FROM dual;
-- 15

--8. 分
SELECT TO_CHAR(SYSDATE, 'MI') AS Current_Minute FROM dual;
-- 04

--9. 秒
SELECT TO_CHAR(SYSDATE, 'SS') AS Current_Second FROM dual;
-- 31

--10. 上午/下午标识
SELECT TO_CHAR(SYSDATE, 'AM') AS "AM or PM" FROM dual;
-- 下午

--11. 年第几周
SELECT TO_CHAR(SYSDATE, 'IW') AS ISO_Week_Day FROM dual;
-- 16 
SELECT TO_CHAR(SYSDATE, 'WW') AS ISO_Week_Day FROM dual;
-- 16 
SELECT TO_CHAR(SYSDATE, 'FMWW') AS ISO_Week_Day FROM dual;
-- 16 

--12.月第几周
SELECT TO_CHAR(SYSDATE, 'W') AS ISO_Week_Day FROM dual;
-- 3

TO_CHAR参数的取值说明表

参数格式	说明
YYYY	年份
Q	季度
MM	月份
WW	一年中的星期
DDD	一年中的第几天
DD	月份的第几天
D	星期中的第几天
HH, HH12	一天中的第几个小时，12进制
HH24	一天中的第几个小时，24进制
MI	一小时中的分钟
SS	一分钟中的秒
SSSS	从0时开始的秒数
AM
IW	是ISO标准周，返回是的当前日期所在周的周一
WW	ORACLE自定义的标准周。返回ORACLE自定义标准周所在的周一
FMWW	该年1月1号（不考虑属星期几）开始至该年该一个星期日为第一周，第二周是从该年第一个星期开始算（这与IW算法相同）一年的最后一周以该年的12月31号做为截止。取周的开始时间时在跨年的时候与IW有些区别
W	一个月的第几周。是按照ORACLE自定义的标准周来返回周数

七、ADD_MONTHS

说明：在日期上加上指定的月份数。如果是加上负数的，即是往前的月份数

SELECT ADD_MONTHS(SYSDATE, 3) AS Three_Months_Later FROM dual;
-- 2024-07-19 15:19:22

SELECT ADD_MONTHS(SYSDATE, -3) AS Three_Months_Later FROM dual;
--2024-01-19 15:19:40

八、LAST_DAY

说明：返回某个月的最后一天。

SELECT LAST_DAY(SYSDATE) AS Last_Day_This_Month FROM dual;

九、MONTHS_BETWEEN

说明：计算两个日期之间的月份数。

SELECT MONTHS_BETWEEN('2023-12-01', '2023-04-01') AS Months_Difference FROM dual;

十、NEXT_DAY

说明：返回下一个指定星期几的日期。

SELECT NEXT_DAY(SYSDATE, '星期五') AS Next_Friday FROM dual;
-- 2024-04-26 15:21:57

十一、ROUND 和 TRUNC

这两个函数用于对日期进行四舍五入和截断。

11.1 ROUND

说明：根据指定的格式单位四舍五入日期。

--此例中，日期被四舍五入到月份的最近一天。
SELECT ROUND(SYSDATE, 'YEAR') AS Rounded_Month FROM dual;
-- 2024-01-01 00:00:00

SELECT ROUND(SYSDATE, 'Q') AS Rounded_Month FROM dual;
-- 2024-04-01 00:00:00

SELECT ROUND(SYSDATE, 'MONTH') AS Rounded_Month FROM dual;
-- 2024-05-01 00:00:00

SELECT ROUND(SYSDATE, 'DDD') AS Rounded_Month FROM dual;
--2024-04-20 00:00:00

SELECT ROUND(SYSDATE, 'DD') AS Rounded_Month FROM dual;
--2024-04-20 00:00:00

SELECT ROUND(SYSDATE, 'D') AS Rounded_Month FROM dual;
-- 2024-04-21 00:00:00

SELECT ROUND(SYSDATE, 'IW') AS Rounded_Month FROM dual;
--2024-04-22 00:00:00

SELECT ROUND(SYSDATE, 'W') AS Rounded_Month FROM dual;
--2024-04-22 00:00:00

SELECT ROUND(SYSDATE, 'HH24') AS Rounded_Month FROM dual;
-- 2024-04-19 15:00:00

SELECT ROUND(SYSDATE, 'MI') AS Rounded_Month FROM dual;
-- 2024-04-19 15:28:00

11.2 TRUNC

说明：根据指定的格式单位截断日期。

--此例中，日期被截断到年份的第一天。
SELECT TRUNC(SYSDATE, 'YEAR') AS TRUNCed_Month FROM dual;
-- 2024-01-01 00:00:00

SELECT TRUNC(SYSDATE, 'Q') AS TRUNCed_Month FROM dual;
-- 2024-04-01 00:00:00

SELECT TRUNC(SYSDATE, 'MONTH') AS TRUNCed_Month FROM dual;
-- 2024-04-01 00:00:00

SELECT TRUNC(SYSDATE, 'DDD') AS TRUNCed_Month FROM dual;
--2024-04-19 00:00:00

SELECT TRUNC(SYSDATE, 'DD') AS TRUNCed_Month FROM dual;
--2024-04-19 00:00:00

SELECT TRUNC(SYSDATE, 'D') AS TRUNCed_Month FROM dual;
-- 2024-04-14 00:00:00

SELECT TRUNC(SYSDATE, 'IW') AS TRUNCed_Month FROM dual;
--2024-04-15 00:00:00

SELECT TRUNC(SYSDATE, 'W') AS TRUNCed_Month FROM dual;
--2024-04-15 00:00:00

SELECT TRUNC(SYSDATE, 'HH24') AS TRUNCed_Month FROM dual;
-- 2024-04-19 15:00:00

SELECT TRUNC(SYSDATE, 'MI') AS TRUNCed_Month FROM dual;
-- 2024-04-19 15:30:00

十二、INTERVAL

使用INTERVAL关键字可以创建时间间隔，并与日期进行计算。

--此例中，向当前日期添加了10天。
SELECT SYSDATE + INTERVAL '10' DAY AS Ten_Days_Later FROM dual;
-- 2024-04-29 15:32:26

十三、DBTIMEZONE 和 SESSIONTIMEZONE

这两个函数用于获取数据库和当前会话的时区。

13.1 DBTIMEZONE

说明：返回数据库的时区。

SELECT DBTIMEZONE FROM dual;
-- +00:00

13.2 SESSIONTIMEZONE

说明：返回当前会话的时区。

SELECT SESSIONTIMEZONE FROM dual;
-- +08:00

十四、FROM_TZ

将一个时间值和一个时区转换为带时区的时间戳。

SELECT FROM_TZ(CAST(TO_DATE('2023-04-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'America/New_York') AS Timestamp_With_Zone FROM dual;
-- 2024-04-19 15:30:00.000000 AMERICA/NEW_YORK

十五、NUMTODSINTERVAL 和 NUMTOYMINTERVAL

这两个函数用于将数字转换为时间间隔。

15.1 NUMTODSINTERVAL

说明：将数字转换为“天到秒”的时间间隔。

--将3600秒转换为时间间隔。
SELECT NUMTODSINTERVAL(3600, 'SECOND') AS Hours_Interval FROM dual;
-- +000000000 01:00:00.000000000

15.2 NUMTOYMINTERVAL

说明：将数字转换为“年到月”的时间间隔。

--将2年转换为时间间隔。
SELECT NUMTOYMINTERVAL(2, 'YEAR') AS Two_Years_Interval FROM dual;
-- +000000002-00