在 SQL 中，處理日期、時間和字串是常見且重要的，這些操作讓我們能夠更好地理解和分析數據

所以今天就來讓我介紹要如何處理這些相關的資料吧，以及如何使用相關的內建函數

處理日期和時間

日期和時間數據類型在數據庫中扮演著關鍵角色。它們讓我們能夠追蹤事件何時發生，並能夠對這些事件進行排序和比較

日期和時間的類型

PostgreSQL 中與日期和時間相關的主要資料類型有：

1. timestamp: 存儲日期和時間 ex: '2023-09-24 14:30:00'
2. date: 僅儲存日期 ex: '1996-02-17'
3. time: 僅儲存時間 ex: '12:30:00'
4. interval: 儲存一段時間 ex: '1 year 6 months'

使用 EXTRACT() 提取日期和時間

假設我們有一個 orders 資料表，其中有一個 order_date 欄位，是 timestamp 類型

-- 建立 `orders` table

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_date TIMESTAMP
)

SELECT 
  EXTRACT(YEAR FROM order_date) AS year,
  EXTRACT(MONTH FROM order_date) AS month,
  EXTRACT(DAY FROM order_date) AS day
FROM orders;

結果會像是下面這樣，只會提取 order_date 欄位的年、月和日：

 year | month | day
------+-------+-----
 2023 |     9 |  30
 2023 |     8 |  30
 2023 |     7 |  30
 2023 |     6 |  30
(4 rows)

日期加法和減法使用 INTERVAL

SELECT 
  order_date,
  order_date + INTERVAL '1 day' AS next_day,
  order_date - INTERVAL '1 hour' AS prev_hour
FROM orders;

結果會像是下面這樣，只會計算 order_date 的下一天和前一小時

     order_date      |      next_day       |      prev_hour
---------------------+---------------------+---------------------
 2023-09-30 18:30:00 | 2023-10-01 18:30:00 | 2023-09-30 17:30:00
 2023-08-30 18:30:00 | 2023-08-31 18:30:00 | 2023-08-30 17:30:00
 2023-07-30 18:30:00 | 2023-07-31 18:30:00 | 2023-07-30 17:30:00
 2023-06-30 18:30:00 | 2023-07-01 18:30:00 | 2023-06-30 17:30:00
(4 rows)

日期和時間函數

PostgreSQL 也提供了眾多的日期和時間函數，例如：

1. NOW(): 當前日期和時間 ex: 2023-10-01 03:01:49.606596+08
2. CURRENT_DATE: 當前日期 ex: 2023-10-01
3. CURRENT_TIME: 當前時間 ex: 03:02:14.387437+08
4. AGE(timestamp1, timestamp2): 兩個時間戳記之間的間隔 ex: AGE('2023-10-30', '2023-10-01') => 29 days

例子

假設我們想要找出最近一個月的所有訂單

SELECT * FROM orders
WHERE order_date >= NOW() - INTERVAL '1 month';

可能會需要時區轉換 可以使用 AT TIME ZONE

在處理日期和時間時，應考慮時區
PostgreSQL 的 timestamptz 類型會儲存時區資訊，並可以使用不同的時區來檢視日期和時間

SELECT 
  order_date AT TIME ZONE 'UTC' AS utc_time,
  order_date AT TIME ZONE 'America/New_York' AS new_york_time
FROM orders;

會以 UTC 和紐約時區來顯示 order_date

        utc_time        |     new_york_time      |     origin_time
------------------------+------------------------+---------------------
 2023-10-01 02:30:00+08 | 2023-10-01 06:30:00+08 | 2023-09-30 18:30:00
 2023-08-31 02:30:00+08 | 2023-08-31 06:30:00+08 | 2023-08-30 18:30:00
 2023-07-31 02:30:00+08 | 2023-07-31 06:30:00+08 | 2023-07-30 18:30:00
 2023-07-01 02:30:00+08 | 2023-07-01 06:30:00+08 | 2023-06-30 18:30:00
(4 rows)

字串函數

字串函數讓我們能夠處理和操作字串數據。例如，我們可以使用字串函數來格式化數據、提取子字串、比較字串等。

在 PostgreSQL 中，字串函數允許你對字串數據進行各種操作，例如，提取子字串、連接字串、轉換字串大小寫等。以下是一些常用的字串函數及其使用方法。

1. CONCAT 和 CONCAT_WS

• CONCAT: 用於連接兩個或多個字串。

SELECT CONCAT('Hello', ' ', 'World');  -- 輸出：'Hello World'

• CONCAT_WS: 用於使用分隔符連接多個字串。

SELECT CONCAT_WS(', ', 'apple', 'banana', 'cherry');  -- 輸出：'apple, banana, cherry'

-- CONCAT_WS 後面的第一個參數用來分隔

2. LENGTH

返回字串的長度

SELECT LENGTH('Hello World');  -- 輸出：11

3. LOWER 和 UPPER

• LOWER: 將字串轉換為小寫。

SELECT LOWER('Hello World');  -- 輸出：'hello world'

• UPPER: 將字串轉換為大寫。

SELECT UPPER('Hello World');  -- 輸出：'HELLO WORLD'

4. TRIM

去除字串兩端的空格

SELECT TRIM('   Hello World   ');  -- 輸出：'Hello World'

5. SUBSTRING

提取子字串

SELECT SUBSTRING('Hello World' FROM 7 FOR 5);  -- 輸出：'World'

6. REPLACE

替換字串中的某些字符

SELECT REPLACE('Hello World', 'World', 'PostgreSQL');  -- 輸出：'Hello PostgreSQL'

-- REPLACE 第一個參數是要處理的字串

7. POSITION 和 STRPOS

找出子字串在字串中的位置

SELECT POSITION('World' IN 'Hello World');  -- 輸出：7

SELECT STRPOS('Hello World', 'World');  -- 輸出：7

-- 以 'Hello World' 這個字串來說，'World' 出現在第 7 個字元

實際應用

假設你有一個 users 資料表，其中有一個 email 欄位，你想找出所有以 'example.com' 結尾的電子郵件地址：

SELECT * FROM users WHERE RIGHT(email, 11) = 'example.com';

或者，你想統計 orders 資料表中的 description 欄位中某個字串出現的次數：

SELECT COUNT(*) FROM orders WHERE POSITION('apple' IN description) > 0;