从Oracle迁移日期功能到Amazon RDS for PostgreSQL

作者：Neha Sharma 和 Sweta Krishna，发表于2024年1月3日，来源于 高级300、RDS for PostgreSQL与技术如何做。永久链接 评论

关键要点

在从 Oracle 迁移至 Amazon RDS for PostgreSQL 或 Amazon Aurora PostgreSQLCompatible Edition 时，可能需要更新数据库模式或访问数据所用的 SQL 命令。为便于迁移，AWS 提供了 AWS Schema Conversion ToolAWS SCT，该工具用于转换应用内嵌的 SQL 代码，并提供了一份包含 Oracle 数据库与 PostgreSQL 之间多个功能映射的 迁移手册。

尽管工具可以帮助自动化迁移工作，但在某些场景下，开发人员仍需手动将 Oracle 的 SQL 转换为 PostgreSQL。日期和时间戳字段在很多应用中被显著使用，用于追踪数据的时间元素。迁移时需要保持转换后日期和时间值之间的对称性，以确保依赖组件不受影响。以下部分将展示 Oracle 与 PostgreSQL 在日期和时间相关函数上的不同，以及如何进行转换，使其结果相等。

Oracle 和 PostgreSQL 的日期相关函数

Oracle 的 DATE 数据类型相当于 PostgreSQL 的 TIMESTAMP WITHOUT TIME ZONE。为了示范，本篇文章中的查询使用 Oracle 的 DATE 数据类型的 CREATEDATE 列和 PostgreSQL 的 TIMESTAMP WITHOUT TIME ZONE 类型。Oracle 和 PostgreSQL 中数据库客户端设置的格式为 YYYYMMDD HHMISSSSS。以下代码示例在 Oracle 190 和 PostgreSQL 13 版本及后续版本中进行了测试。

Oracle 的 SYSDATE 和 PostgreSQL 的 NOW() 及 CURRENTDATE

在 Oracle 中，SYSDATE 用于获取系统当前的日期和时间，其格式取决于 NLSDATEFORMAT 初始化参数。

而 PostgreSQL 有内置的 NOW() 函数，它返回当前的日期和时间精确到微秒及时区，而 CURRENTDATE 函数仅返回当前日期。DATE 和 TIMESTAMP 的格式取决于数据库参数或数据库客户端设置。这两个函数与 Oracle 的 SYSDATE 函数并不等价，Oracle 的 SYSDATE 输出为 DATE 数据类型，而 PostgreSQL 的 NOW() 输出为 TIMESTAMP 数据类型，CURRENTDATE 则为 DATE 数据类型。有关更多信息，请参阅 Oracle 和 PostgreSQL 系统日期和时间戳的不同之处。

下表显示了 Oracle 的 SYSDATE 和 PostgreSQL 的 CURRENTDATE 之间的区别。

你可以将 PostgreSQL 的 CURRENTTIMESTAMP 转换为 TIMESTAMP WITHOUT TIME ZONE，以获得与 Oracle 的 SYSDATE 类似的结果。

Oracle 的 TRUNC 与 PostgreSQL 的 DATETRUNC

在 Oracle 中，你可以使用 TRUNC 函数与 DATE 数据类型来截断日期至特定精度。 PostgreSQL 中可以使用 CAST 或 DATETRUNC 来实现日期或时间戳列的截断。CAST 用于将一种数据类型转换为另一种，而 DATETRUNC 用于截断 TIMESTAMP 或 INTERVAL 列。

Oracle 的 TRUNC BY DAY/MONTH/YEAR 与 PostgreSQL

使用 DATE 或 TIMESTAMP 数据类型列的截断函数可以将交易时间戳四舍五入到所需的粒度。对于报告应用，该函数可以用于创建基于时间的聚合，比如每日、每周或每月的数据汇总。

Oracle 的 TRUNC (ltDateFieldgt ltfmtgt) (date) 函数返回的是时间部分被截断到所指定格式模型fmt单位的 DATE：DD/MONTH/YEAR。 返回的值始终为数据类型 DATE。相似的功能可以通过 PostgreSQL 的 DATETRUNC() 函数实现。DATETRUNC() 用于将 DATE、TIME 或 TIMESTAMP 截断到所需的精度。

在以下示例中，Oracle 的 TRUNC 函数将 CREATEDATE 列的时间部分从 125319000 截断至 000000000，而在 PostgreSQL 中则使用 DATETRUNC 函数来实现。

Oracle 的 TODATE 和 PostgreSQL 的 TODATE

TODATE 函数用于将字符数据类型转换为日期。在 Oracle 和 PostgreSQL 中，结果的格式有所不同。在 Oracle 中，结果为 ‘YYYYMMDD 000000000’ 格式，而在 PostgreSQL 中，它则为 ‘YYYYMMDD’。因此需要显式类型转换。

下表显示了在 Oracle 和 PostgreSQL 中使用 TODATE 时格式为 YYYYMMDD 的差异。

要将 TODATE(ltDATEFIELDgt YYYYMMDD) 从 Oracle 转换为 PostgreSQL 中的等价形式，需要执行类型转换：TODATE(ltDATEFIELDgt YYYYMMDD)TIMESTAMP(0) 或 TODATE(ltDATEFIELDgt YYYYMMDDTIMESTAMP WITHOUT TIME ZONE)。

以下示例在 PostgreSQL 中将 TODATE(ltDATEFIELDgt YYYYMMDD) 转换为 TIMESTAMP(0)。

下面的表格展示了使用格式为 YYYYMMDD HH24MISS 的 TODATE 函数时，在 Oracle 和 PostgreSQL 中的不同结果。

在 Oracle 中，TODATE(ltDATEFIELDgt YYYYMMDD HH24MISS) 函数需转换为 PostgreSQL 中的 TOTIMESTAMP，并加上类型转换：TOTIMESTAMP(ltDateFieldgt ‘YYYYMMDD HH24MISS’)TIMESTAMP(0) 或 TOTIMESTAMP(ltDateFieldgt ‘YYYYMMDD HH24MISS’)TIMESTAMP WITHOUT TIME ZONE。

以下示例中，PostgreSQL 将 TODATE(ltDATEFIELDgt YYYYMMDD) 转换为 TIMESTAMP WITHOUT TIME ZONE。

向日期数据类型添加整数

在需要向 DATE 列添加天数的场景中，Oracle 和 PostgreSQL 的结果是一致的。然而，当向 PostgreSQL 的 TIMESTAMP 添加天数时会出现错误。

要将这类查询从 Oracle 转换为 PostgreSQL，需向时间戳列中添加 INTERVAL。

PostgreSQL 不允许整数直接加到 TIMESTAMP 列上，会返回错误。要解决此问题，请向 TIMESTAMP 列添加 INTERVAL。

SELECT CREATEDATE AS CREATEDATE CREATEDATE 2 AS NEWDATE FROM TABDATE

SQL Error [42883] ERROR operator does not exist timestamp without time zone integer Hint No operator matches the given name and argument types You might need to add explicit type casts

向日期类型列添加整数列

有时，天数可能作为 INTEGER