如何在PostgreSQL PLPerl函数中进行日期/时间操作

Question

在plperl中编写Postgres函数(存储过程)时，我了解到不可能加载perl模块，例如"use Time::Piece;"。考虑到这一点，在perl中处理日期/时间操作和比较的最佳方法是什么?使用plperlu和加载模块对我来说不是一个选择。

我不得不求助于这样的查询:

$query = "SELECT extract(day from timestamp '$eventDate') AS day,
                 extract(month from timestamp '$eventDate') AS month,
                 extract(year from timestamp '$eventDate') AS year";

我还使用SQL将时间戳转换为epoch时间，以便在perl中进行比较。我希望这是一个愚蠢的问题，有一个更直接和简单的方法来处理日期/时间功能。谢谢。

Answer 1

要做到这一点并不容易。您有四个基本选项，使用哪个取决于您的具体情况。

1. 在存储过程中使用SQL

   这通常比我喜欢的更冗长，但它可能是最简单和最有效的。例子:

   my $now = spi_exec_query('SELECT EXTRACT(epoch FROM NOW())')->{rows}[0]->{date_part}
       || die "Unable to determine current time";
   

   或者同时使用多个值:

   my $row = spi_exec_query('SELECT EXTRACT(...) AS a,EXTRACT(...) AS b,... AS x')->{rows}
       or die "Some useful message...";
   my $a = $row->{a};
   my $b = $row->{b};
   ...
   my $x = $row->{x};
   

2. 用SQL或plpgsql存储过程包装plperl SP，该存储过程传递所需的日期值:

   CREATE FUNCTION foo AS $$
       SELECT foo_pl(EXTRACT(...),EXTRACT(...),...)
   $$ LANGUAGE SQL;
   

3. 在perl中手动操作日期。

   这通常是丑陋的(这也是为什么日期操作模块首先存在的原因!)，但可能适用于足够简单的操作，其中闰年，时区等无关紧要(在时间跨度中添加一个小时或一天等)。

4. 需要一个更复杂的perl模块，比如DateTime

   这通常需要使用plperlu，而不是标准的plperl，这可能会带来一些安全问题。将如此大的模块加载到存储过程中也不太可能提高性能。但是，如果您有足够的系统内存，并且您对plperlu的安全问题感到满意(本文未涉及)，则可以选择它。

Answer 2

几乎任何Postgres数据库框架都会自动为您转换Date和DateTime数据类型。当这些类型插入Postgres时，如果它们不是自动转换的，那么UTC格式总是Postgres可以理解的格式。

根据下面的注释，我只能参考文档完备的日期/时间函数列表，因为问题中没有具体的操作类型:Postgres日期/时间函数和操作符