DateTime::Format::Pg(3pm)

1DateTime::Format::Pg(3pUms)er Contributed Perl DocumentatDiaotneTime::Format::Pg(3pm)
2
3
4

NAME

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

SYNOPSIS

9         use DateTime::Format::Pg;
10
11         my $dt = DateTime::Format::Pg->parse_datetime( '2003-01-16 23:12:01' );
12
13         # 2003-01-16 23:12:01
14         DateTime::Format::Pg->format_datetime($dt);
15

DESCRIPTION

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

CONSTRUCTORS

25       The following methods can be used to create "DateTime::Format::Pg"
26       objects.
27
28       •   new( name => value, ... )
29
30           Creates a new "DateTime::Format::Pg" instance. This is generally
31           not required for simple operations. If you wish to use a different
32           parsing style from the default then it is more comfortable to
33           create an object.
34
35             my $parser = DateTime::Format::Pg->new()
36             my $copy = $parser->new( 'european' => 1 );
37
38           This method accepts the following options:
39
40           •       european
41
42                   If european is set to non-zero, dates are assumed to be in
43                   european dd/mm/yyyy format. The default is to assume US
44                   mm/dd/yyyy format (because this is the default for
45                   PostgreSQL).
46
47                   This option only has an effect if PostgreSQL is set to
48                   output dates in the 'PostgreSQL' (DATE only) and 'SQL'
49                   (DATE and TIMESTAMP) styles.
50
51                   Note that you don't have to set this option if the
52                   PostgreSQL server has been set to use the 'ISO' format,
53                   which is the default.
54
55           •       server_tz
56
57                   This option can be set to a "DateTime::TimeZone" object or
58                   a string that contains a time zone name.
59
60                   This value must be set to the same value as the PostgreSQL
61                   server's time zone in order to parse TIMESTAMP WITH
62                   TIMEZONE values in the 'PostgreSQL', 'SQL', and 'German'
63                   formats correctly.
64
65                   Note that you don't have to set this option if the
66                   PostgreSQL server has been set to use the 'ISO' format,
67                   which is the default.
68
69       •   clone()
70
71           This method is provided for those who prefer to explicitly clone
72           via a method called "clone()".
73
74              my $clone = $original->clone();
75
76           If called as a class method it will die.
77

METHODS

79       This class provides the following methods. The parse_datetime,
80       parse_duration, format_datetime, and format_duration methods are
81       general-purpose methods provided for compatibility with other
82       "DateTime::Format" modules.
83
84       The other methods are specific to the corresponding PostgreSQL
85       date/time data types. The names of these methods are derived from the
86       name of the PostgreSQL data type.  (Note: Prior to PostgreSQL 7.3, the
87       TIMESTAMP type was equivalent to the TIMESTAMP WITH TIME ZONE type.
88       This data type corresponds to the format/parse_timestamp_with_time_zone
89       method but not to the format/parse_timestamp method.)
90
91   PARSING METHODS
92       This class provides the following parsing methods.
93
94       As a general rule, the parsing methods accept input in any format that
95       the PostgreSQL server can produce. However, if PostgreSQL's DateStyle
96       is set to 'SQL' or 'PostgreSQL', dates can only be parsed correctly if
97       the 'european' option is set correctly (i.e. same as the PostgreSQL
98       server).  The same is true for time zones and the
99       'australian_timezones' option in all modes but 'ISO'.
100
101       The default DateStyle, 'ISO', will always produce unambiguous results
102       and is also parsed most efficiently by this parser class. I strongly
103       recommend using this setting unless you have a good reason not to.
104
105       •   parse_datetime($string,...)
106
107           Given a string containing a date and/or time representation, this
108           method will return a new "DateTime" object.
109
110           If the input string does not contain a date, it is set to
111           1970-01-01.  If the input string does not contain a time, it is set
112           to 00:00:00.  If the input string does not contain a time zone, it
113           is set to the floating time zone.
114
115           If given an improperly formatted string, this method may die.
116
117       •   parse_timestamptz($string,...)
118
119       •   parse_timestamp_with_time_zone($string,...)
120
121           Given a string containing a timestamp (date and time)
122           representation, this method will return a new "DateTime" object.
123           This method is suitable for the TIMESTAMPTZ (or TIMESTAMP WITH TIME
124           ZONE) type.
125
126           If the input string does not contain a time zone, it is set to the
127           floating time zone.
128
129           Please note that PostgreSQL does not actually store a time zone
130           along with the TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ) type but
131           will just return a time stamp converted for the server's local time
132           zone.
133
134           If given an improperly formatted string, this method may die.
135
136       •   parse_timestamp($string,...)
137
138       •   parse_timestamp_without_time_zone($string,...)
139
140           Similar to the functions above, but always returns a "DateTime"
141           object with a floating time zone. This method is suitable for the
142           TIMESTAMP (or TIMESTAMP WITHOUT TIME ZONE) type.
143
144           If the server does return a time zone, it is ignored.
145
146           If given an improperly formatted string, this method may die.
147
148       •   parse_timetz($string,...)
149
150       •   parse_time_with_time_zone($string,...)
151
152           Given a string containing a time representation, this method will
153           return a new "DateTime" object. The date is set to 1970-01-01. This
154           method is suitable for the TIMETZ (or TIME WITH TIME ZONE) type.
155
156           If the input string does not contain a time zone, it is set to the
157           floating time zone.
158
159           Please note that PostgreSQL stores a numerical offset with its TIME
160           WITH TIME ZONE (or TIMETZ) type. It does not store a time zone name
161           (such as 'Europe/Rome').
162
163           If given an improperly formatted string, this method may die.
164
165       •   parse_time($string,...)
166
167       •   parse_time_without_time_zone($string,...)
168
169           Similar to the functions above, but always returns an "DateTime"
170           object with a floating time zone. If the server returns a time
171           zone, it is ignored. This method is suitable for use with the TIME
172           (or TIME WITHOUT TIME ZONE) type.
173
174           This ensures that the resulting "DateTime" object will always have
175           the time zone expected by your application.
176
177           If given an improperly formatted string, this method may die.
178
179       •   parse_date($string,...)
180
181           Given a string containing a date representation, this method will
182           return a new "DateTime" object. The time is set to 00:00:00
183           (floating time zone). This method is suitable for the DATE type.
184
185           If given an improperly formatted string, this method may die.
186
187       •   parse_duration($string)
188
189       •   parse_interval($string)
190
191           Given a string containing a duration (SQL type INTERVAL)
192           representation, this method will return a new "DateTime::Duration"
193           object.
194
195           If given an improperly formatted string, this method may die.
196
197   FORMATTING METHODS
198       This class provides the following formatting methods.
199
200       The output is always in the format mandated by the SQL standard
201       (derived from ISO 8601), which is parsed by PostgreSQL unambiguously in
202       all DateStyle modes.
203
204       •   format_datetime($datetime,...)
205
206           Given a "DateTime" object, this method returns a string appropriate
207           as input for all date and date/time types of PostgreSQL. It will
208           contain date and time.
209
210           If the time zone of the "DateTime" part is floating, the resulting
211           string will contain no time zone, which will result in the server's
212           time zone being used. Otherwise, the numerical offset of the time
213           zone is used.
214
215       •   format_time($datetime,...)
216
217       •   format_time_without_time_zone($datetime,...)
218
219           Given a "DateTime" object, this method returns a string appropriate
220           as input for the TIME type (also known as TIME WITHOUT TIME ZONE),
221           which will contain the local time of the "DateTime" object and no
222           time zone.
223
224       •   format_timetz($datetime)
225
226       •   format_time_with_time_zone($datetime)
227
228           Given a "DateTime" object, this method returns a string appropriate
229           as input for the TIME WITH TIME ZONE type (also known as TIMETZ),
230           which will contain the local part of the "DateTime" object and a
231           numerical time zone.
232
233           You should not use the TIME WITH TIME ZONE type to store dates with
234           floating time zones.  If the time zone of the "DateTime" part is
235           floating, the resulting string will contain no time zone, which
236           will result in the server's time zone being used.
237
238       •   format_date($datetime)
239
240           Given a "DateTime" object, this method returns a string appropriate
241           as input for the DATE type, which will contain the date part of the
242           "DateTime" object.
243
244       •   format_timestamp($datetime)
245
246       •   format_timestamp_without_time_zone($datetime)
247
248           Given a "DateTime" object, this method returns a string appropriate
249           as input for the TIMESTAMP type (also known as TIMESTAMP WITHOUT
250           TIME ZONE), which will contain the local time of the "DateTime"
251           object and no time zone.
252
253       •   format_timestamptz($datetime)
254
255       •   format_timestamp_with_time_zone($datetime)
256
257           Given a "DateTime" object, this method returns a string appropriate
258           as input for the TIMESTAMP WITH TIME ZONE type, which will contain
259           the local part of the "DateTime" object and a numerical time zone.
260
261           You should not use the TIMESTAMP WITH TIME ZONE type to store dates
262           with floating time zones.  If the time zone of the "DateTime" part
263           is floating, the resulting string will contain no time zone, which
264           will result in the server's time zone being used.
265
266       •   format_duration($du)
267
268       •   format_interval($du)
269
270           Given a "DateTime::Duration" object, this method returns a string
271           appropriate as input for the INTERVAL type.
272

LIMITATIONS

274       Some output formats of PostgreSQL have limitations that can only be
275       passed on by this class.
276
277       As a general rules, none of these limitations apply to the 'ISO' output
278       format.  It is strongly recommended to use this format (and to use
279       PostgreSQL's to_char function when another output format that's not
280       supposed to be handled by a parser of this class is desired). 'ISO' is
281       the default but you are advised to explicitly set it at the beginning
282       of the session by issuing a SET DATESTYLE TO 'ISO'; command in case the
283       server administrator changes that setting.
284
285       When formatting DateTime objects, this class always uses a format
286       that's handled unambiguously by PostgreSQL.
287
288   TIME ZONES
289       If DateStyle is set to 'PostgreSQL', 'SQL', or 'German', PostgreSQL
290       does not send numerical time zones for the TIMESTAMPTZ (or TIMESTAMP
291       WITH TIME ZONE) type. Unfortunately, the time zone names used instead
292       can be ambiguous: For example, 'EST' can mean -0500, +1000, or +1100.
293
294       You must set the 'server_tz' variable to a time zone that is identical
295       to that of the PostgreSQL server. If the server is set to a different
296       time zone (or the underlying operating system interprets the time zone
297       differently), the parser will return wrong times.
298
299       You can avoid such problems by setting the server's time zone to UTC
300       using the SET TIME ZONE 'UTC' command and setting 'server_tz' parameter
301       to 'UTC' (or by using the ISO output format, of course).
302
303   EUROPEAN DATES
304       For the SQL (for DATE and TIMSTAMP[TZ]) and the PostgreSQL (for DATE)
305       output format, the server can send dates in both European-style
306       'dd/mm/yyyy' and in US-style 'mm/dd/yyyy' format. In order to parse
307       these dates correctly, you have to pass the 'european' option to the
308       constructor or to the "parse_xxx" routines.
309
310       This problem does not occur when using the ISO or German output format
311       (and for PostgreSQL with TIMESTAMP[TZ] as month names are used then).
312
313   INTERVAL ELEMENTS
314       "DateTime::Duration" stores months, days, minutes and seconds
315       separately. PostgreSQL only stores months and seconds and disregards
316       the irregular length of days due to DST switching and the irregular
317       length of minutes due to leap seconds. Therefore, it is not possible to
318       store "DateTime::Duration" objects as SQL INTERVALs without the loss of
319       some information.
320
321   NEGATIVE INTERVALS
322       In the SQL and German output formats, the server does not send an
323       indication of the sign with intervals. This means that '1 month ago'
324       and '1 month' are both returned as '1 mon'.
325
326       This problem can only be avoided by using the 'ISO' or 'PostgreSQL'
327       output format.
328

SUPPORT

330       Support for this module is provided via the datetime@perl.org email
331       list.  See http://lists.perl.org/ for more details.
332

AUTHOR

334       Daisuke Maki <daisuke@endeworks.jp>
335

AUTHOR EMERITUS

337       Claus A. Faerber <perl@faerber.muc.de>
338

COPYRIGHT

340       Copyright (c) 2003 Claus A. Faerber. Copyright (c) 2005-2007 Daisuke
341       Maki
342
343       This program is free software; you can redistribute it and/or modify it
344       under the same terms as Perl itself.
345
346       The full text of the license can be found in the LICENSE file included
347       with this module.
348