PostgreSQL日期操作指南

一、入門概述

PostgreSQL是一種開源的對象關係型資料庫，它支持高級數據類型，例如數組、JSON等，這讓它成為了數據分析、地理信息系統、感測器等領域的首選。PostgreSQL提供完整的日期/時間操作支持，其中也包括了日期類型的處理。在這一章中，我們會學習如何使用PostgreSQL中的日期類型以及與日期相關的各種函數、操作符等等。

二、日期類型的數據結構

PostgreSQL中定義日期類型為”date”，它佔4個位元組的存儲空間。日期類型的值由年、月、日所組成，其範圍為公元前4713年到公元後5874897年。當需要對某個日期進行計算時，需要使用 “interval” 類型代表時間間隔。 “time” 類型代表一段時間內的具體時刻。

CREATE TABLE example(
    id INTEGER PRIMARY KEY,
    mydate DATE,
    myinterval INTERVAL,
    mytime TIME
);

三、日期函數

1. date_part函數

date_part函數可用於從日期/時間值中提取特定部分。函數接受兩個參數：第一個參數指定部分，第二個參數指定時間戳或日期。它支持的部分有：年、月、日、小時、分鐘、秒。該函數返回一個DOUBLE PRECISION值，可以是整數或小數

SELECT date_part('hour', TIMESTAMP '2020-05-11 13:50:10');
-- 返回 13
SELECT date_part('minute', TIMESTAMP '2020-05-11 13:50:10') as column;
-- 返回 50

2. date_trunc函數

date_trunc函數用於截斷日期或時間戳以生成所需的結果。函數接受兩個參數：第一個參數指定截斷部分，第二個參數指定時間戳或日期。它支持的截斷部分有：年、月、日、小時、分鐘、秒

SELECT date_trunc('year', TIMESTAMP '2020-05-11 13:50:10');
-- 返回 2020-01-01 00:00:00
SELECT date_trunc('hour', TIMESTAMP '2020-05-11 13:50:10');
-- 返回 2020-05-11 13:00:00

3. current_date函數

current_date函數返回當前系統日期

SELECT current_date;
-- 返回當前日期：2021-08-19

4. age函數

age函數用於計算日期之間的差異。函數接受兩個參數，分別是開始和結束日期。當第一個日期早於第二個日期時，函數返回的值為正數

SELECT age(DATE '2021-08-01', DATE '2021-08-19');
-- 返回: 18 days

四、日期操作符

1. + / – 操作符

在PostgreSQL中，可以使用 + 操作符將日期/時間戳與時間間隔相加，或使用 – 操作符將兩個日期/時間戳相減得出時間間隔。

SELECT DATE '2021-08-01' + INTERVAL '3 months';
-- 返回 2021-11-01
SELECT TIMESTAMP '2021-08-19 10:30:00' - TIMESTAMP '2021-08-19 10:00:00';
-- 返回 00:30:00

2. << 操作符

該操作符用於比較兩個日期/時間戳是否為同一天。

SELECT DATE '2021-08-19' << DATE '2021-08-20';
-- 返回 true
SELECT TIMESTAMP '2021-08-19 12:00:00' << TIMESTAMP '2021-08-20 01:30:00';
-- 返回 false

3. >> 操作符

該操作符用於比較兩個日期/時間戳是否為連續的兩天。即第一個日期/時間戳加一天後是否與第二個日期/時間戳相同。

SELECT DATE '2021-08-19' >> DATE '2021-08-20';
-- 返回 false
SELECT DATE '2021-08-19' >> DATE '2021-08-20' - INTERVAL '1 day';
-- 返回 true

五、日期類型的數據輸入與輸出

PostgreSQL遵循ISO格式：YYYY-MM-DD來表示日期。同時PostgreSQL也支持其他常用的日期格式。例如：

SELECT DATE '2021/08/19';
-- 返回 2021-08-19
SELECT DATE '19-Aug-2021';
-- 返回 2021-08-19
SELECT TIMESTAMP '2021-08-19 12:30:00+08';
-- 返回 2021-08-19 12:30:00

總結

PostgreSQL提供了豐富的日期/時間處理操作，包括日期函數、日期操作符等，使得我們可以更簡單、方便地處理日期類型的數據。同時PostgreSQL也支持對不同格式的日期數據進行輸入輸出操作。