Chuyển đổi định dạng ngày tháng

Thế giới database và frontend thường không đồng ý với nhau về việc ngày tháng nên hiển thị như thế nào. PostgreSQL có thể lưu ngày dưới dạng DATE, TIMESTAMP hoặc thậm chí TIMESTAMPTZ, nhưng định dạng này không phải lúc nào cũng phù hợp để show cho user. Ví dụ, thay vì kiểu mặc định 2023-10-01 12:30:45, designer có thể muốn thấy 01 tháng mười 2023 năm, 12:30. Và đôi khi cần format ngày cho báo cáo hoặc API.

Để chuyển đổi ngày sang dạng chuỗi và ngược lại trong PostgreSQL, có các hàm TO_CHAR() và TO_DATE().

Hàm TO_CHAR()

TO_CHAR() — là bạn thân khi cần biến dữ liệu thời gian thành chuỗi dễ đọc cho con người. Nó nhận ngày hoặc timestamp và format nó theo mẫu bạn chỉ định.

Cú pháp

TO_CHAR(value, format)

• value — ngày hoặc timestamp cần chuyển đổi.
• format — chuỗi mẫu định dạng, bạn muốn hiển thị ngày như thế nào.

Ví dụ về các định dạng

Danh sách đầy đủ các định dạng bạn có thể xem ở tài liệu chính thức của PostgreSQL.

Ví dụ dùng TO_CHAR()

Định dạng ngày cho báo cáo

SELECT TO_CHAR(NOW(), 'DD.MM.YYYY') AS formatted_date;
-- Kết quả: '09.10.2023'

Hiển thị giờ theo định dạng 12h

SELECT TO_CHAR(NOW(), 'HH12:MI AM') AS formatted_time;
-- Kết quả: '03:45 PM'

Xuất tên tháng bằng chữ

SELECT TO_CHAR(NOW(), 'Month') AS month_name;
-- Kết quả: 'October '

Lưu ý: PostgreSQL sẽ thêm dấu cách ở cuối. Đây là feature chứ không phải bug! Nếu muốn bỏ dấu cách, dùng hàm TRIM():

SELECT TRIM(TO_CHAR(NOW(), 'Month')) AS trimmed_month_name;

Tạo định dạng custom

SELECT TO_CHAR(NOW(), 'YYYY/MM/DD HH24:MI:SS') AS custom_format;
-- Kết quả: '2023/10/09 15:45:30'

Định dạng cho giao diện người dùng

SELECT TO_CHAR(NOW(), 'DD "tháng mười" YYYY năm') AS user_friendly_date;
-- Kết quả: '09 tháng mười 2023 năm'

Hàm TO_DATE()

TO_DATE() làm ngược lại: nó nhận chuỗi và chuyển thành kiểu dữ liệu DATE. Để làm gì? Ví dụ, user có thể nhập ngày theo kiểu 01-10-2023, và PostgreSQL cần "hiểu" đó là ngày nào.

Cú pháp

TO_DATE(value, format)

• value — chuỗi chứa ngày.
• format — chuỗi mẫu mô tả định dạng chuỗi.<

Ví dụ dùng TO_DATE()

Chuyển chuỗi thành ngày

SELECT TO_DATE('01-10-2023', 'DD-MM-YYYY') AS date_value;
-- Kết quả: '2023-10-01' (kiểu dữ liệu: DATE)

So sánh ngày dạng chuỗi với ngày trong bảng

Giả sử có bảng appointments với cột appointment_date kiểu DATE. User nhập ngày dưới dạng chuỗi:

SELECT *
FROM appointments
WHERE appointment_date = TO_DATE('2023-10-09', 'YYYY-MM-DD');

Sai định dạng

Lưu ý: nếu định dạng chuỗi không khớp với mẫu, sẽ bị lỗi! Ví dụ:

SELECT TO_DATE('01/10/2023', 'DD-MM-YYYY');
-- Lỗi: định dạng đầu vào không hợp lệ

Kiểm tra dữ liệu nhập từ user

Giả sử bạn tạo bảng lưu đơn hàng, ngày nhập bởi user:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE
);

-- Thêm dữ liệu, chuyển chuỗi thành ngày
INSERT INTO orders (order_date)
VALUES (TO_DATE('10-09-2023', 'MM-DD-YYYY'));

Ví dụ thực tế

Định dạng báo cáo. Trong bảng sales lưu ngày bán hàng ở cột sale_date (kiểu TIMESTAMP). Cần xuất báo cáo, ngày ở định dạng DD.MM.YYYY.

-- Dữ liệu mẫu
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_date TIMESTAMP
);

INSERT INTO sales (sale_date)
VALUES
    ('2023-10-01 15:30:00'),
    ('2023-10-02 10:15:00'),
    ('2023-10-03 12:45:00');

-- Báo cáo
SELECT sale_id,
       TO_CHAR(sale_date, 'DD.MM.YYYY') AS formatted_date
FROM sales;

Chuyển đổi dữ liệu nhập từ user. Giả sử user nhập ngày ở dạng chuỗi MM/DD/YYYY. Cần chuyển thành DATE để lưu vào hệ thống.

INSERT INTO sales (sale_date)
VALUES (TO_TIMESTAMP('10/01/2023 15:30:00', 'MM/DD/YYYY HH24:MI:SS'));

Lỗi thường gặp và khuyến nghị

Sai định dạng. Lỗi phổ biến là định dạng chuỗi không khớp với mẫu. Ví dụ, user nhập 01-10-2023 mà mẫu là MM/DD/YYYY, PostgreSQL sẽ báo lỗi. Khuyến nghị: luôn validate dữ liệu nhập trước khi truyền vào SQL.

Dấu cách trong định dạng TO_CHAR(). Một số định dạng như MONTH sẽ thêm dấu cách. Nếu thấy phiền, dùng hàm TRIM().

Lỗi khi parse chuỗi. Nếu chuỗi có ký tự lạ hoặc sai định dạng, PostgreSQL sẽ không chuyển được. Khuyến nghị: dùng regex hoặc kiểm tra dữ liệu trước khi insert vào database.

Dùng sai định dạng thời gian. Ví dụ, xử lý timestamp bằng mẫu cho DATE. Khuyến nghị: đảm bảo kiểu dữ liệu phù hợp với mục đích sử dụng.

Hai hàm TO_CHAR() và TO_DATE() mở ra rất nhiều khả năng khi làm việc với dữ liệu thời gian. Bạn có thể tạo định dạng tiện cho báo cáo, chuyển đổi dữ liệu nhập từ user và làm cho SQL query dễ đọc hơn. Thực tế, các hàm này được dùng nhiều để visualize dữ liệu, tạo báo cáo, tích hợp với hệ thống khác và chuẩn bị cho giao diện người dùng.