DateTime::Format::Pg - Parse and format PostgreSQL dates and times


DateTime::Format::Pg - Parse and format PostgreSQL dates and times


  use DateTime::Format::Pg;
  my $dt = DateTime::Format::Pg->parse_datetime( '2003-01-16 23:12:01' );
  # 2003-01-16T23:12:01+0200


This module understands the formats used by PostgreSQL for its DATE, TIME, TIMESTAMP, and INTERVAL data types. It can be used to parse these formats in order to create DateTime or DateTime::Duration objects, and it can take a DateTime or DateTime::Duration object and produce a string representing it in a format accepted by PostgreSQL.


The following methods can be used to create DateTime::Format::Pg objects.


This class provides the following methods. The parse_datetime, parse_duration, format_datetime, and format_duration methods are general-purpose methods provided for compatibility with other DateTime::Format modules.

The other methods are specific to the corresponding PostgreSQL date/time data types. The names of these methods are derived from the name of the PostgreSQL data type. (Note: Prior to PostgreSQL 7.3, the TIMESTAMP type was equivalent to the TIMESTAMP WITH TIME ZONE type. This data type corresponds to the format/parse_timestamp_with_time_zone method but not to the format/parse_timestamp method.)


This class provides the following parsing methods.

As a general rule, the parsing methods accept input in any format that the PostgreSQL server can produce. However, if PostgreSQL's DateStyle is set to 'SQL' or 'PostgreSQL', dates can only be parsed correctly if the 'european' option is set correctly (i.e. same as the PostgreSQL server). The same is true for time zones and the 'australian_timezones' option in all modes but 'ISO'.

The default DateStyle, 'ISO', will always produce unambiguous results and is also parsed most efficiently by this parser class. I stronlgly recommend using this setting unless you have a good reason not to.


This class provides the following formatting methods.

The output is always in the format mandated by the SQL standard (derived from ISO 8601), which is parsed by PostgreSQL unambiguously in all DateStyle modes.


Some output formats of PostgreSQL have limitations that can only be passed on by this class.

As a general rules, none of these limitations apply to the 'ISO' output format. It is strongly recommended to use this format (and to use PostgreSQL's to_char function when another output format that's not supposed to be handled by a parser of this class is desired). 'ISO' is the default but you are advised to explicitly set it at the beginnig of the session by issuing a SET DATESTYLE TO 'ISO'; command in case the server administrator changes that setting.

When formatting DateTime objects, this class always uses a format that's handled unambiguously by PostgreSQL.


If DateStyle is set to 'PostgreSQL', 'SQL', or 'German', PostgreSQL does not send numerical time zones for the TIMESTAMPTZ (or TIMESTAMP WITH TIME ZONE) type. Unfortunatly, the time zone names used instead can be ambiguous: For example, 'EST' can mean -0500, +1000, or +1100.

You must set the 'server_tz' variable to a time zone that is identical to that of the PostgreSQL server. If the server is set to a different time zone (or the underlying operating system interprets the time zone differently), the parser will return wrong times.

You can avoid such problems by setting the server's time zone to UTC using the SET TIME ZONE 'UTC' command and setting 'server_tz' parameter to 'UTC' (or by using the ISO output format, of course).


For the SQL (for DATE and TIMSTAMP[TZ]) and the PostgreSQL (for DATE) output format, the server can send dates in both European-style 'dd/mm/yyyy' and in US-style 'mm/dd/yyyy' format. In order to parse these dates correctly, you have to pass the 'european' option to the constructor or to the parse_xxx routines.

This problem does not occur when using the ISO or German output format (and for PostgreSQL with TIMESTAMP[TZ] as month names are used then).


DateTime::Duration stores months, days, minutes and seconds separately. PostgreSQL only stores months and seconds and disregards the irregular length of days due to DST switching and the irregular length of minutes due to leap seconds. Therefore, it is not possitble to store DateTime::Duration objects as SQL INTERVALs without the loss of some information.


In the SQL and German output formats, the server does not send an indication of the sign with intervals. This means that '1 month ago' and '1 month' are both returned as '1 mon'.

This problem can only be avoided by using the 'ISO' or 'PostgreSQL' output format.


Support for this module is provided via the email list. See for more details.


Claus A. Färber <>

Currently maintained by Daisuke Maki <>


Copyright © 2003 Claus A. Färber. All rights reserved.

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

The full text of the license can be found in the LICENSE file included with this module.

SEE ALSO mailing list

 DateTime::Format::Pg - Parse and format PostgreSQL dates and times