Việc chuyển từ Oracle sang Amazon RDS cho PostgreSQL hoặc Phiên bản tương thích với PostgreSQL của Amazon Aurora có thể yêu cầu cập nhật sơ đồ cơ sở dữ liệu hoặc các lệnh SQL dùng để truy cập dữ liệu. Để hỗ trợ di chuyển, AWS cung cấp Công cụ chuyển đổi lược đồ AWS (AWS SCT) để chuyển đổi mã SQL nhúng trong ứng dụng và cung cấp cẩm nang di chuyển ghi lại một số ánh xạ chức năng giữa cơ sở dữ liệu Oracle và PostgreSQL.

Mặc dù công cụ này giúp tự động hóa nỗ lực di chuyển nhưng có những tình huống mà nhà phát triển cần can thiệp và chuyển đổi SQL từ Oracle sang PostgreSQL theo cách thủ công. Cột ngày và dấu thời gian được sử dụng đáng kể trong nhiều ứng dụng để theo dõi yếu tố thời gian của dữ liệu. Khi di chuyển từ cơ sở dữ liệu này sang cơ sở dữ liệu khác, chúng tôi cần duy trì tính đối xứng giữa các giá trị ngày và giờ được chuyển đổi để các thành phần phụ thuộc không bị ảnh hưởng. Trong bài đăng này, chúng tôi chỉ ra sự khác biệt giữa các hàm liên quan đến ngày và dấu thời gian của Oracle và PostgreSQL cũng như cách chuyển đổi chúng để tạo ra kết quả tương đương.

Các hàm liên quan đến ngày tháng trong Oracle và PostgreSQL

Kiểu dữ liệu của Oracle DATEtương đương với TIMESTAMP WITHOUT TIME ZONEPostgreSQL. Với mục đích trình diễn, trong các truy vấn của bài đăng này, chúng tôi sử dụng CREATE_DATEcột DATEkiểu dữ liệu trong Oracle và TIMESTAMP WITHOUT TIME ZONEtrong PostgreSQL. Định dạng được sử dụng trong cài đặt máy khách cơ sở dữ liệu cho Oracle và PostgreSQL là YYYY-MM-DD HH:MI:SS.SSS. Các đoạn mã hiển thị trong bài đăng này đã được thử nghiệm trong phiên bản Oracle 19.0 và PostgreSQL phiên bản 13 trở lên.

Oracle SYSDATE và PostgreSQL NOW() và CURRENT_DATE

Trong Oracle, SYSDATEđược sử dụng để lấy ngày giờ hiện tại của hệ thống và định dạng phụ thuộc vào giá trị của tham số khởi tạo NLS_DATE_FORMAT .

PostgreSQL có một NOW()hàm tích hợp trả về ngày và giờ hiện tại tính đến micro giây theo múi giờ, trong khi một hàm PostgreSQL khác CURRENT_DATEchỉ cung cấp ngày hiện tại. Định dạng của DATEvà TIMESTAMPphụ thuộc vào tham số cơ sở dữ liệu hoặc cài đặt máy khách cơ sở dữ liệu. Cả hai chức năng này đều không tương đương với SYSDATEchức năng của Oracle. Đầu ra của Oracle SYSDATElà DATEkiểu dữ liệu trong khi PostgreSQL NOW()là TIMESTAMPkiểu dữ liệu và CURRENT_DATEthuộc DATEkiểu dữ liệu. Để biết thêm thông tin, hãy tham khảo ngày và dấu thời gian của hệ thống Oracle và PostgreSQL khác nhau .

Bảng sau đây cho thấy sự khác biệt giữa Oracle SYSDATEvà PostgreSQL CURRENT_DATE.

Bạn có thể truyền PostgreSQL CURRENT_TIMESTAMPsang TIMESTAMP WITHOUT TIME ZONEđể có kết quả tương tự như SYSDATE.

TRUNC trong Oracle và DATE_TRUNC trong PostgreSQL

Trong Oracle, bạn có thể sử dụng TRUNCkiểu DATEdữ liệu này để cắt ngắn ngày đến một mức độ chính xác cụ thể. Trong PostgreSQL, bạn có thể sử dụng CASThoặc DATE_TRUNCđể cắt bớt cột ngày hoặc dấu thời gian. CASTđược sử dụng để chuyển đổi loại dữ liệu này sang loại dữ liệu khác và DATE_TRUNCđược sử dụng để cắt bớt TIMESTAMPhoặc INTERVALcột.

TRUNC THEO NGÀY/THÁNG/NĂM trong Oracle và PostgreSQL

Hàm cắt ngắn với cột kiểu dữ liệu DATEhoặc TIMESTAMPđược sử dụng để làm tròn dấu thời gian giao dịch đến mức độ chi tiết mong muốn. Đối với ứng dụng báo cáo, chức năng này giúp tạo tập hợp dựa trên thời gian như bản tóm tắt dữ liệu hàng ngày, hàng tuần hoặc hàng tháng trong cơ sở dữ liệu Oracle và PostgreSQL.

Hàm TRUNC (<DateField>, <fmt>) (date)trong Oracle trả về DATEphần thời gian trong ngày được cắt ngắn thành đơn vị được chỉ định cho mô hình định dạng ( fmt): ‘DD’/’MONTH’/’YEAR’. Giá trị trả về luôn có kiểu dữ liệu DATE. Chức năng tương tự có thể đạt được bằng cách sử dụng DATE_TRUNC()chức năng trong PostgreSQL. Được date_trunc()sử dụng để cắt bớt NGÀY, THỜI GIAN hoặc DẤU THỜI GIAN theo độ chính xác đã chỉ định.

Hàm TRUNC (<DateField>,’DD’)trong Oracle và DATE_TRUNC(‘DAY’, <DateField>)hàm trong PostgreSQL cắt bớt cột ngày thành ngày ‘ YYYY-MM-DD’và trả về phần thời gian dưới dạng ‘00:00:00.000’. Trong ví dụ sau, CREATE_DATEcột có DATEkiểu dữ liệu trong Oracle và TIMESTAMP WITHOUT TIME ZONEkiểu dữ liệu trong PostgreSQL. TRUNCHàm có đối số của Oracle ‘DD’cắt bớt phần thời gian của cột ngày từ 12:53:19.000thành 00:00:00.000. Nó có thể được chuyển đổi trong PostgreSQL bằng cách sử dụng DATE_TRUNCwith ‘DAY’làm đối số.

Hàm TRUNC(<DateField>,’MONTH’)trong Oracle và DATE_TRUNC(‘MONTH’, <DateField>)hàm trong PostgreSQL cắt bớt cột ngày thành phần tháng. PostgreSQL cắt bớt cột ngày thành ngày ‘YYYY-MM-DD’và loại trừ phần thời gian. Trong ví dụ sau, TRUNChàm của Oracle có đối số ‘MONTH’sẽ cắt bớt cột ngày từ ngày 21 của tháng xuống ngày đầu tiên của tháng. Nó có thể được chuyển đổi trong PostgreSQL bằng cách sử dụng DATE_TRUNCwith ‘MONTH’làm đối số.

Hàm TRUNC(<DateField>,’YEAR’)trong Oracle và DATE_TRUNC(‘YEAR’, <DateField>)hàm trong PostgreSQL cắt bớt cột ngày thành phần năm.

Trong ví dụ sau, TRUNChàm có đối số của Oracle ‘YEAR’sẽ cắt bớt cột ngày thành ngày đầu tiên của năm. Nó có thể được chuyển đổi trong PostgreSQL bằng cách sử dụng DATE_TRUNCwith ‘YEAR’làm đối số.

TO_DATE trong Oracle và PostgreSQL

Hàm TO_DATEdùng để chuyển đổi kiểu dữ liệu ký tự thành ngày tháng. Nó đưa ra kết quả ở một định dạng khác trong Oracle và PostgreSQL. Trong Oracle kết quả có ‘YYYY-MM-DD 00:00:00.000’định dạng, trong khi ở PostgreSQL nó có định dạng này ‘YYYY-MM-DD’. Vì vậy việc định kiểu rõ ràng là cần thiết.

Bảng sau minh họa việc sử dụng TO_DATEđịnh dạng with ‘YYYY-MM-DD’trong Oracle và PostgreSQL và sự khác biệt trong kết quả.

Để chuyển đổi TO_DATE(<DATE_FIELD>, ‘YYYY-MM-DD’)từ Oracle sang tương đương trong PostgreSQL, cần phải gõ kiểu: TO_DATE(<DATE_FIELD>, ‘YYYY-MM-DD’)::TIMESTAMP(0)hoặc TO_DATE(<DATE_FIELD>, ‘YYYY-MM-DD’::TIMESTAMP WITHOUT TIME ZONE).

Trong ví dụ sau, việc định kiểu của TO_DATE(<DATE_FIELD>, ‘YYYY-MM-DD’)to TIMESTAMP(0)được thực hiện trong PostgreSQL.

Trong ví dụ sau, việc định kiểu của TO_DATE(<DATE_FIELD>, ‘YYYY-MM-DD’)to TIMESTAMP WITHOUT TIME ZONEđược thực hiện trong PostgreSQL.

Bảng sau minh họa cách sử dụng TO_DATEvới định dạng ‘YYYY-MM-DD HH24:MI:SS’.

Hàm TO_DATEtrong Oracle và PostgreSQL cho kết quả khác nhau. Oracle trả về kết quả ở ‘YYYY-MM-DD HH24:MI:SS’định dạng, trong khi PostgreSQL trả về kết quả ở định dạng ‘YYYY-MM-DD’.

Để chuyển đổi TO_DATE(<DATE_FIELD>, ‘YYYY-MM-DD HH24:MI:SS’)hàm trong Oracle sang PostgreSQL, TO_TIMESTAMPcần phải có cùng với việc định kiểu: TO_TIMESTAMP(<Date_Field>, ‘YYYY-MM-DD HH24:MI:SS’)::TIMESTAMP(0)hoặc TO_TIMESTAMP(<Date_Field>, ‘YYYY-MM-DD HH24:MI:SS’)::TIMESTAMP WITHOUT TIME ZONE.

Trong ví dụ sau, việc đánh máy TO_DATE(<DATE_FIELD>, ‘YYYY-MM-DD’) to TIMESTAMP(0)được thực hiện trong PostgreSQL.

Trong ví dụ sau, việc định kiểu của TO_DATE(<DATE_FIELD>, ‘YYYY-MM-DD’)to TIMESTAMP WITHOUT TIME ZONEđược thực hiện trong PostgreSQL.

Thêm kiểu dữ liệu INTEGER vào DATE trong Oracle và PostgreSQL

Trong trường hợp số ngày được thêm vào một DATEcột, kết quả sẽ khớp với cả Oracle và PostgreSQL. Tuy nhiên, khi số ngày được thêm vào TIMESTAMPPostgreSQL thì nó báo lỗi.

Để chuyển đổi các truy vấn như vậy từ Oracle sang PostgreSQL, INTERVALcần thêm vào cột dấu thời gian.

PostgreSQL không cho phép thêm số nguyên vào TIMESTAMPcột. Nó báo lỗi như hình bên dưới. Để giải quyết nó, hãy thêm INTERVALvào một TIMESTAMPcột.

Nếu không làm theo các bước trước đó, bạn sẽ gặp lỗi vì PostgreSQL không cho phép thêm số nguyên vào cột DATEhoặc TIMESTAMP.

Nếu không làm theo các bước trước đó, bạn sẽ gặp lỗi vì PostgreSQL không cho phép thêm số nguyên vào cột DATEhoặc TIMESTAMP.

Sự khác biệt về ngày tháng trong Oracle và PostgreSQL

Khi hai cột ngày tháng bị trừ trong Oracle, kết quả là NUMBER, trong khi đó INTERVALở PostgreSQL đối với các cột dấu thời gian.

Trong PostgreSQL, để tính chênh lệch ngày theo ngày trong đó kiểu dữ liệu ngày là dấu thời gian và kiểu dữ liệu liên quan, chúng ta có thể sử dụng hàm EXTRACTwith epochđể lấy chênh lệch giữa hai ngày ở dạng số. Ngoài ra, EXTRACTcó thể được sử dụng với kiểu dữ liệu ngày tháng, chuyển đổi nó thành giây và sau đó chia cho tổng số giây trong một ngày, tức là 24*60*60.

Chênh lệch ngày sử dụng TRUNC trong Oracle

Trong Oracle, khi TRUNCđược sử dụng với các cột ngày tháng để lấy chênh lệch ngày, kết quả là INTEGERgiá trị. Điều này có thể đạt được trong PostgreSQL bằng cách sử dụng hàm CASThoặc DATE_PART.

Sự khác biệt ngày tháng trong Oracle và PostgreSQL

Trong Oracle, MONTHS_BETWEENcung cấp chênh lệch ngày theo NUMBER, trong khi ở PostgreSQL, AGEhàm cung cấp chênh lệch ngày theo INTERVAL.

Hàm MONTHS_BETWEENcùng với TRUNCcho kết quả INTEGER. Điều này có thể được chuyển đổi trong PostgreSQL bằng cách sử dụng DATE_PARThàm AGE.

Trong PostgreSQL, bạn có thể sử dụng AGEhàm EXTRACTđể khớp với kết quả của Oracle MONTHS_BETWEEN. Nó chuyển đổi đầu ra của hàm AGE từ INTERVALthành NUMERIC.

Chuyển đổi hàm NUMTODSINTERVAL trong Oracle sang PostgreSQL

Trong hàm Oracle NUMTODSINTERVALđược sử dụng để chuyển đổi một NUMBERgiá trị thành INTERVAL. Nó chuyển đổi một NUMBERgiá trị nhất định thành một INTERVAL DAY TO SECONDchữ. Trong NUMTODSINTERVAL(n, ‘interval_unit’), nđối số là giá trị đầu vào và ‘ interval_unit’ phải là một trong các giá trị sau: ‘DAY’, ‘HOUR’, ‘MINUTE’, hoặc ‘SECOND’.

Ví dụ:

• NUMTODSINTERVAL(90, ‘SECOND’)thay đổi 90 secondsthành 0 0:1:30.0, bằng 1 minutevà 30 seconds.
• NUMTODSINTERVAL(90, ‘MINUTE’)thay đổi 90 minutesthành 0 1:30:0.0, bằng 1 hourvà 30 minutes.
• NUMTODSINTERVAL(90, ‘HOUR’)thay đổi 90 hoursthành 3 18:0:0.0, bằng 3 daysvà 18 hours

Trong ví dụ sau, chúng ta sẽ chuyển đổi một giá trị số thành một INTERVAL DAY TO SECONDchữ.

Chuyển đổi ADD_MONTHS trong ORACLE sang PostgreSQL

Chuyển đổi chuỗi ngày trong Oracle sang PostgreSQL

Trong Oracle, bạn có thể sử dụng CONNECT BY LEVELđể tạo chuỗi. PostgreSQL có GENERATE_SERIESchức năng cho phép bạn tạo một tập hợp dữ liệu bắt đầu tại một điểm, kết thúc ở một điểm khác và tùy ý đặt giá trị tăng dần.

Phần kết luận

Trong bài đăng này, chúng tôi đã hướng dẫn bạn cách xử lý các vấn đề di chuyển phổ biến nhất liên quan đến ngày khi chuyển từ Oracle sang Amazon RDS cho PostgreSQL hoặc Phiên bản tương thích với Amazon Aurora PostgreSQL. Để biết thêm thông tin về việc di chuyển cơ sở dữ liệu AWS, hãy tham khảo AWS DMS và AWS SCT . Bạn cũng có thể tham khảo Mẹo di chuyển dành cho nhà phát triển từ Oracle hoặc Microsoft SQL Server sang PostgreSQL và Cẩm nang di chuyển Oracle sang Aurora PostgreSQL .

Chúng tôi mời bạn để lại phản hồi của bạn trong phần bình luận.

Giới thiệu về tác giả

Neha Sharma đang làm chuyên gia cơ sở dữ liệu tại Amazon Web Services. Cô cho phép khách hàng AWS di chuyển cơ sở dữ liệu của họ sang Đám mây AWS. Ngoài công việc, cô ấy thích tích cực tham gia các hoạt động thể thao khác nhau và thích giao lưu với mọi người.

Sweta Krishna  là Chuyên gia di chuyển cơ sở dữ liệu của nhóm Dịch vụ chuyên nghiệp tại Amazon Web Services. Cô hợp tác chặt chẽ với khách hàng để giúp họ di chuyển và hiện đại hóa các giải pháp cơ sở dữ liệu của họ sang AWS.