May

24

Converting Unix Time Stamps to SQL Dates (ISO 8601) with Perl

Filed Under Computers & Tech, Software Development on May 24, 2013 | Leave a Comment

I spent quite some time this afternoon finding a reliable way of converting Unix Time Stamps (UTS) to the ISO 8601 format used in SQL databases like MySQL and PostgreSQL that does not get confused by timezones. In the end the final result is, as is so often the case with Perl, very short and simple, but since the Googles failed to find the answer for me today, I thought it would be worth sharing in case it’s of use to others in the future.

use DateTime;

# Function to convert Unix Time Stamp to ISO 8601 Date
# Arguments:
# 1) The Unix Time Stamp to convert
sub uts_to_iso8601{
    my $uts = shift;
    my $date = DateTime->from_epoch(epoch => $uts, time_zone => 'UTC');
    return $date->ymd().q{ }.$date->hms().'z';
}

The algorithm is very simple, use DateTime‘s from_epoch function to a DateTime object in the UTC timezone (AKA Zulu). Then assemble the output as YYYY-MM-DD HH:MM:SS, and append a z for Zulu. MySQL and PostgreSQL can now use the string to populate Date or Timestamp columns.

As an example, the Unix Time Stamp 1369410796 converts to 2013-05-24 15:53:16z.

Tagged with: • •