本文討論了在A(yíng)mazon RDS和Aurora 中使用PostgreSQL數據庫時(shí)，與日期/時(shí)間相關(guān)的函數，并確定PostgreSQL數據庫里的clock_timestamp()函數與Oracle中的SYSDATE函數最匹配。同時(shí)我們可以自定義基于clock_timestamp()的改進(jìn)函數(設置遷移的Oracle數據庫服務(wù)器時(shí)區)，具體參考“建議”部分中所述。

作者：Baji Shaik and Sudip Acharya
來(lái)源：https://aws.amazon.com/cn/blogs/database/converting-the-sysdate-function-from-oracle-to-postgresql/
譯者：多米爸比

在A(yíng)WS Cloud 中遷移Oracle數據庫到PostgreSQL數據庫是一個(gè)復雜的過(guò)程，從最初評估階段到遷移轉換階段，多個(gè)階段過(guò)程中會(huì )涉及不同的技術(shù)和技能。有關(guān)遷移過(guò)程的更多信息，請參閱下面幾篇文章：

Database Migration—What Do You Need to Know Before You Start?
Migration process and infrastructure considerations
Source database considerations
Target database considerations for the PostgreSQL environment

Oracle數據庫遷移到Amazon RDS或者Amazon Aurora 環(huán)境下的PostgreSQL數據庫時(shí)，最常見(jiàn)的問(wèn)題之一是SYSDATE函數。應用程序或存儲過(guò)程以及觸發(fā)器中最常用的日期/時(shí)間函數是SYSDATE。

建表字段如creation_date，last_updated_date，approved_date，在做更新操作時(shí)會(huì )被更新為設置的默認值SYSDATE或通過(guò)觸發(fā)器賦值為SYSDATE。

本文描述了在PostgreSQL數據庫里替代Oracle數據庫SYSDATE函數的一種方法。

在評估Oracle和PostgreSQL的日期/時(shí)間函數時(shí)，要考慮三個(gè)方面：

Statement級別或者事務(wù)級別的影響
客戶(hù)端時(shí)區設置的影響
Daylight Saving Time (DST)

PostgreSQL日期/時(shí)間功能概述

PostgreSQL提供了幾個(gè)函數，這些函數返回與當前日期和時(shí)間有關(guān)的值。其中一些功能是SQL標準功能，而其他功能是非SQL標準功能。

支持的SQL標準函數

以下是受支持的SQL標準函數：

CURRENT_DATE
CURRENT_TIMESTAMP/CURRENT_TIMESTAMP(precision)
CURRENT_TIME/CURRENT_TIME(precision)
LOCALTIME/LOCALTIME(precision)
LOCALTIMESTAMP/LOCALTIMESTAMP(precision)

這些函數返回當前事務(wù)的開(kāi)始時(shí)間。如果您在同一事務(wù)中多次運行這些函數，則值不會(huì )更改。這是一個(gè)內部功能特性。如果要在整個(gè)事務(wù)中使用一致的時(shí)間戳，可以使用這些功能。

CURRENT_DATE

CURRENT_DATE函數以yyyy-mm-dd格式顯示當前日期。請參見(jiàn)以下代碼：

postgres=> select CURRENT_DATE; current_date-------------- 2020-01-03(1 row)

CURRENT_TIMESTAMP/CURRENT_TIMESTAMP(precision)

CURRENT_TIME/CURRENT_TIME(precision)

這些函數返回帶時(shí)區的當前日期和時(shí)間。您可以選擇precision參數控制時(shí)間精度。此精度舍入小數位數（毫秒）。請參見(jiàn)以下代碼：

postgres=> select CURRENT_TIMESTAMP;       current_timestamp------------------------------- 2020-01-03 04:38:15.662514+00(1 row) postgres=> select CURRENT_TIMESTAMP(2);     current_timestamp--------------------------- 2020-01-03 04:38:19.75+00(1 row) postgres=> select CURRENT_TIME;    current_time-------------------- 04:40:29.409115+00(1 row) postgres=> select CURRENT_TIME(2);  current_time---------------- 04:40:38.01+00(1 row)

LOCALTIME/LOCALTIME(precision)

LOCALTIMESTAMP/LOCALTIMESTAMP(precision)

這些函數返回沒(méi)有時(shí)區的當前日期和時(shí)間。您可以選擇precision參數控制時(shí)間精度。此精度舍入小數位數（毫秒）。請參見(jiàn)以下代碼：

postgres=> select LOCALTIMESTAMP; localtimestamp---------------------------- 2020-01-03 04:42:39.405423(1 row) postgres=> select LOCALTIMESTAMP(2); localtimestamp------------------------ 2020-01-03 04:42:41.97(1 row) postgres=> select LOCALTIME; localtime----------------- 04:42:24.022253(1 row) postgres=> select LOCALTIME(2); localtime------------- 04:42:32.01(1 row)

支持的非SQL標準函數

以下是受支持的非SQL標準函數：

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

PostgreSQL還提供了返回當前語(yǔ)句的開(kāi)始時(shí)間和調用該函數時(shí)的實(shí)際當前時(shí)間的函數。

transaction_timestamp()和 statement_timestamp()

transaction_timestamp函數的行為與current_timestamp相同。但是，顧名思義，它返回事務(wù)的開(kāi)始時(shí)間，并且在整個(gè)事務(wù)中保持一致。statement_timestamp函數返回語(yǔ)句的開(kāi)始時(shí)間，與事務(wù)無(wú)關(guān)。

statement_timestamp()和transaction_timestamp()在事務(wù)的第一個(gè)命令期間返回相同的值，但在后續命令期間可能有所不同。請參見(jiàn)以下代碼：

postgres=> begin;BEGINpostgres=> select statement_timestamp(), transaction_timestamp();      statement_timestamp      |     transaction_timestamp-------------------------------+------------------------------- 2020-01-03 04:58:39.271915+00 | 2020-01-03 04:58:37.690723+00(1 row) postgres=> select pg_sleep(5); pg_sleep---------- (1 row) postgres=> select statement_timestamp(), transaction_timestamp();      statement_timestamp      |     transaction_timestamp-------------------------------+------------------------------- 2020-01-03 04:58:49.770003+00 | 2020-01-03 04:58:37.690723+00(1 row)

從上面可以看出transaction_timestamp()兩次執行結果值是一樣的，statement_timestamp()值發(fā)生了改變。

clock_timestamp()和 statement_timestamp()

clock_timestamp()函數返回當前時(shí)間的真實(shí)時(shí)間（clock時(shí)間），其值在單個(gè)SQL命令中動(dòng)態(tài)生成。

下面的代碼示例演示clock_timestamp()在同一命令中返回不同的時(shí)間戳值，但statement_timestamp()函數返回相同的值：

postgres=> WITH time_testpostgres->      AS (SELECT Statement_timestamp())postgres-> SELECT *,postgres->        Pg_sleep(3) AS "<- see the difference ->",postgres->        Statement_timestamp()postgres-> FROM   time_test;      statement_timestamp      | <- see the difference -> |      statement_timestamp-------------------------------+--------------------------+------------------------------- 2020-01-03 05:05:08.458192+00 |                          | 2020-01-03 05:05:08.458192+00(1 row) postgres=>postgres=> WITH time_testpostgres->      AS (SELECT clock_timestamp())postgres-> SELECT *,postgres->        Pg_sleep(3) AS "<- see the difference ->",postgres->        clock_timestamp()postgres-> FROM   time_test;        clock_timestamp        | <- see the difference -> |        clock_timestamp-------------------------------+--------------------------+------------------------------- 2020-01-03 05:05:18.040189+00 |                          | 2020-01-03 05:05:21.042861+00(1 row)

timeofday() vs clock_timestamp()

函數timeofday()和clock_timestamp()兩者的行為均相同。唯一的區別是timeofday()返回文本數據類(lèi)型，clock_timestamp()返回帶有時(shí)區的時(shí)間戳。在以下代碼示例中的pg_typeof列顯示了clock_timestamp()和timeofday()函數的返回類(lèi)型，分別是時(shí)間類(lèi)型和文本：

postgres=> select clock_timestamp(), pg_typeof(clock_timestamp()), timeofday(), pg_typeof(timeofday());        clock_timestamp        |        pg_typeof         |              timeofday              | pg_typeof-------------------------------+--------------------------+-------------------------------------+----------- 2020-01-03 05:28:50.203961+00 | timestamp with time zone | Fri Jan 03 05:28:50.203961 2020 UTC | text(1 row)

now()

now()函數是PostgreSQL的傳統函數，等效于transaction_timestamp()。在下面的代碼示例中，兩個(gè)函數顯示相同的時(shí)間戳（并且在同一個(gè)事務(wù)中多次執行，結果值是一樣的）：

postgres=> begin;BEGINpostgres=> select now(), transaction_timestamp();              now              |     transaction_timestamp-------------------------------+------------------------------- 2020-01-03 05:29:25.805646+00 | 2020-01-03 05:29:25.805646+00(1 row) postgres=> select pg_sleep(3); pg_sleep---------- (1 row) postgres=> select now(), transaction_timestamp();              now              |     transaction_timestamp-------------------------------+------------------------------- 2020-01-03 05:29:25.805646+00 | 2020-01-03 05:29:25.805646+00(1 row)

如何選擇要使用的PostgreSQL時(shí)間函數

Oracle 的SYSDATE函數返回語(yǔ)句執行時(shí)的數據庫庫服務(wù)器日期/時(shí)間。因此，在長(cháng)時(shí)間運行的事務(wù)中，如果您有多個(gè)SYSDATE函數，則每個(gè)語(yǔ)句執行都會(huì )返回不同的時(shí)間。

在下面的Oracle代碼示例中，您可以看到兩個(gè)不同的SYSDATE函數執行值。在Oracle中SYSDATE函數返回語(yǔ)句的開(kāi)始時(shí)間，與事務(wù)開(kāi)始的時(shí)間無(wú)關(guān)：

SET SERVEROUTPUT ON ;BEGINDBMS_OUTPUT.PUT_LINE('Start : ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));dbms_lock.sleep(30);DBMS_OUTPUT.PUT_LINE('End : ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));END;/Start : 2020-01-03 06:11:06End : 2020-01-03 06:11:36

在下面的PostgreSQL代碼示例中，在一個(gè)事務(wù)內，您可以按不同的時(shí)間間隔捕獲不同的PostgreSQL日期和時(shí)間函數返回的時(shí)間。以下代碼在兩者之間休眠15秒，比較之前和之后的結果。確定哪個(gè)函數提供與以下行為相同的輸出值SYSDATE：

DO$BODY$BEGINRAISE NOTICE 'clock_timestamp()       : %', clock_timestamp();RAISE NOTICE 'statement_timestamp()   : %', statement_timestamp();RAISE NOTICE 'now()                   : %', now();RAISE NOTICE 'current_timestamp       : %', current_timestamp;RAISE NOTICE 'transaction_timestamp() : %', transaction_timestamp();RAISE NOTICE '';RAISE NOTICE 'sleep for 15 secs and see the difference below: %', pg_sleep(15);RAISE NOTICE '';RAISE NOTICE 'clock_timestamp()       : %', clock_timestamp();RAISE NOTICE 'statement_timestamp()   : %', statement_timestamp();RAISE NOTICE 'now()                   : %', now();RAISE NOTICE 'current_timestamp       : %', current_timestamp;RAISE NOTICE 'transaction_timestamp() : %', transaction_timestamp();END;$BODY$;

上面的代碼執行結果如下：

NOTICE: clock_timestamp() : 2020-01-03 06:20:52.3715+00NOTICE: statement_timestamp() : 2020-01-03 06:20:52.371345+00NOTICE: now() : 2020-01-03 06:20:52.371345+00NOTICE: current_timestamp : 2020-01-03 06:20:52.371345+00NOTICE: transaction_timestamp() : 2020-01-03 06:20:52.371345+00NOTICE:NOTICE: sleep for 15 secs and see the difference below:NOTICE:NOTICE: clock_timestamp() : 2020-01-03 06:21:07.438274+00NOTICE: statement_timestamp() : 2020-01-03 06:20:52.371345+00NOTICE: now() : 2020-01-03 06:20:52.371345+00NOTICE: current_timestamp : 2020-01-03 06:20:52.371345+00NOTICE: transaction_timestamp() : 2020-01-03 06:20:52.371345+00

只有clock_timestamp()函數在單個(gè)事務(wù)中返回不同的時(shí)間信息。因此，最佳匹配替代SYSDATE的PostgreSQL函數是clock_timestamp()。但僅此信息還不夠，因為這些值只是時(shí)間戳值。由于不同的時(shí)區在同一時(shí)間點(diǎn)具有不同的時(shí)間戳值，因此您還必須考慮時(shí)區和DST。否則，您可能會(huì )看到與預期不同的值。

客戶(hù)端時(shí)區設置的影響

Oracle日期/時(shí)間函數（例如SYSDATE()和SYSTIMESTAMP()）返回數據庫服務(wù)器時(shí)區的當前日期和時(shí)間，而與客戶(hù)端或會(huì )話(huà)時(shí)區設置無(wú)關(guān)。但是PostgreSQL日期/時(shí)間函數會(huì )根據您的客戶(hù)端或會(huì )話(huà)時(shí)區設置返回時(shí)間。在PostgreSQL中，帶有時(shí)區值的時(shí)間戳在UTC內部存儲，并在顯示給客戶(hù)端時(shí)轉換為時(shí)區配置參數指定的時(shí)區中的本地時(shí)間。

對Oracle的影響

在Oracle中，SYSDATE對于客戶(hù)端或會(huì )話(huà)級時(shí)區設置，該功能不起作用。在以下代碼示例中，更改時(shí)區設置不會(huì )影響SYSDATE結果：

SQL> (select ‘dbtimezone’ as config, dbtimezone as offset from dual) union     (select ‘sessiontimezone’ as config, sessiontimezone as offset from dual); CONFIG OFFSET--------------- ------------------------------dbtimezone      +00:00sessiontimezone +05:30 SQL> select sysdate from dual; SYSDATE
03/01/2020 09:56:53 SQL> alter session set time_zone = ‘-08:30’;Session altered. SQL> (select ‘dbtimezone’ as config, dbtimezone as offset from dual) union     (select ‘sessiontimezone’ as config, sessiontimezone as offset from dual); CONFIG OFFSET--------------- ------------------------------dbtimezone       +00:00sessiontimezone. -08:30 SQL> select sysdate from dual; SYSDATE
03/01/2020 09:57.34

SYSDATE無(wú)論客戶(hù)端或會(huì )話(huà)如何設置時(shí)區，仍會(huì )返回服務(wù)器時(shí)區的時(shí)間戳。

對PostgreSQL的影響

在PostgreSQL中，clock_timestamp()和其他時(shí)間函數返回客戶(hù)端會(huì )話(huà)時(shí)區的當前日期和時(shí)間。請參見(jiàn)以下代碼示例：

postgres=> show timezone; TimeZone

UTC(1 row) postgres=> select clock_timestamp(); clock_timestamp

2020-01-03 06:25:36.165378+00(1 row) postgres=> set timezone = ‘America/New_York’;SETpostgres=> show timezone; TimeZone

America/New_York(1 row) postgres=> select clock_timestamp(); clock_timestamp

2020-01-03 01:25:49.329555-05(1 row) postgres=>

如果這些時(shí)間戳是由不同客戶(hù)端在不同時(shí)區返回并存儲在TIMESTAMP WITHOUT TIME ZONE類(lèi)型列中，則數據會(huì )產(chǎn)生誤導。

DST注意事項

Oracle和PostgreSQL數據庫之間的時(shí)區名稱(chēng)和偏移量實(shí)現并不一致。您可能沒(méi)有從UTC獲得匹配的時(shí)區名稱(chēng)或時(shí)間偏移。另外，PostgreSQL中時(shí)區設置的行為取決于您使用完整的時(shí)區名稱(chēng)還是時(shí)區縮寫(xiě)。PostgreSQL中的時(shí)區縮寫(xiě)定義了與UTC的特定偏移量，但是完整的時(shí)區名稱(chēng)可能意味著(zhù)一組DST日期規則。

系統目錄表pg_timezone_names中包含全時(shí)區名稱(chēng)的詳細信息，pg_timezone_abbrevs表具有時(shí)區縮寫(xiě)的詳細信息。

示例：遷移Oracle數據庫

在以下示例中，您必須將Oracle數據庫（數據庫時(shí)區MET）遷移到PostgreSQL數據庫，而應用程序代碼使用了SYSDATE。在PostgreSQL中，您需要一個(gè)類(lèi)似的時(shí)間函數。

在Oracle中，MET時(shí)區支持DST，UTC偏移為+02:00:00。PostgreSQL具有時(shí)區名稱(chēng)MET（UTS偏移+02:00:00和DST支持），并且還具有時(shí)區縮寫(xiě)MET（UTC偏移+01:00:00和不支持DST）。

在PostgreSQL數據庫中，當您在會(huì )話(huà)級別或設置AT TIME ZONE時(shí)區，如果存在匹配的全名和縮寫(xiě)，則使用縮寫(xiě)。如果將MET設置為timezone DB參數，則偏移量為+01:00:00，并且DST無(wú)效。

以下代碼示例關(guān)聯(lián)全時(shí)區表和縮寫(xiě)表，過(guò)濾顯示偏移量不同的值：

postgres=> select n.name, n.abbrev N_abbrev,a.abbrev, n.utc_offset N_utc_offset ,                 a.utc_offset, n.is_dst N_is_dst, a.is_dst  from pg_timezone_names n, pg_timezone_abbrevs a where n.name = a.abbrev   and n.utc_offset <> a.utc_offset order by 1; name | n_abbrev | abbrev | n_utc_offset | utc_offset | n_is_dst | is_dst------+----------+--------+--------------+------------+----------+--------CET | CEST | CET | 02:00:00 | 01:00:00 | t | fEET | EEST | EET | 03:00:00 | 02:00:00 | t | fMET | MEST | MET | 02:00:00 | 01:00:00 | t | fWET | WEST | WET | 01:00:00 | 00:00:00 | t | f(4 rows) postgres=> show timezone; TimeZone---------- UTC(1 row) postgres=> select clock_timestamp();        clock_timestamp------------------------------- 2020-01-03 06:29:09.672859+00(1 row) postgres=> set session time zone 'MET';SETpostgres=> select clock_timestamp() AT TIME ZONE 'MET';          timezone---------------------------- 2020-01-03 07:29:16.261098(1 row)

將會(huì )話(huà)時(shí)區設置為MET時(shí)，UTC與MET之間的時(shí)差為1小時(shí)，這是與時(shí)區縮寫(xiě)相關(guān)的偏移量。另外，此時(shí)區不支持DST。您可以使用以下代碼手動(dòng)添加間隔來(lái)進(jìn)行檢查：

postgres=> select clock_timestamp() AT TIME ZONE 'UTC' + interval '02:00:00';          ?column?---------------------------- 2020-01-03 08:29:19.732955(1 row)

對于MET時(shí)區，正確的時(shí)區應為Europe / Berlin。請參見(jiàn)以下代碼：

postgres=> select * from pg_timezone_names where lower(name) like '%berlin%';  name          | abbrev | utc_offset | is_dst---------------+--------+------------+-------- Europe/Berlin | CEST   | 02:00:00   | t

要驗證DST是否影響時(shí)區，請完成以下步驟：
查找歷史或即將發(fā)生的DST更改。有關(guān)更多信息，請參見(jiàn)即將進(jìn)行的夏時(shí)制時(shí)鐘更改。
在Oracle中，檢查DST更改前后UTC偏移量是否更改。
在PostgreSQL中，檢查DTC更改前后UTC偏移是否更改。

示例：DST更改

在以下示例中，當本地時(shí)鐘倒退1小時(shí)，MET（中歐時(shí)間）的DST更改發(fā)生在2018年10月28日上午03:00:00。原始DST UTC偏移應為02:00:00，而新DST UTC偏移應為01:00:00。

在以下Oracle示例代碼中，DST更改時(shí)需將時(shí)鐘延后1小時(shí)。

SQL> ALTER SESSION SET TIME_ZONE='UTC';Session altered. -- Before DST, 28-OCT-2018 at 00:00:00 UTC equivalent to 28-OCT-2018 at 02:00:00 MET SQL> select to_timestamp('2020-01-03 00:00:00','YYYY-MM-DD HH24:MI:SS') at time zone 'MET' from dual; TO_TIMESTAMP('2018-10-2800:00:00','YYYY-MM-DDHH24:MI:SS')ATTIMEZONE'MET'---------------------------------------------------------------------------28-OCT-18 02.00.00.000000000 AM MET -- Before DST, 28-OCT-2018 at 01:00:00 UTC equivalent to 28-OCT-2018 at 02:00:00 MET SQL> select to_timestamp('2018-10-28 01:00:00','YYYY-MM-DD HH24:MI:SS') at time zone 'MET' from dual; TO_TIMESTAMP('2018-10-2801:00:00','YYYY-MM-DDHH24:MI:SS')ATTIMEZONE'MET'---------------------------------------------------------------------------28-OCT-18 02.00.00.000000000 AM MET

以下代碼在PostgreSQL中顯示了相同的效果：

postgres=> show timezone;TimeZone----------UTC postgres => select '2018-10-28 00:00:00' AT TIME ZONE 'Europe/Berlin';timezone---------------------2018-10-28 02:00:00 postgres => select '2018-10-28 01:00:00' AT TIME ZONE 'Europe/Berlin';timezone---------------------2018-10-28 02:00:00

建議

考慮到PostgreSQL日期和時(shí)間函數不同方面的影響（例如客戶(hù)端時(shí)區設置和DST更改的影響）之后，可以在PostgreSQL中使用如下函數來(lái)模擬Oracle的SYSDATE 函數。它提供了一個(gè)語(yǔ)句級的時(shí)間戳，并不受客戶(hù)端設置的影響：

CREATE OR REPLACE FUNCTION <<Your schema>>.sysdate()RETURNS TIMESTAMP WITHOUT TIME ZONEAS $BODY$ SELECT clock_timestamp() AT TIME ZONE '<<DB Timezone>>'; $BODY$ LANGUAGE sql;

在下面的代碼示例中，修改客戶(hù)端時(shí)區后也返回一致的結果：

CREATE OR REPLACE FUNCTION public.sysdate()RETURNS TIMESTAMP WITHOUT TIME ZONEAS $BODY$ SELECT clock_timestamp() AT TIME ZONE 'Europe/Berlin'; $BODY$ LANGUAGE sql; postgres=> set session time zone 'UTC';SETpostgres=> select sysdate(); sysdate---------------------------- 2020-01-03 07:34:54.441904(1 row) postgres=> set session time zone 'Asia/Kolkata';SETpostgres=> select sysdate(); sysdate---------------------------- 2020-01-03 07:35:02.392743(1 row)

墨天輪原文鏈接：https://www.modb.pro/db/24525（復制到瀏覽器中打開(kāi)或者點(diǎn)擊“閱讀原文”）

推薦下載：144頁(yè)！分享珍藏已久的數據庫技術(shù)年刊

數據和云

ID：OraNews

如有收獲，請劃至底部，點(diǎn)擊“在看”，謝謝！

本站僅提供存儲服務(wù)，所有內容均由用戶(hù)發(fā)布，如發(fā)現有害或侵權內容，請點(diǎn)擊舉報。

欧美性猛交XXXX免费看蜜桃,成人网18免费韩国,亚洲国产成人精品区综合,欧美日韩一区二区三区高清不卡,亚洲综合一区二区精品久久