Modern SQL Types: JSON/JSONB & Timezones

Dữ liệu thực tế không phải lúc nào cũng fit vào các column cứng nhắc. Bài này sẽ dạy bạn khi nào dùng JSON và cách xử lý thời gian đúng cách cho ứng dụng global.

---

1️⃣ JSON in SQL: Semi-structured Data

Khi nào dùng JSON thay vì thêm column?

Tình huống	Giải pháp
Dữ liệu có schema động (mỗi user có settings khác nhau)	✅ JSON
Dữ liệu cần query thường xuyên và index	❌ Column riêng
Lưu log, metadata, response từ API bên thứ ba	✅ JSON
Dữ liệu là core business (price, quantity)	❌ Column riêng

JSON vs JSONB (PostgreSQL)

Đặc điểm	JSON	JSONB
Lưu trữ	Text dạng nguyên bản	Binary đã parse
Tốc độ INSERT	Nhanh hơn	Chậm hơn một chút
Tốc độ Query	Chậm (parse mỗi lần)	Nhanh (đã parse sẵn)
Index	Không hỗ trợ	Hỗ trợ GIN index
Giữ thứ tự key?	Có	Không
Giữ whitespace?	Có	Không

Kết luận: Luôn dùng JSONB trừ khi bạn cần giữ nguyên format text gốc.

Ví dụ: User Settings

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    -- Settings động: mỗi user có thể có các key khác nhau
    settings JSONB DEFAULT '{}'::jsonb
);

-- Insert với JSON
INSERT INTO users (email, settings) VALUES
    ('alice@example.com', '{"theme": "dark", "language": "vi", "notifications": {"email": true, "sms": false}}'),
    ('bob@example.com', '{"theme": "light", "language": "en"}');

Query bên trong JSON

Truy cập giá trị

-- Operator -> : Lấy giá trị dưới dạng JSON
SELECT settings -> 'theme' FROM users;
-- Kết quả: "dark" (vẫn là JSON string, có dấu "")

-- Operator ->> : Lấy giá trị dưới dạng TEXT
SELECT settings ->> 'theme' FROM users;
-- Kết quả: dark (text thuần, không dấu "")

-- Truy cập nested object
SELECT settings -> 'notifications' ->> 'email' FROM users;
-- Kết quả: true

-- Đường dẫn với #> và #>>
SELECT settings #>> '{notifications, email}' FROM users;
-- Kết quả: true

Lọc (WHERE) với JSON

-- Tìm user có theme = dark
SELECT * FROM users WHERE settings ->> 'theme' = 'dark';

-- Tìm user có bật email notifications
SELECT * FROM users 
WHERE (settings -> 'notifications' ->> 'email')::boolean = true;

-- Kiểm tra key có tồn tại không
SELECT * FROM users WHERE settings ? 'language';

-- Kiểm tra object chứa subset
SELECT * FROM users 
WHERE settings @> '{"theme": "dark"}'::jsonb;

Cập nhật JSON

-- Cập nhật một key
UPDATE users 
SET settings = jsonb_set(settings, '{theme}', '"light"')
WHERE email = 'alice@example.com';

-- Thêm key mới
UPDATE users 
SET settings = settings || '{"new_feature": true}'::jsonb
WHERE email = 'alice@example.com';

-- Xóa một key
UPDATE users 
SET settings = settings - 'old_key'
WHERE email = 'alice@example.com';

Index cho JSONB

-- GIN Index cho toàn bộ JSONB column (query với @>, ?, ?|, ?&)
CREATE INDEX idx_users_settings ON users USING GIN (settings);

-- Index cho một key cụ thể (query với ->>)
CREATE INDEX idx_users_theme ON users ((settings ->> 'theme'));

💡 HPN Pro Tip: Khi nào KHÔNG dùng JSON

Đừng lạm dụng JSON chỉ vì "linh hoạt". JSON mất:

1. Type safety: {"price": "abc"} vẫn hợp lệ.
2. Constraint enforcement: Không có CHECK, NOT NULL bên trong JSON.
3. Referential integrity: Không thể FOREIGN KEY vào JSON field.

Quy tắc: Nếu data quan trọng cho business logic, hãy tạo column riêng.

---

2️⃣ Date & Time: TIMESTAMP vs TIMESTAMPTZ

Vấn đề: Bạn đang ở đâu trên thế giới?

User A ở Việt Nam tạo đơn hàng lúc 10:00 sáng (UTC+7).
Server ở Singapore (UTC+8), Database ở Ireland (UTC+0).

Hỏi: created_at lưu giá trị gì? Khi user ở Mỹ (UTC-5) xem, thấy mấy giờ?

TIMESTAMP vs TIMESTAMP WITH TIME ZONE

Kiểu	Mô tả	Ví dụ lưu trữ
TIMESTAMP	Lưu chính xác ngày giờ bạn truyền vào	2024-01-15 10:00:00
TIMESTAMPTZ	Lưu thời điểm quy về UTC, hiển thị theo timezone hiện tại	2024-01-15 03:00:00+00

Ví dụ minh họa:

-- Giả sử server timezone = 'Asia/Ho_Chi_Minh' (UTC+7)

-- Với TIMESTAMP (không timezone)
CREATE TABLE events_naive (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP
);
INSERT INTO events_naive (event_time) VALUES ('2024-01-15 10:00:00');
-- Lưu: 2024-01-15 10:00:00 (không thông tin timezone)

SET timezone = 'America/New_York';  -- Đổi timezone
SELECT event_time FROM events_naive;
-- Vẫn trả về: 2024-01-15 10:00:00 (SAI! User Mỹ tưởng event lúc 10am EST)


-- Với TIMESTAMPTZ (có timezone)
CREATE TABLE events_aware (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMPTZ
);
SET timezone = 'Asia/Ho_Chi_Minh';
INSERT INTO events_aware (event_time) VALUES ('2024-01-15 10:00:00');
-- PostgreSQL hiểu: 10:00 tại UTC+7 -> Lưu: 2024-01-15 03:00:00 UTC

SET timezone = 'America/New_York';  -- Đổi timezone
SELECT event_time FROM events_aware;
-- Trả về: 2024-01-14 22:00:00-05 (ĐÚNG! Tự convert sang EST)

⚠️ Golden Rule: LUÔN dùng TIMESTAMPTZ

-- ĐÚNG: created_at với timezone
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- SAI: Không timezone
CREATE TABLE orders_bad (
    created_at TIMESTAMP  -- Đừng làm thế này!
);

Xử lý Timezone trong Application

Backend (Python/Node.js)

# Python với pytz/zoneinfo
from datetime import datetime
from zoneinfo import ZoneInfo  # Python 3.9+

# Luôn làm việc với UTC trong backend
now_utc = datetime.now(ZoneInfo("UTC"))

# Khi hiển thị cho user, convert sang timezone của họ
user_tz = ZoneInfo("Asia/Ho_Chi_Minh")
local_time = now_utc.astimezone(user_tz)
print(local_time.strftime("%Y-%m-%d %H:%M:%S %Z"))
# 2024-01-15 10:00:00 +07

// JavaScript với Intl API (built-in)
const now = new Date();  // Luôn là UTC internally

// Hiển thị cho user theo timezone
const options = { 
    timeZone: 'Asia/Ho_Chi_Minh',
    year: 'numeric', month: '2-digit', day: '2-digit',
    hour: '2-digit', minute: '2-digit'
};
console.log(now.toLocaleString('vi-VN', options));

PostgreSQL Functions

-- Lấy thời gian hiện tại (luôn UTC)
SELECT NOW();  -- 2024-01-15 03:00:00+00

-- Convert sang timezone cụ thể
SELECT NOW() AT TIME ZONE 'Asia/Ho_Chi_Minh';
-- 2024-01-15 10:00:00

-- Tạo timestamp từ string với timezone rõ ràng
SELECT '2024-01-15 10:00:00 Asia/Ho_Chi_Minh'::timestamptz;
-- 2024-01-15 03:00:00+00 (stored as UTC)

Date Range Queries với Timezone

-- ⚠️ SAI: Query theo ngày không tính timezone
SELECT * FROM orders 
WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16';
-- PostgreSQL parse '2024-01-15' theo timezone của SESSION!

-- ✅ ĐÚNG: Chỉ định timezone rõ ràng
SELECT * FROM orders 
WHERE created_at >= '2024-01-15 00:00:00+07' 
  AND created_at < '2024-01-16 00:00:00+07';

-- Hoặc dùng AT TIME ZONE
SELECT * FROM orders 
WHERE created_at AT TIME ZONE 'Asia/Ho_Chi_Minh' >= '2024-01-15'::date
  AND created_at AT TIME ZONE 'Asia/Ho_Chi_Minh' < '2024-01-16'::date;

---

3️⃣ Best Practices Summary

JSON/JSONB Checklist

✅ DO	❌ DON'T
Dùng JSONB, không dùng JSON	Lưu business-critical data trong JSON
Tạo GIN index nếu query JSON thường xuyên	Query JSON mà không có index
Validate JSON schema ở application layer	Tin tưởng JSON data không validate

Timezone Checklist

✅ DO	❌ DON'T
Dùng TIMESTAMPTZ cho mọi datetime	Dùng TIMESTAMP không timezone
Lưu trữ UTC, hiển thị local	Lưu trữ local time
Lưu user's timezone preference riêng	Giả định mọi user cùng timezone

-- Pattern chuẩn cho users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    timezone VARCHAR(50) DEFAULT 'UTC',  -- Lưu preference của user
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

💡 HPN Pro Tip: UTC everywhere, Local at the edge

[Frontend] <--> [Backend] <--> [Database]
   Local         UTC           UTC

- Database: Luôn lưu TIMESTAMPTZ (UTC internally)
- Backend: Luôn xử lý datetime dạng UTC
- Frontend: Convert sang local timezone khi HIỂN THỊ
- API: Truyền timestamp dạng ISO 8601 với timezone: "2024-01-15T10:00:00+07:00"