1package DateTime::Format::Pg; 2 3use strict; 4use vars qw ($VERSION); 5 6use Carp; 7use DateTime 0.13; 8use DateTime::Duration; 9use DateTime::Format::Builder 0.72; 10use DateTime::TimeZone 0.06; 11use DateTime::TimeZone::UTC; 12use DateTime::TimeZone::Floating; 13 14$VERSION = '0.16014'; 15$VERSION = eval $VERSION; 16 17our @ISA = ('DateTime::Format::Builder'); 18 19=head1 NAME 20 21DateTime::Format::Pg - Parse and format PostgreSQL dates and times 22 23=head1 SYNOPSIS 24 25 use DateTime::Format::Pg; 26 27 my $dt = DateTime::Format::Pg->parse_datetime( '2003-01-16 23:12:01' ); 28 29 # 2003-01-16 23:12:01 30 DateTime::Format::Pg->format_datetime($dt); 31 32=head1 DESCRIPTION 33 34This module understands the formats used by PostgreSQL for its DATE, TIME, 35TIMESTAMP, and INTERVAL data types. It can be used to parse these formats in 36order to create C<DateTime> or C<DateTime::Duration> objects, and it can take a 37C<DateTime> or C<DateTime::Duration> object and produce a string representing 38it in a format accepted by PostgreSQL. 39 40=head1 CONSTRUCTORS 41 42The following methods can be used to create C<DateTime::Format::Pg> objects. 43 44=over 4 45 46=item * new( name => value, ... ) 47 48Creates a new C<DateTime::Format::Pg> instance. This is generally not 49required for simple operations. If you wish to use a different parsing 50style from the default then it is more comfortable to create an object. 51 52 my $parser = DateTime::Format::Pg->new() 53 my $copy = $parser->new( 'european' => 1 ); 54 55This method accepts the following options: 56 57=over 8 58 59=item * european 60 61If european is set to non-zero, dates are assumed to be in european 62dd/mm/yyyy format. The default is to assume US mm/dd/yyyy format 63(because this is the default for PostgreSQL). 64 65This option only has an effect if PostgreSQL is set to output dates in 66the 'PostgreSQL' (DATE only) and 'SQL' (DATE and TIMESTAMP) styles. 67 68Note that you don't have to set this option if the PostgreSQL server has 69been set to use the 'ISO' format, which is the default. 70 71=item * server_tz 72 73This option can be set to a C<DateTime::TimeZone> object or a string 74that contains a time zone name. 75 76This value must be set to the same value as the PostgreSQL server's time 77zone in order to parse TIMESTAMP WITH TIMEZONE values in the 78'PostgreSQL', 'SQL', and 'German' formats correctly. 79 80Note that you don't have to set this option if the PostgreSQL server has 81been set to use the 'ISO' format, which is the default. 82 83=back 84 85=cut 86 87sub _add_param 88{ 89 my ($to,%param) = @_; 90 foreach(keys %param) 91 { 92 if($_ eq 'european') { 93 $$to{'_european'} = $param{$_}; 94 } elsif($_ eq 'server_tz') { 95 $$to{'_server_tz'} = $param{$_}; 96 } else { 97 croak("Unknown option $_." ); 98 } 99 } 100} 101 102sub european { 103 my ($self,%param) = @_; 104 return $param{'european'} if exists $param{'european'}; 105 return $self->{'_european'} if ref $self; 106} 107 108sub server_tz { 109 my ($self,%param) = @_; 110 return $param{''} if (ref($param{'server_tz'})) =~ /TimeZone/; 111 return DateTime::TimeZone->new('name' => $param{''}) if exists $param{'server_tz'}; 112 return ((ref $self) && $self->{'_server_tz'}); 113} 114 115sub new 116{ 117 my $class = shift; 118 my $self = bless {}, ref($class)||$class; 119 if (ref $class) 120 { 121 $self->{'_european'} = ( scalar $class->{'_european'} ); 122 } 123 _add_param($self,@_); 124 return $self; 125} 126 127=item * clone() 128 129This method is provided for those who prefer to explicitly clone via a 130method called C<clone()>. 131 132 my $clone = $original->clone(); 133 134If called as a class method it will die. 135 136=back 137 138=cut 139 140sub clone 141{ 142 my $self = shift; 143 croak('Calling object method as class method!') unless ref $self; 144 return $self->new(); 145} 146 147sub _create_infinity 148{ 149 my $self = shift; 150 my %p = @_; 151 152 if ($p{sign}) { 153 return DateTime::Infinite::Past->new; 154 } else { 155 return DateTime::Infinite::Future->new; 156 } 157} 158 159# infinite datetimes 160my $pg_infinity = 161{ 162 regex => qr/^(-)?infinity$/, 163 params => [ qw(sign) ], 164 constructor => \&_create_infinity, 165}; 166 167# Dates (without time zone) 168# 169# see EncodeDateOnly() in 170# pgsql-server/src/backend/utils/adt/datetime.c 171# 172# 2003-04-18 (USE_ISO_DATES) 173# 174my $pg_dateonly_iso = 175{ 176 regex => qr/^(\d{4,})-(\d{2,})-(\d{2,})( BC)?$/, 177 params => [ qw( year month day era ) ], 178 postprocess => \&_fix_era, 179}; 180 181# 18/04/2003 (USE_SQL_DATES, EuroDates) 182# 18-04-2003 (USE_POSTGRES_DATES, EuroDates) 183# 04/18/2003 (USE_SQL_DATES, !EuroDates) 184# 04-18-2003 (USE_POSTGRES_DATES, !EuroDates) 185# 186my $pg_dateonly_sql = 187{ 188 regex => qr/^(\d{2,})[\/-](\d{2,})[\/-](\d{4,})( BC)?$/, 189 params => [ qw( month day year era) ], 190 postprocess => [ \&_fix_era, \&_fix_eu ], 191}; 192 193# 18.04.2003 (USE_GERMAN_DATES) 194# 195my $pg_dateonly_german = 196{ 197 regex => qr/^(\d{2,})\.(\d{2,})\.(\d{4,})( BC)?$/, 198 params => [ qw( day month year era ) ], 199 postprocess => \&_fix_era 200}; 201 202# Times (with/without time zone) 203# 204# see EncodeTimeOnly() in 205# pgsql-server/src/backend/utils/adt/datetime.c 206# 207# 17:20:24.373942+02 208# (NB: always uses numerical tz) 209# 210my $pg_timeonly = 211{ 212 regex => qr/^T?(\d{2,}):(\d{2,}):(\d{2,})(\.\d+)? *([-\+][\d:]+)?$/, 213 params => [ qw( hour minute second nanosecond time_zone) ], 214 extra => { year => '1970' }, 215 postprocess => [ \&_fix_timezone, \&_fix_nanosecond ], 216}; 217 218# Timestamps (with/without time zone) 219# 220# see EncodeDateTime() in 221# pgsql-server/src/backend/utils/adt/datetime.c 222# 223# 2003-04-18 17:20:24.373942+02 (USE_ISO_DATES) 224# (NB: always uses numerical tz) 225my $pg_datetime_iso = 226{ 227 regex => 228 qr/^ 229 (\d{4,})-(\d{2,})-(\d{2,}) # date part 230 [ T] # separator 231 (\d{2,}):(\d{2,}):(\d{2,})(\.\d+)? # time part 232 [ ]* 233 ([-\+][\d:]+)? # numerical timezone 234 ([ ]BC)? 235 $/x, 236 params => [ qw( year month day hour minute second nanosecond time_zone era) ], 237 postprocess => [ \&_fix_era, \&_fix_timezone, \&_fix_nanosecond ], 238}; 239 240# * Added for https://github.com/lestrrat-p5/DateTime-Format-Pg/issues/18 241# Concatenated dates/times are accepted 242# e.g. YYYYMMDDTHHMMSS 243my $pg_datetime_iso_concat_date = 244{ 245 regex => 246 qr/^ 247 (\d{4})(\d{2})(\d{2}) # concatenated date 248 [ T] # separator 249 (\d{2,}):(\d{2,}):(\d{2,})(\.\d+)? # time part 250 [ ]* 251 ([-\+][\d:]+)? # numerical timezone 252 ([ ]BC)? 253 $/x, 254 params => [ qw( year month day hour minute second nanosecond time_zone era) ], 255 postprocess => [ \&_fix_era, \&_fix_timezone, \&_fix_nanosecond ], 256}; 257 258# Fri 18 Apr 17:20:24.373942 2003 CEST (USE_POSTGRES_DATES, EuroDates) 259# 260my $pg_datetime_pg_eu = 261{ 262 regex => qr/^\S{3,} (\d{2,}) (\S{3,}) (\d{2,}):(\d{2,}):(\d{2,})(\.\d+)? (\d{4,}) *((?:[-\+][\d:]+)|(?:\S+))?( BC)?$/, 263 params => [ qw( day month hour minute second nanosecond year time_zone era ) ], 264 postprocess => [ \&_fix_era, \&_fix_timezone, \&_fix_nanosecond ], 265}; 266 267# Fri Apr 18 17:20:24.373942 2003 CEST (USE_POSTGRES_DATES, !EuroDates) 268# 269my $pg_datetime_pg_us = 270{ 271 regex => qr/^\S{3,} (\S{3,}) (\s{2,}) (\d{2,}):(\d{2,}):(\d{2,})(\.\d+)? (\d{4,}) *((?:[-\+][\d:]+)|(?:\S+))?( BC)?$/, 272 params => [ qw( month day hour minute second nanosecond year time_zone era ) ], 273 postprocess => [ \&_fix_era, \&_fix_month_names, \&_fix_timezone, \&_fix_nanosecond ], 274}; 275 276# 18/04/2003 17:20:24.373942 CEST (USE_SQL_DATES, EuroDates) 277# 04/18/2003 17:20:24.373942 CEST (USE_SQL_DATES, !EuroDates) 278# 279my $pg_datetime_sql = 280{ 281 regex => qr/^(\d{2,})\/(\d{2,})\/(\d{4,}) (\d{2,}):(\d{2,}):(\d{2,})(\.\d+)? *((?:[-\+][\d:]+)|(?:\S+))?( BC)?$/, 282 params => [ qw( month day year hour minute second nanosecond time_zone era ) ], 283 postprocess => [ \&_fix_era, \&_fix_eu, \&_fix_timezone, \&_fix_nanosecond ], 284}; 285 286# 18.04.2003 17:20:24.373942 CEST (USE_GERMAN_DATES) 287# 288my $pg_datetime_german = 289{ 290 regex => qr/^(\d{2,})\.(\d{2,})\.(\d{4,}) (\d{2,}):(\d{2,}):(\d{2,})(\.\d+)? *((?:[-\+][\d:]+)|(?:\S+))?( BC)?$/, 291 params => [ qw( day month year hour minute second nanosecond time_zone era ) ], 292 postprocess => [ \&_fix_era, \&_fix_timezone, \&_fix_nanosecond ], 293}; 294 295# Helper functions 296# 297# Fix BC dates (1 BC => year 0, 2 BC => year -1) 298# 299sub _fix_era { 300 my %args = @_; 301 my $era = (delete $args{'parsed'}->{'era'}) || ''; 302 if ($era =~ m/BC/) { 303 $args{'parsed'}->{'year'} = 1-$args{'parsed'}->{'year'} 304 } 305 return 1; 306} 307 308# Fix European dates (swap month and day) 309# 310sub _fix_eu { 311 my %args = @_; 312 if($args{'self'}->european(@{$args{'args'}}) ) { 313 my $save = $args{'parsed'}->{'month'}; 314 $args{'parsed'}->{'month'} = $args{'parsed'}->{'day'}; 315 $args{'parsed'}->{'day'} = $save; 316 } 317 return 1; 318} 319 320# Fix month names (name => numeric) 321# 322my %months = ( 323 'jan' => 1, 'feb' => 2, 'mar' => 3, 'apr' => 4, 324 'may' => 5, 'jun' => 6, 'jul' => 7, 'aug' => 8, 325 'sep' => 9, 'oct' =>10, 'nov' =>11, 'dec' =>12, ); 326 327sub _fix_month_names { 328 my %args = @_; 329 $args{'parsed'}->{'month'} = $months{lc( $args{'parsed'}->{'month'} )}; 330 return $args{'parsed'}->{'month'} ? 1 : undef; 331} 332 333# Fix time zones 334# 335sub _fix_timezone { 336 my %args = @_; 337 my %param = $args{'args'} ? (@{$args{'args'}}) : (); 338 339 if($param{'_force_tz'}) { 340 $args{'parsed'}->{'time_zone'} = $param{'_force_tz'}; 341 } 342 343 elsif(!defined($args{'parsed'}->{'time_zone'})) { 344 # For very early and late dates, PostgreSQL always returns times in 345 # UTC and does not tell us that it did so. 346 # 347 if ( $args{'parsed'}->{'year'} < 1901 348 || ( $args{'parsed'}->{'year'} == 1901 && ($args{'parsed'}->{'month'} < 12 || $args{'parsed'}->{'day'} < 14) ) 349 || $args{'parsed'}->{'year'} > 2038 350 || ( $args{'parsed'}->{'year'} == 2038 && ($args{'parsed'}->{'month'} > 01 || $args{'parsed'}->{'day'} > 18) ) 351 ) { 352 $args{'parsed'}->{'time_zone'} = DateTime::TimeZone::UTC->new(); 353 } 354 355 # DT->new() does not like undef time_zone params, which are generated 356 # by the regexps 357 # 358 else { 359 delete $args{'parsed'}->{'time_zone'}; 360 } 361 } 362 363 # Numerical time zone 364 # 365 366 elsif($args{'parsed'}->{'time_zone'} =~ m/^([-\+])(\d+)(?::(\d+))?(?::(\d+))?$/) { 367 my $tz; 368 if (length($2) == 2) { 369 # regular hour notation 370 my ($min, $sec) = ($3 || '00', $4 || '00'); 371 $tz = sprintf "%s%02d:%02d:%02d", $1, $2, $min, $sec; 372 } else { 373 $tz = "$1$2"; 374 } 375 $args{'parsed'}->{'time_zone'} = $tz; 376 } 377 378 # Non-numerical time zone returned, which can be ambiguous :( 379 # 380 else 381 { 382 # XXX This barfs because 'self' may not necessarily be initialized 383 # Need to fix it 384 my $stz = $args{'self'}->server_tz($args{'args'} ? @{$args{'args'}} : ()); 385 $args{'parsed'}->{'time_zone'} = $stz || 'floating'; 386 } 387 388 return 1; 389} 390 391# Fix fractional seconds 392# 393sub _fix_nanosecond { 394 my %args = @_; 395 if(defined $args{'parsed'}->{'nanosecond'}) { 396 $args{'parsed'}->{'nanosecond'} = sprintf '%.0f', $args{'parsed'}->{'nanosecond'} * 1.0E9; 397 } else { 398 delete $args{'parsed'}->{'nanosecond'} 399 }; 400 return 1; 401} 402 403# Parser generation 404# 405DateTime::Format::Builder->create_class( 406 parsers => { 407 parse_date => [ 408 $pg_dateonly_iso, 409 $pg_dateonly_sql, 410 $pg_dateonly_german, 411 $pg_infinity, 412 ], 413 parse_timetz => [ 414 $pg_timeonly, 415 ], 416 parse_timestamptz => [ 417 $pg_datetime_iso, 418 $pg_datetime_iso_concat_date, 419 $pg_datetime_pg_eu, 420 $pg_datetime_pg_us, 421 $pg_datetime_sql, 422 $pg_datetime_german, 423 $pg_infinity, 424 ], 425 parse_datetime => [ 426 $pg_datetime_iso, 427 $pg_datetime_iso_concat_date, 428 $pg_datetime_pg_eu, 429 $pg_datetime_pg_us, 430 $pg_datetime_sql, 431 $pg_datetime_german, 432 $pg_dateonly_iso, 433 $pg_dateonly_german, 434 $pg_dateonly_sql, 435 $pg_timeonly, 436 $pg_infinity, 437 ], 438 } 439); 440 441=head1 METHODS 442 443This class provides the following methods. The parse_datetime, parse_duration, 444format_datetime, and format_duration methods are general-purpose methods 445provided for compatibility with other C<DateTime::Format> modules. 446 447The other methods are specific to the corresponding PostgreSQL date/time data 448types. The names of these methods are derived from the name of the PostgreSQL 449data type. (Note: Prior to PostgreSQL 7.3, the TIMESTAMP type was equivalent 450to the TIMESTAMP WITH TIME ZONE type. This data type corresponds to the 451format/parse_timestamp_with_time_zone method but not to the 452format/parse_timestamp method.) 453 454=head2 PARSING METHODS 455 456This class provides the following parsing methods. 457 458As a general rule, the parsing methods accept input in any format that the 459PostgreSQL server can produce. However, if PostgreSQL's DateStyle is set to 460'SQL' or 'PostgreSQL', dates can only be parsed correctly if the 'european' 461option is set correctly (i.e. same as the PostgreSQL server). The same is true 462for time zones and the 'australian_timezones' option in all modes but 'ISO'. 463 464The default DateStyle, 'ISO', will always produce unambiguous results 465and is also parsed most efficiently by this parser class. I strongly 466recommend using this setting unless you have a good reason not to. 467 468=over 4 469 470=item * parse_datetime($string,...) 471 472Given a string containing a date and/or time representation, this method 473will return a new C<DateTime> object. 474 475If the input string does not contain a date, it is set to 1970-01-01. 476If the input string does not contain a time, it is set to 00:00:00. 477If the input string does not contain a time zone, it is set to the 478floating time zone. 479 480If given an improperly formatted string, this method may die. 481 482=cut 483 484# sub parse_datetime { 485# *** created automatically *** 486# } 487 488=item * parse_timestamptz($string,...) 489 490=item * parse_timestamp_with_time_zone($string,...) 491 492Given a string containing a timestamp (date and time) representation, 493this method will return a new C<DateTime> object. This method is 494suitable for the TIMESTAMPTZ (or TIMESTAMP WITH TIME ZONE) type. 495 496If the input string does not contain a time zone, it is set to the 497floating time zone. 498 499Please note that PostgreSQL does not actually store a time zone along 500with the TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ) type but will just 501return a time stamp converted for the server's local time zone. 502 503If given an improperly formatted string, this method may die. 504 505=cut 506 507# sub parse_timestamptz { 508# *** created automatically *** 509# } 510 511*parse_timestamp_with_time_zone = \&parse_timestamptz; 512 513=item * parse_timestamp($string,...) 514 515=item * parse_timestamp_without_time_zone($string,...) 516 517Similar to the functions above, but always returns a C<DateTime> object 518with a floating time zone. This method is suitable for the TIMESTAMP (or 519TIMESTAMP WITHOUT TIME ZONE) type. 520 521If the server does return a time zone, it is ignored. 522 523If given an improperly formatted string, this method may die. 524 525=cut 526 527sub parse_timestamp { 528 parse_timestamptz(@_,'_force_tz' => DateTime::TimeZone::Floating->new()); 529} 530 531*parse_timestamp_without_time_zone = \&parse_timestamp; 532 533=item * parse_timetz($string,...) 534 535=item * parse_time_with_time_zone($string,...) 536 537Given a string containing a time representation, this method will return 538a new C<DateTime> object. The date is set to 1970-01-01. This method is 539suitable for the TIMETZ (or TIME WITH TIME ZONE) type. 540 541If the input string does not contain a time zone, it is set to the 542floating time zone. 543 544Please note that PostgreSQL stores a numerical offset with its TIME WITH 545TIME ZONE (or TIMETZ) type. It does not store a time zone name (such as 546'Europe/Rome'). 547 548If given an improperly formatted string, this method may die. 549 550=cut 551 552# sub parse_timetz { 553# *** created automatically *** 554# } 555 556*parse_time_with_time_zone = \&parse_timetz; 557 558=item * parse_time($string,...) 559 560=item * parse_time_without_time_zone($string,...) 561 562Similar to the functions above, but always returns an C<DateTime> object 563with a floating time zone. If the server returns a time zone, it is 564ignored. This method is suitable for use with the TIME (or TIME WITHOUT 565TIME ZONE) type. 566 567This ensures that the resulting C<DateTime> object will always have the 568time zone expected by your application. 569 570If given an improperly formatted string, this method may die. 571 572=cut 573 574sub parse_time { 575 parse_timetz(@_,'_force_tz' => 'floating'); 576} 577 578*parse_time_without_time_zone = \&parse_time; 579 580=item * parse_date($string,...) 581 582Given a string containing a date representation, this method will return 583a new C<DateTime> object. The time is set to 00:00:00 (floating time 584zone). This method is suitable for the DATE type. 585 586If given an improperly formatted string, this method may die. 587 588=cut 589 590# sub parse_date { 591# *** generated automatically *** 592# } 593 594=item * parse_duration($string) 595 596=item * parse_interval($string) 597 598Given a string containing a duration (SQL type INTERVAL) representation, 599this method will return a new C<DateTime::Duration> object. 600 601If given an improperly formatted string, this method may die. 602 603=cut 604 605sub parse_duration { 606 my ($self, $string_to_parse) = @_; 607 608 # NB: We can't just pass our values to new() because it treats all 609 # arguments as negative if we have a single negative component. 610 # PostgreSQL might return mixed signs, e.g. '1 mon -1day'. 611 my $du = DateTime::Duration->new; 612 613 my %units = ( map(($_, ["seconds", 1]), qw(s second seconds sec secs)), 614 map(($_, ["minutes", 1]), qw(m minute minutes min mins)), 615 map(($_, ["hours", 1]), qw(h hr hour hours)), 616 map(($_, ["days", 1]), qw(d day days)), 617 map(($_, ["weeks", 1]), qw(w week weeks)), 618 map(($_, ["months", 1]), qw(M mon mons month months)), 619 map(($_, ["years", 1]), qw(y yr yrs year years)), 620 map(($_, ["years", 10]), qw(decade decades dec decs)), 621 map(($_, ["years", 100]), qw(c cent century centuries)), 622 map(($_, ["years", 1000]), qw(millennium millennia millenniums mil mils)) ); 623 624 (my $string = $string_to_parse) =~ s/^@\s*//; 625 $string =~ s/\+(\d+)/$1/g; 626 627 # Method used later on duration object 628 my $arith_method = "add"; 629 if ( $string =~ s/ago// ) { 630 $arith_method = "subtract"; 631 } 632 633 my $sign = 0; 634 my %done; 635 636 $string =~ s/\b(\d+):(\d\d):(\d\d)(\.\d+)?\b/$1h $2m $3$4s/g; 637 $string =~ s/\b(\d+):(\d\d)\b/$1h $2m/g; 638 $string =~ s/(-\d+h)\s+(\d+m)\s+(\d+(?:\.\d+)?s)\s*/$1 -$2 -$3 /; 639 $string =~ s/(-\d+h)\s+(\d+m)\s*/$1 -$2 /; 640 641 while ($string =~ s/^\s*(-?\d+(?:[.,]\d+)?)\s*([a-zA-Z]+)(?:\s*(?:,|and)\s*)*//i) { 642 my($amount, $unit) = ($1, $2); 643 if (length($unit) != 1) { 644 $unit = lc($unit); 645 } 646 647 my $udata = $units{$unit}; 648 if (! $udata) { 649 Carp::croak("Unknown timespec: $string_to_parse"); 650 } 651 my ($base_unit, $num) = @$udata; 652 my $key = $base_unit . "-" . $num; 653 if (exists $done{$key}) { 654 Carp::croak("Unknown timespec: $string_to_parse"); 655 } 656 $done{$key} = 1; 657 658 my @extra_args; 659 660 $amount =~ s/,/./; 661 if ($amount =~ s/\.(\d+)$//) { 662 my $fractional = $1; 663 # We only handle fractional seconds right now. If you 664 # need support for silly formats (from my perspective ;-P) 665 # like '1.5 weeks', please provide me with a comprehensive 666 # test for all possible combinations of fractional times. 667 if ($base_unit ne "seconds") { 668 Carp::croak("Fractional input detected: currently only fractional seconds are supported") 669 } 670 671 # From the spec, Pg can take up to 6 digits for fractional part 672 # that is microseconds. If we're missing 0's, 673 # we should pad them 674 $fractional .= '0'x (9 - length($fractional)); 675 my $sign = ($amount > 0) ? 1 : -1; 676 push @extra_args, ("nanoseconds" => $sign * $fractional); 677 } 678 679 $du->$arith_method($base_unit => $amount * $num, @extra_args); 680 } 681 682 if ($string =~ /\S/) { # OK to have extra spaces, but nothing else 683 Carp::croak "Unknown timespec: $string_to_parse"; 684 } 685 686 return $du; 687} 688 689*parse_interval = \&parse_duration; 690 691=back 692 693=head2 FORMATTING METHODS 694 695This class provides the following formatting methods. 696 697The output is always in the format mandated by the SQL standard (derived 698from ISO 8601), which is parsed by PostgreSQL unambiguously in all 699DateStyle modes. 700 701=over 4 702 703=item * format_datetime($datetime,...) 704 705Given a C<DateTime> object, this method returns a string appropriate as 706input for all date and date/time types of PostgreSQL. It will contain 707date and time. 708 709If the time zone of the C<DateTime> part is floating, the resulting 710string will contain no time zone, which will result in the server's time 711zone being used. Otherwise, the numerical offset of the time zone is 712used. 713 714=cut 715 716*format_datetime = \&format_timestamptz; 717 718=item * format_time($datetime,...) 719 720=item * format_time_without_time_zone($datetime,...) 721 722Given a C<DateTime> object, this method returns a string appropriate as 723input for the TIME type (also known as TIME WITHOUT TIME ZONE), which 724will contain the local time of the C<DateTime> object and no time zone. 725 726=cut 727 728sub _format_fractional 729{ 730 my $ns = shift->nanosecond; 731 return $ns ? sprintf(".%09d", "$ns") : '' 732} 733 734sub format_time 735{ 736 my ($self,$dt,%param) = @_; 737 return $dt->hms(':')._format_fractional($dt); 738} 739 740*format_time_without_time_zone = \&format_time; 741 742=item * format_timetz($datetime) 743 744=item * format_time_with_time_zone($datetime) 745 746Given a C<DateTime> object, this method returns a string appropriate as 747input for the TIME WITH TIME ZONE type (also known as TIMETZ), which 748will contain the local part of the C<DateTime> object and a numerical 749time zone. 750 751You should not use the TIME WITH TIME ZONE type to store dates with 752floating time zones. If the time zone of the C<DateTime> part is 753floating, the resulting string will contain no time zone, which will 754result in the server's time zone being used. 755 756=cut 757 758sub _format_time_zone 759{ 760 my $dt = shift; 761 return '' if $dt->time_zone->is_floating; 762 return &DateTime::TimeZone::offset_as_string($dt->offset); 763} 764 765sub format_timetz 766{ 767 my ($self,$dt) = @_; 768 return $dt->hms(':')._format_fractional($dt)._format_time_zone($dt); 769} 770 771*format_time_with_time_zone = \&format_timetz; 772 773=item * format_date($datetime) 774 775Given a C<DateTime> object, this method returns a string appropriate as 776input for the DATE type, which will contain the date part of the 777C<DateTime> object. 778 779=cut 780 781sub format_date 782{ 783 my ($self,$dt) = @_; 784 if($dt->is_infinite) { 785 return $dt->isa('DateTime::Infinite::Future') ? 'infinity' : '-infinity'; 786 } elsif($dt->year()<=0) { 787 return sprintf('%04d-%02d-%02d BC', 788 1-$dt->year(), 789 $dt->month(), 790 $dt->day()); 791 } else { 792 return $dt->ymd('-'); 793 } 794} 795 796=item * format_timestamp($datetime) 797 798=item * format_timestamp_without_time_zone($datetime) 799 800Given a C<DateTime> object, this method returns a string appropriate as 801input for the TIMESTAMP type (also known as TIMESTAMP WITHOUT TIME 802ZONE), which will contain the local time of the C<DateTime> object and 803no time zone. 804 805=cut 806 807sub format_timestamp 808{ 809 my ($self,$dt,%param) = @_; 810 if($dt->is_infinite) { 811 return $dt->isa('DateTime::Infinite::Future') ? 'infinity' : '-infinity'; 812 } elsif($dt->year()<=0) { 813 return sprintf('%04d-%02d-%02d %s BC', 814 1-$dt->year(), 815 $dt->month(), 816 $dt->day(), 817 $dt->hms(':')._format_fractional($dt)); 818 } else { 819 return $dt->ymd('-').' '.$dt->hms(':')._format_fractional($dt); 820 } 821} 822 823*format_timestamp_without_time_zone = \&format_timestamp; 824 825=item * format_timestamptz($datetime) 826 827=item * format_timestamp_with_time_zone($datetime) 828 829Given a C<DateTime> object, this method returns a string appropriate as 830input for the TIMESTAMP WITH TIME ZONE type, which will contain the 831local part of the C<DateTime> object and a numerical time zone. 832 833You should not use the TIMESTAMP WITH TIME ZONE type to store dates with 834floating time zones. If the time zone of the C<DateTime> part is 835floating, the resulting string will contain no time zone, which will 836result in the server's time zone being used. 837 838=cut 839 840sub format_timestamptz 841{ 842 my ($self,$dt,%param) = @_; 843 if($dt->is_infinite) { 844 return $dt->isa('DateTime::Infinite::Future') ? 'infinity' : '-infinity'; 845 } elsif($dt->year()<=0) { 846 return sprintf('%04d-%02d-%02d', 847 1-$dt->year(), 848 $dt->month(), 849 $dt->day()). 850 ' '. 851 $dt->hms(':'). 852 _format_fractional($dt). 853 _format_time_zone($dt). 854 ' BC'; 855 } else { 856 return $dt->ymd('-').' '.$dt->hms(':'). 857 _format_fractional($dt). 858 _format_time_zone($dt); 859 } 860} 861 862*format_timestamp_with_time_zone = \&format_timestamptz; 863 864=item * format_duration($du) 865 866=item * format_interval($du) 867 868Given a C<DateTime::Duration> object, this method returns a string appropriate 869as input for the INTERVAL type. 870 871=cut 872 873sub format_duration { 874 shift if UNIVERSAL::isa($_[0], __PACKAGE__) || $_[0] eq __PACKAGE__; 875 my($du,%param) = @_; 876 croak 'DateTime::Duration object expected' unless UNIVERSAL::isa($du,'DateTime::Duration'); 877 878 my %deltas = $du->deltas(); 879 my $output = '@'; 880 881 if($deltas{'nanoseconds'}) { 882 $deltas{'seconds'} = 883 sprintf('%f', $deltas{'seconds'} + $deltas{'nanoseconds'} / 884 DateTime::Duration::MAX_NANOSECONDS); 885 } 886 887 foreach(qw(months days minutes seconds)) { 888 $output .= ' '.$deltas{$_}.' '.$_ if $deltas{$_}; 889 } 890 891 $output .= ' 0' if(length($output)<=2); 892 return $output; 893} 894 895*format_interval = \&format_duration; 896 897=back 898 899=cut 900 901 902 9031; 904 905__END__ 906 907=head1 LIMITATIONS 908 909Some output formats of PostgreSQL have limitations that can only be passed on 910by this class. 911 912As a general rules, none of these limitations apply to the 'ISO' output 913format. It is strongly recommended to use this format (and to use 914PostgreSQL's to_char function when another output format that's not 915supposed to be handled by a parser of this class is desired). 'ISO' is 916the default but you are advised to explicitly set it at the beginning of 917the session by issuing a SET DATESTYLE TO 'ISO'; command in case the 918server administrator changes that setting. 919 920When formatting DateTime objects, this class always uses a format that's 921handled unambiguously by PostgreSQL. 922 923=head2 TIME ZONES 924 925If DateStyle is set to 'PostgreSQL', 'SQL', or 'German', PostgreSQL does 926not send numerical time zones for the TIMESTAMPTZ (or TIMESTAMP WITH 927TIME ZONE) type. Unfortunately, the time zone names used instead can be 928ambiguous: For example, 'EST' can mean -0500, +1000, or +1100. 929 930You must set the 'server_tz' variable to a time zone that is identical to that 931of the PostgreSQL server. If the server is set to a different time zone (or the 932underlying operating system interprets the time zone differently), the parser 933will return wrong times. 934 935You can avoid such problems by setting the server's time zone to UTC 936using the SET TIME ZONE 'UTC' command and setting 'server_tz' parameter 937to 'UTC' (or by using the ISO output format, of course). 938 939=head2 EUROPEAN DATES 940 941For the SQL (for DATE and TIMSTAMP[TZ]) and the PostgreSQL (for DATE) 942output format, the server can send dates in both European-style 943'dd/mm/yyyy' and in US-style 'mm/dd/yyyy' format. In order to parse 944these dates correctly, you have to pass the 'european' option to the 945constructor or to the C<parse_xxx> routines. 946 947This problem does not occur when using the ISO or German output format 948(and for PostgreSQL with TIMESTAMP[TZ] as month names are used then). 949 950=head2 INTERVAL ELEMENTS 951 952C<DateTime::Duration> stores months, days, minutes and seconds 953separately. PostgreSQL only stores months and seconds and disregards the 954irregular length of days due to DST switching and the irregular length 955of minutes due to leap seconds. Therefore, it is not possible to store 956C<DateTime::Duration> objects as SQL INTERVALs without the loss of some 957information. 958 959=head2 NEGATIVE INTERVALS 960 961In the SQL and German output formats, the server does not send an 962indication of the sign with intervals. This means that '1 month ago' and 963'1 month' are both returned as '1 mon'. 964 965This problem can only be avoided by using the 'ISO' or 'PostgreSQL' 966output format. 967 968=head1 SUPPORT 969 970Support for this module is provided via the datetime@perl.org email 971list. See http://lists.perl.org/ for more details. 972 973=head1 AUTHOR 974 975Daisuke Maki E<lt>daisuke@endeworks.jpE<gt> 976 977=head1 AUTHOR EMERITUS 978 979Claus A. Faerber <perl@faerber.muc.de> 980 981=head1 COPYRIGHT 982 983Copyright (c) 2003 Claus A. Faerber. Copyright (c) 2005-2007 Daisuke Maki 984 985This program is free software; you can redistribute it and/or modify it under 986the same terms as Perl itself. 987 988The full text of the license can be found in the LICENSE file included with 989this module. 990 991=head1 SEE ALSO 992 993datetime@perl.org mailing list 994 995http://datetime.perl.org/ 996 997=cut 998