Karjala's blog

I created a function in Perl called pg_dt, that will convert PostgreSQL's datetime values into Perl's DateTime values and vice versa. This is useful both when you want to store DateTime values into the database, or want to convert the pg datetime/timestamp value from the database into a DateTime object value that Perl can use.

package Local::Util;

use v5.38;

use DateTime;
use DateTime::Format::Pg;

use Exporter 'import';
our @EXPORT_OK = qw/ pg_dt /;

# converts:
#   - PostgreSQL timestamp strings to DateTime objects,
#   - DateTime objects to PostgreSQL timestamp strings,
#   - epoch numbers to PostgreSQL timestamp strings
sub pg_dt :prototype(_) ($thing) {
    if (! length ref $thing and $thing =~ /^[0-9]+(\.[0-9]+)?\z/) {
        # epoch (number) to postgresql date string
        $thing = DateTime->from_epoch(epoch => $thing, time_zone => 'UTC');
        return DateTime::Format::Pg->format_timestamp_with_time_zone($thing);
    } elsif (! length ref $thing) {
        # postgresql date string to DateTime object
        return DateTime::Format::Pg->parse_timestamp_with_time_zone($thing);
    } elsif ($thing isa DateTime) {
        # DateTime object to postgresql date string
        return DateTime::Format::Pg->format_timestamp_with_time_zone($thing);
    } else {
        die 'invalid $thing: ', ref $thing;
    }
}

Then you can do stuff like:

my $expiration = DateTime->now->add(days => 7);

$db->insert(
    'a_table',
    {
        name => 'a_name',
        expiration => pg_dt $expiration,
    },
);

...and...

my @rows = $db->select(
    'a_table',
    [qw/ name expiration /],
)->hashes->@*;

$_->{expiration} = pg_dt $_->{expiration} foreach @rows;

You can also do:

my $now = pg_dt(time);

And when combined with the following:

sub DateTime::TO_JSON { $_[0]->epoch }

...you can have the Mojolicious JSON renderer automatically convert DateTime objects into epoch values, for the front-end to use.