How to do date/time manipulations in PostgreSQL PLPerl function

Question

While writing Postgres functions (stored procedures) in plperl I've learned that it's not possible to load perl modules such as "use Time::Piece;". Given that, what's the best way to handle date/time manipulations and comparisons in plperl? Using plperlu and loading modules is not an option for me.

I've had to resort to queries such as the following:

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

I've also used SQL to convert timestamps to epoch time in order to do comparisons in perl. I'm hoping this is a silly question and there's a much more direct and simple way to deal with date/time functionality. Thanks.

Answer 1

There's not an easy way to do this. You have four basic options, and which to use depends on your specific situation.

1. Use SQL from within your stored procedure.

   This is often more verbose than I like, but it's probably the easiest and most efficient. Examples:

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

   Or for multiple values at once:

   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. Wrap your plperl SP with a SQL or plpgsql stored procedure, which passes in the required date value(s):

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

3. Do the date manipulation manually within perl.

   This is often ugly (and why date-manipulating modules exist in the first place!) but might work for sufficiently simple operations, where leap years, time zones, etc, are irrelevant (adding a single hour or day to a time span, etc).

4. Require a more sophisticated perl module, such as DateTime.

   This usually requires using plperlu, instead of standard plperl, which can come with some security concerns. It's also not likely to benefit performance to load such large modules into your stored procedures. But if you have plenty of system memory, and you are comfortable with security concerns of plperlu (not covered in this post), it can be an option.

Answer 2

Just about any Postgres database framework will convert Date and DateTime data types automatically for you. And when inserting those types into Postgres, if they are not converted automatically, then UTC format is the one that Postgres can understand always.

UPDATE

As per the comments that followed, I can only refer to the list of well-documented Date/Time functions, since there is no specific in the question as to the type of operations needed: Postgres Date/Time Functions and Operators