logo
搜索
我的图书馆

发文章

发文工具

其他工具

留言交流
搜索
分享

【原】深度解析：Oracle数据库SYSDATE到PostgreSQL数据库最佳转换

 数据和云 2020-07-01

本文讨论了在Amazon RDS和Aurora 中使用PostgreSQL数据库时，与日期/时间相关的函数，并确定PostgreSQL数据库里的clock_timestamp()函数与Oracle中的SYSDATE函数最匹配。同时我们可以自定义基于clock_timestamp()的改进函数(设置迁移的Oracle数据库服务器时区)，具体参考“建议”部分中所述。

作者：Baji Shaik and Sudip Acharya
来源：https://aws.amazon.com/cn/blogs/database/converting-the-sysdate-function-from-oracle-to-postgresql/
译者：多米爸比


在AWS Cloud 中迁移Oracle数据库到PostgreSQL数据库是一个复杂的过程，从最初评估阶段到迁移转换阶段，多个阶段过程中会涉及不同的技术和技能。有关迁移过程的更多信息，请参阅下面几篇文章：

  • 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 环境下的PostgreSQL数据库时，最常见的问题之一是SYSDATE函数。应用程序或存储过程以及触发器中最常用的日期/时间函数是SYSDATE。

建表字段如creation_date，last_updated_date，approved_date，在做更新操作时会被更新为设置的默认值SYSDATE或通过触发器赋值为SYSDATE。

本文描述了在PostgreSQL数据库里替代Oracle数据库SYSDATE函数的一种方法。
在评估Oracle和PostgreSQL的日期/时间函数时，要考虑三个方面：
  • Statement级别或者事务级别的影响
  • 客户端时区设置的影响
  • Daylight Saving Time (DST)


PostgreSQL日期/时间功能概述

PostgreSQL提供了几个函数，这些函数返回与当前日期和时间有关的值。其中一些功能是SQL标准功能，而其他功能是非SQL标准功能。


支持的SQL标准函数

以下是受支持的SQL标准函数：
  • CURRENT_DATE
  • CURRENT_TIMESTAMP/CURRENT_TIMESTAMP(precision)
  • CURRENT_TIME/CURRENT_TIME(precision)
  • LOCALTIME/LOCALTIME(precision)
  • LOCALTIMESTAMP/LOCALTIMESTAMP(precision)
这些函数返回当前事务的开始时间。如果您在同一事务中多次运行这些函数，则值不会更改。这是一个内部功能特性。如果要在整个事务中使用一致的时间戳，可以使用这些功能。


CURRENT_DATE

CURRENT_DATE函数以yyyy-mm-dd格式显示当前日期。请参见以下代码：
postgres=> select CURRENT_DATE; current_date-------------- 2020-01-03(1 row)

CURRENT_TIMESTAMP/CURRENT_TIMESTAMP(precision)

CURRENT_TIME/CURRENT_TIME(precision)

这些函数返回带时区的当前日期和时间。您可以选择precision参数控制时间精度。此精度舍入小数位数（毫秒）。请参见以下代码：
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)

这些函数返回没有时区的当前日期和时间。您可以选择precision参数控制时间精度。此精度舍入小数位数（毫秒）。请参见以下代码：
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还提供了返回当前语句的开始时间和调用该函数时的实际当前时间的函数。


transaction_timestamp()和 statement_timestamp()

transaction_timestamp函数的行为与current_timestamp相同。但是，顾名思义，它返回事务的开始时间，并且在整个事务中保持一致。statement_timestamp函数返回语句的开始时间，与事务无关。

statement_timestamp()和transaction_timestamp()在事务的第一个命令期间返回相同的值，但在后续命令期间可能有所不同。请参见以下代码：
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()值发生了改变。


clock_timestamp()和 statement_timestamp()

clock_timestamp()函数返回当前时间的真实时间（clock时间），其值在单个SQL命令中动态生成。

下面的代码示例演示clock_timestamp()在同一命令中返回不同的时间戳值，但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()返回文本数据类型，clock_timestamp()返回带有时区的时间戳。在以下代码示例中的pg_typeof列显示了clock_timestamp()和timeofday()函数的返回类型，分别是时间类型和文本：
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()。在下面的代码示例中，两个函数显示相同的时间戳（并且在同一个事务中多次执行，结果值是一样的）：
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时间函数

Oracle 的SYSDATE函数返回语句执行时的数据库库服务器日期/时间。因此，在长时间运行的事务中，如果您有多个SYSDATE函数，则每个语句执行都会返回不同的时间。

在下面的Oracle代码示例中，您可以看到两个不同的SYSDATE函数执行值。在Oracle中SYSDATE函数返回语句的开始时间，与事务开始的时间无关：
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代码示例中，在一个事务内，您可以按不同的时间间隔捕获不同的PostgreSQL日期和时间函数返回的时间。以下代码在两者之间休眠15秒，比较之前和之后的结果。确定哪个函数提供与以下行为相同的输出值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()函数在单个事务中返回不同的时间信息。因此，最佳匹配替代SYSDATE的PostgreSQL函数是clock_timestamp()。但仅此信息还不够，因为这些值只是时间戳值。由于不同的时区在同一时间点具有不同的时间戳值，因此您还必须考虑时区和DST。否则，您可能会看到与预期不同的值。


客户端时区设置的影响

Oracle日期/时间函数（例如SYSDATE()和SYSTIMESTAMP()）返回数据库服务器时区的当前日期和时间，而与客户端或会话时区设置无关。但是PostgreSQL日期/时间函数会根据您的客户端或会话时区设置返回时间。在PostgreSQL中，带有时区值的时间戳在UTC内部存储，并在显示给客户端时转换为时区配置参数指定的时区中的本地时间。


对Oracle的影响

在Oracle中，SYSDATE对于客户端或会话级时区设置，该功能不起作用。在以下代码示例中，更改时区设置不会影响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无论客户端或会话如何设置时区，仍会返回服务器时区的时间戳。


对PostgreSQL的影响

在PostgreSQL中，clock_timestamp()和其他时间函数返回客户端会话时区的当前日期和时间。请参见以下代码示例：
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=>

如果这些时间戳是由不同客户端在不同时区返回并存储在TIMESTAMP WITHOUT TIME ZONE类型列中，则数据会产生误导。


DST注意事项

Oracle和PostgreSQL数据库之间的时区名称和偏移量实现并不一致。您可能没有从UTC获得匹配的时区名称或时间偏移。另外，PostgreSQL中时区设置的行为取决于您使用完整的时区名称还是时区缩写。PostgreSQL中的时区缩写定义了与UTC的特定偏移量，但是完整的时区名称可能意味着一组DST日期规则。

系统目录表pg_timezone_names中包含全时区名称的详细信息，pg_timezone_abbrevs表具有时区缩写的详细信息。


示例：迁移Oracle数据库

在以下示例中，您必须将Oracle数据库（数据库时区MET）迁移到PostgreSQL数据库，而应用程序代码使用了SYSDATE。在PostgreSQL中，您需要一个类似的时间函数。

在Oracle中，MET时区支持DST，UTC偏移为+02:00:00。PostgreSQL具有时区名称MET（UTS偏移+02:00:00和DST支持），并且还具有时区缩写MET（UTC偏移+01:00:00和不支持DST）。

在PostgreSQL数据库中，当您在会话级别或设置AT TIME ZONE时区，如果存在匹配的全名和缩写，则使用缩写。如果将MET设置为timezone DB参数，则偏移量为+01:00:00，并且DST无效。

以下代码示例关联全时区表和缩写表，过滤显示偏移量不同的值：
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)

将会话时区设置为MET时，UTC与MET之间的时差为1小时，这是与时区缩写相关的偏移量。另外，此时区不支持DST。您可以使用以下代码手动添加间隔来进行检查：
postgres=> select clock_timestamp() AT TIME ZONE 'UTC' + interval '02:00:00';          ?column?---------------------------- 2020-01-03 08:29:19.732955(1 row)

对于MET时区，正确的时区应为Europe / Berlin。请参见以下代码：
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是否影响时区，请完成以下步骤：
查找历史或即将发生的DST更改。有关更多信息，请参见即将进行的夏时制时钟更改。
在Oracle中，检查DST更改前后UTC偏移量是否更改。
在PostgreSQL中，检查DTC更改前后UTC偏移是否更改。


示例：DST更改

在以下示例中，当本地时钟倒退1小时，MET（中欧时间）的DST更改发生在2018年10月28日上午03:00:00。原始DST UTC偏移应为02:00:00，而新DST UTC偏移应为01:00:00。
在以下Oracle示例代码中，DST更改时需将时钟延后1小时。
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日期和时间函数不同方面的影响（例如客户端时区设置和DST更改的影响）之后，可以在PostgreSQL中使用如下函数来模拟Oracle的SYSDATE 函数。它提供了一个语句级的时间戳，并不受客户端设置的影响：
CREATE OR REPLACE FUNCTION <<Your schema>>.sysdate()RETURNS TIMESTAMP WITHOUT TIME ZONEAS$BODY$ SELECT clock_timestamp() AT TIME ZONE '<<DB Timezone>>';$BODY$LANGUAGE sql;

在下面的代码示例中，修改客户端时区后也返回一致的结果：

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./db/24525（复制到浏览器中打开或者点击“阅读原文”）

推荐下载：144页！分享珍藏已久的数据库技术年刊

数据和云

ID：OraNews

如有收获，请划至底部，点击“在看”，谢谢！

    转藏 分享 献花（0）

    来自： 数据和云 > 《待分类》

    举报/认领

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多