1. 基本類型

Oracle支持不同的日期格式模型，其中包括：

• ISO 8601: YYYY-MM-DDTHH:MI:SS，例如2024-06-20T14:30:00
• Oracle內(nèi)部格式: DD-MON-YYYY HH:MI:SS AM，例如20-JUN-2024 02:30:00 PM

DATE： 存儲(chǔ)日期和時(shí)間，精確到秒

CREATE TABLE test_date (col DATE);
INSERT INTO test_date (col) VALUES (TO_DATE('2024-06-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS'));

TIMESTAMP：比DATE類型更精確，可以精確到小數(shù)秒

CREATE TABLE test_timestamp (col TIMESTAMP);
INSERT INTO test_timestamp (col) VALUES (TO_TIMESTAMP('2024-06-20 12:34:56.789', 'YYYY-MM-DD HH24:MI:SS.FF3'));

INTERVAL YEAR TO MONTH：存儲(chǔ)年份和月份的時(shí)間間隔

CREATE TABLE test_interval_ym (col INTERVAL YEAR TO MONTH);
INSERT INTO test_interval_ym (col) VALUES (INTERVAL '2-3' YEAR TO MONTH);

INTERVAL DAY TO SECOND：存儲(chǔ)天、小時(shí)、分鐘、秒以及小數(shù)秒的時(shí)間間隔

CREATE TABLE test_interval_ds (col INTERVAL DAY TO SECOND);
INSERT INTO test_interval_ds (col) VALUES (INTERVAL '5 12:34:56.789' DAY TO SECOND);

2. 常用函數(shù)

SYSDATE：返回當(dāng)前系統(tǒng)日期和時(shí)間。SELECT SYSDATE FROM dual;

CURRENT_TIMESTAMP：返回當(dāng)前系統(tǒng)時(shí)間戳：SELECT CURRENT_TIMESTAMP FROM dual;

EXTRACT: 從日期或時(shí)間戳中提取特定的部分（如年、月、日、小時(shí)等）

SELECT EXTRACT(YEAR FROM SYSDATE) AS year FROM dual; # 2024
SELECT EXTRACT(MONTH FROM SYSDATE) AS month FROM dual; # 6
SELECT EXTRACT(DAY FROM SYSDATE) AS day FROM dual; # 20

TO_DATE: 將字符串轉(zhuǎn)換為DATE類型：SELECT TO_DATE('2024-06-20', 'YYYY-MM-DD') FROM dual;

TO_TIMESTAMP: 將字符串轉(zhuǎn)換為TIMESTAMP類型：SELECT TO_TIMESTAMP('2024-06-20 12:34:56.789', 'YYYY-MM-DD HH24:MI:SS.FF3') FROM dual;

TO_CHAR: 將日期或時(shí)間戳轉(zhuǎn)換為字符串，可以指定格式：SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

ADD_MONTHS: 給日期加上指定的月份數(shù)：SELECT ADD_MONTHS(SYSDATE, 6) AS new_date FROM dual;

MONTHS_BETWEEN: 計(jì)算兩個(gè)日期之間的月份數(shù)：SELECT MONTHS_BETWEEN(TO_DATE('2024-12-20', 'YYYY-MM-DD'), SYSDATE) AS months_between FROM dual;

NEXT_DAY: 返回指定日期之后的第一個(gè)指定星期幾：SELECT NEXT_DAY(SYSDATE, 'FRIDAY') AS next_friday FROM dual;

LAST_DAY: 返回指定月份的最后一天：SELECT LAST_DAY(SYSDATE) AS last_day_of_month FROM dual;

3. Demo

示例的查詢有如下：

當(dāng)前系統(tǒng)日期和時(shí)間

SELECT SYSDATE, CURRENT_TIMESTAMP FROM dual;

將字符串轉(zhuǎn)換為日期并進(jìn)行加減操作

SELECT TO_DATE('2024-06-20', 'YYYY-MM-DD') + 10 AS new_date FROM dual;

提取日期的不同部分

SELECT EXTRACT(YEAR FROM SYSDATE) AS year,
       EXTRACT(MONTH FROM SYSDATE) AS month,
       EXTRACT(DAY FROM SYSDATE) AS day
FROM dual;

到此這篇關(guān)于Oracle日期和時(shí)間的基本命令的文章就介紹到這了,更多相關(guān)Oracle日期和時(shí)間內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論