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.16007'; 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-16T23:12:01+0200 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/^(\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) 225# 226my $pg_datetime_iso = 227{ 228 regex => qr/^(\d{4,})-(\d{2,})-(\d{2,}) (\d{2,}):(\d{2,}):(\d{2,})(\.\d+)? *([-\+][\d:]+)?( BC)?$/, 229 params => [ qw( year month day hour minute second nanosecond time_zone era) ], 230 postprocess => [ \&_fix_era, \&_fix_timezone, \&_fix_nanosecond ], 231}; 232 233# Fri 18 Apr 17:20:24.373942 2003 CEST (USE_POSTGRES_DATES, EuroDates) 234# 235my $pg_datetime_pg_eu = 236{ 237 regex => qr/^\S{3,} (\d{2,}) (\S{3,}) (\d{2,}):(\d{2,}):(\d{2,})(\.\d+)? (\d{4,}) *((?:[-\+][\d:]+)|(?:\S+))?( BC)?$/, 238 params => [ qw( day month hour minute second nanosecond year time_zone era ) ], 239 postprocess => [ \&_fix_era, \&_fix_timezone, \&_fix_nanosecond ], 240}; 241 242# Fri Apr 18 17:20:24.373942 2003 CEST (USE_POSTGRES_DATES, !EuroDates) 243# 244my $pg_datetime_pg_us = 245{ 246 regex => qr/^\S{3,} (\S{3,}) (\s{2,}) (\d{2,}):(\d{2,}):(\d{2,})(\.\d+)? (\d{4,}) *((?:[-\+][\d:]+)|(?:\S+))?( BC)?$/, 247 params => [ qw( month day hour minute second nanosecond year time_zone era ) ], 248 postprocess => [ \&_fix_era, \&_fix_month_names, \&_fix_timezone, \&_fix_nanosecond ], 249}; 250 251# 18/04/2003 17:20:24.373942 CEST (USE_SQL_DATES, EuroDates) 252# 04/18/2003 17:20:24.373942 CEST (USE_SQL_DATES, !EuroDates) 253# 254my $pg_datetime_sql = 255{ 256 regex => qr/^(\d{2,})\/(\d{2,})\/(\d{4,}) (\d{2,}):(\d{2,}):(\d{2,})(\.\d+)? *((?:[-\+][\d:]+)|(?:\S+))?( BC)?$/, 257 params => [ qw( month day year hour minute second nanosecond time_zone era ) ], 258 postprocess => [ \&_fix_era, \&_fix_eu, \&_fix_timezone, \&_fix_nanosecond ], 259}; 260 261# 18.04.2003 17:20:24.373942 CEST (USE_GERMAN_DATES) 262# 263my $pg_datetime_german = 264{ 265 regex => qr/^(\d{2,})\.(\d{2,})\.(\d{4,}) (\d{2,}):(\d{2,}):(\d{2,})(\.\d+)? *((?:[-\+][\d:]+)|(?:\S+))?( BC)?$/, 266 params => [ qw( day month year hour minute second nanosecond time_zone era ) ], 267 postprocess => [ \&_fix_era, \&_fix_timezone, \&_fix_nanosecond ], 268}; 269 270# Helper functions 271# 272# Fix BC dates (1 BC => year 0, 2 BC => year -1) 273# 274sub _fix_era { 275 my %args = @_; 276 my $era = (delete $args{'parsed'}->{'era'}) || ''; 277 if ($era =~ m/BC/) { 278 $args{'parsed'}->{'year'} = 1-$args{'parsed'}->{'year'} 279 } 280 return 1; 281} 282 283# Fix European dates (swap month and day) 284# 285sub _fix_eu { 286 my %args = @_; 287 if($args{'self'}->european(@{$args{'args'}}) ) { 288 my $save = $args{'parsed'}->{'month'}; 289 $args{'parsed'}->{'month'} = $args{'parsed'}->{'day'}; 290 $args{'parsed'}->{'day'} = $save; 291 } 292 return 1; 293} 294 295# Fix month names (name => numeric) 296# 297my %months = ( 298 'jan' => 1, 'feb' => 2, 'mar' => 3, 'apr' => 4, 299 'may' => 5, 'jun' => 6, 'jul' => 7, 'aug' => 8, 300 'sep' => 9, 'oct' =>10, 'nov' =>11, 'dec' =>12, ); 301 302sub _fix_month_names { 303 my %args = @_; 304 $args{'parsed'}->{'month'} = $months{lc( $args{'parsed'}->{'month'} )}; 305 return $args{'parsed'}->{'month'} ? 1 : undef; 306} 307 308# Fix time zones 309# 310sub _fix_timezone { 311 my %args = @_; 312 my %param = $args{'args'} ? (@{$args{'args'}}) : (); 313 314 if($param{'_force_tz'}) { 315 $args{'parsed'}->{'time_zone'} = $param{'_force_tz'}; 316 } 317 318 elsif(!defined($args{'parsed'}->{'time_zone'})) { 319 # For very early and late dates, PostgreSQL always returns times in 320 # UTC and does not tell us that it did so. 321 # 322 if ( $args{'parsed'}->{'year'} < 1901 323 || ( $args{'parsed'}->{'year'} == 1901 && ($args{'parsed'}->{'month'} < 12 || $args{'parsed'}->{'day'} < 14) ) 324 || $args{'parsed'}->{'year'} > 2038 325 || ( $args{'parsed'}->{'year'} == 2038 && ($args{'parsed'}->{'month'} > 01 || $args{'parsed'}->{'day'} > 18) ) 326 ) { 327 $args{'parsed'}->{'time_zone'} = DateTime::TimeZone::UTC->new(); 328 } 329 330 # DT->new() does not like undef time_zone params, which are generated 331 # by the regexps 332 # 333 else { 334 delete $args{'parsed'}->{'time_zone'}; 335 } 336 } 337 338 # Numerical time zone 339 # 340 341 elsif($args{'parsed'}->{'time_zone'} =~ m/^([-\+])(\d+)(?::(\d+))?(?::(\d+))?$/) { 342 my $tz; 343 if (length($2) == 2) { 344 # regular hour notation 345 my ($min, $sec) = ($3 || '00', $4 || '00'); 346 $tz = sprintf "%s%02d:%02d:%02d", $1, $2, $min, $sec; 347 } else { 348 $tz = "$1$2"; 349 } 350 $args{'parsed'}->{'time_zone'} = $tz; 351 } 352 353 # Non-numerical time zone returned, which can be ambiguous :( 354 # 355 else 356 { 357 # XXX This barfs because 'self' may not necessarily be initialized 358 # Need to fix it 359 my $stz = $args{'self'}->_server_tz($args{'args'} ? @{$args{'args'}} : ()); 360 $args{'parsed'}->{'time_zone'} = $stz || 'floating'; 361 } 362 363 return 1; 364} 365 366# Fix fractional seconds 367# 368sub _fix_nanosecond { 369 my %args = @_; 370 if(defined $args{'parsed'}->{'nanosecond'}) { 371 $args{'parsed'}->{'nanosecond'} *= 1.0E9; 372 } else { 373 delete $args{'parsed'}->{'nanosecond'} 374 }; 375 return 1; 376} 377 378# Parser generation 379# 380DateTime::Format::Builder->create_class 381( 382 parsers => 383 { 384 parse_date => [ $pg_dateonly_iso, $pg_dateonly_sql, 385 $pg_dateonly_german, $pg_infinity ], 386 parse_timetz => [ $pg_timeonly, ], 387 parse_timestamptz => [ $pg_datetime_iso, $pg_datetime_pg_eu, 388 $pg_datetime_pg_us, $pg_datetime_sql, 389 $pg_datetime_german, $pg_infinity ], 390 parse_datetime => [ $pg_datetime_iso, $pg_datetime_pg_eu, 391 $pg_datetime_pg_us, $pg_datetime_sql, 392 $pg_datetime_german, 393 $pg_dateonly_iso, $pg_dateonly_german, 394 $pg_dateonly_sql, $pg_timeonly, $pg_infinity], 395 } 396); 397 398=head1 METHODS 399 400This class provides the following methods. The parse_datetime, parse_duration, 401format_datetime, and format_duration methods are general-purpose methods 402provided for compatibility with other C<DateTime::Format> modules. 403 404The other methods are specific to the corresponding PostgreSQL date/time data 405types. The names of these methods are derived from the name of the PostgreSQL 406data type. (Note: Prior to PostgreSQL 7.3, the TIMESTAMP type was equivalent 407to the TIMESTAMP WITH TIME ZONE type. This data type corresponds to the 408format/parse_timestamp_with_time_zone method but not to the 409format/parse_timestamp method.) 410 411=head2 PARSING METHODS 412 413This class provides the following parsing methods. 414 415As a general rule, the parsing methods accept input in any format that the 416PostgreSQL server can produce. However, if PostgreSQL's DateStyle is set to 417'SQL' or 'PostgreSQL', dates can only be parsed correctly if the 'european' 418option is set correctly (i.e. same as the PostgreSQL server). The same is true 419for time zones and the 'australian_timezones' option in all modes but 'ISO'. 420 421The default DateStyle, 'ISO', will always produce unambiguous results 422and is also parsed most efficiently by this parser class. I stronlgly 423recommend using this setting unless you have a good reason not to. 424 425=over 4 426 427=item * parse_datetime($string,...) 428 429Given a string containing a date and/or time representation, this method 430will return a new C<DateTime> object. 431 432If the input string does not contain a date, it is set to 1970-01-01. 433If the input string does not contain a time, it is set to 00:00:00. 434If the input string does not contain a time zone, it is set to the 435floating time zone. 436 437If given an improperly formatted string, this method may die. 438 439=cut 440 441# sub parse_datetime { 442# *** created autmatically *** 443# } 444 445=item * parse_timestamptz($string,...) 446 447=item * parse_timestamp_with_time_zone($string,...) 448 449Given a string containing a timestamp (date and time) representation, 450this method will return a new C<DateTime> object. This method is 451suitable for the TIMESTAMPTZ (or TIMESTAMP WITH TIME ZONE) type. 452 453If the input string does not contain a time zone, it is set to the 454floating time zone. 455 456Please note that PostgreSQL does not actually store a time zone along 457with the TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ) type but will just 458return a time stamp converted for the server's local time zone. 459 460If given an improperly formatted string, this method may die. 461 462=cut 463 464# sub parse_timestamptz { 465# *** created autmatically *** 466# } 467 468*parse_timestamp_with_time_zone = \&parse_timestamptz; 469 470=item * parse_timestamp($string,...) 471 472=item * parse_timestamp_without_time_zone($string,...) 473 474Similar to the functions above, but always returns a C<DateTime> object 475with a floating time zone. This method is suitable for the TIMESTAMP (or 476TIMESTAMP WITHOUT TIME ZONE) type. 477 478If the server does return a time zone, it is ignored. 479 480If given an improperly formatted string, this method may die. 481 482=cut 483 484sub parse_timestamp { 485 parse_timestamptz(@_,'_force_tz' => DateTime::TimeZone::Floating->new()); 486} 487 488*parse_timestamp_without_time_zone = \&parse_timestamp; 489 490=item * parse_timetz($string,...) 491 492=item * parse_time_with_time_zone($string,...) 493 494Given a string containing a time representation, this method will return 495a new C<DateTime> object. The date is set to 1970-01-01. This method is 496suitable for the TIMETZ (or TIME WITH TIME ZONE) type. 497 498If the input string does not contain a time zone, it is set to the 499floating time zone. 500 501Please note that PostgreSQL stores a numerical offset with its TIME WITH 502TIME ZONE (or TIMETZ) type. It does not store a time zone name (such as 503'Europe/Rome'). 504 505If given an improperly formatted string, this method may die. 506 507=cut 508 509# sub parse_timetz { 510# *** created autmatically *** 511# } 512 513*parse_time_with_time_zone = \&parse_timetz; 514 515=item * parse_time($string,...) 516 517=item * parse_time_without_time_zone($string,...) 518 519Similar to the functions above, but always returns an C<DateTime> object 520with a floating time zone. If the server returns a time zone, it is 521ignored. This method is suitable for use with the TIME (or TIME WITHOUT 522TIME ZONE) type. 523 524This ensures that the resulting C<DateTime> object will always have the 525time zone expected by your application. 526 527If given an improperly formatted string, this method may die. 528 529=cut 530 531sub parse_time { 532 parse_timetz(@_,'_force_tz' => 'floating'); 533} 534 535*parse_time_without_time_zone = \&parse_time; 536 537=item * parse_date($string,...) 538 539Given a string containing a date representation, this method will return 540a new C<DateTime> object. The time is set to 00:00:00 (floating time 541zone). This method is suitable for the DATE type. 542 543If given an improperly formatted string, this method may die. 544 545=cut 546 547# sub parse_date { 548# *** generated autmatically *** 549# } 550 551=item * parse_duration($string) 552 553=item * parse_interval($string) 554 555Given a string containing a duration (SQL type INTERVAL) representation, 556this method will return a new C<DateTime::Duration> object. 557 558If given an improperly formatted string, this method may die. 559 560=cut 561 562sub parse_duration { 563 my ($self, $string) = @_; 564 my ($year, $mon, $day, $sgn, $hour, $min, $sec, $frc, $ago) = $string =~ m{ 565 \A # Start of string. 566 (?:\@\s*)? # Optional leading @. 567 (?:([-+]?\d+)\s+years?\s*)? # years 568 (?:([-+]?\d+)\s+mons?\s*)? # months 569 (?:([-+]?\d+)\s+days?\s*)? # days 570 (?: # Start h/m/s 571 # hours 572 (?:([-+])?([0-9]\d|[1-9]\d{2,}(?=:)|\d+(?=\s+hour))(?:\s+hours?)?\s*)? 573 # minutes 574 (?::?((?<=:)[012345]\d|\d+(?=\s+mins?))(?:\s+mins?)?\s*)? 575 # seconds 576 (?::?((?<=:)[012345]\d|\d+(?=\.|\s+secs?))(\.\d+)?(?:\s+secs?)?\s*)? 577 ?) # End hh:mm:ss 578 (ago)? # Optional inversion 579 \z # End of string 580 }xms or croak "Invalid interval string $string"; 581 582 # NB: We can't just pass our values to new() because it treats all 583 # arguments as negative if we have a single negative component. 584 # PostgreSQL might return mixed signs, e.g. '1 mon -1day'. 585 my $du = DateTime::Duration->new; 586 587 # Define for calculations 588 $_ ||= 0 for $sec, $frc, $min, $day, $mon; 589 590 # DT::Duration only stores years, days, months, seconds (and 591 # nanoseconds) 592 $mon += 12 * $year if $year; 593 $min += 60 * $hour if $hour; 594 595 # HH:MM:SS.FFFF share a single sign 596 if ($sgn && $sgn eq '-') { 597 $_ *= -1 for $min, $sec, $frc; 598 } 599 600 $du->add( 601 months => $mon, 602 days => $day, 603 minutes => $min, 604 seconds => $sec, 605 nanoseconds => $frc * DateTime::Duration::MAX_NANOSECONDS, 606 ); 607 return $ago ? $du->inverse : $du; 608} 609 610*parse_interval = \&parse_duration; 611 612=back 613 614=head2 FORMATTING METHODS 615 616This class provides the following formatting methods. 617 618The output is always in the format mandated by the SQL standard (derived 619from ISO 8601), which is parsed by PostgreSQL unambiguously in all 620DateStyle modes. 621 622=over 4 623 624=item * format_datetime($datetime,...) 625 626Given a C<DateTime> object, this method returns a string appropriate as 627input for all date and date/time types of PostgreSQL. It will contain 628date and time. 629 630If the time zone of the C<DateTime> part is floating, the resulting 631string will contain no time zone, which will result in the server's time 632zone being used. Otherwise, the numerical offset of the time zone is 633used. 634 635=cut 636 637*format_datetime = \&format_timestamptz; 638 639=item * format_time($datetime,...) 640 641=item * format_time_without_time_zone($datetime,...) 642 643Given a C<DateTime> object, this method returns a string appropriate as 644input for the TIME type (also known as TIME WITHOUT TIME ZONE), which 645will contain the local time of the C<DateTime> object and no time zone. 646 647=cut 648 649sub _format_fractional 650{ 651 my $ns = shift->nanosecond; 652 return $ns ? sprintf(".%09d", "$ns") : '' 653} 654 655sub format_time 656{ 657 my ($self,$dt,%param) = @_; 658 return $dt->hms(':')._format_fractional($dt); 659} 660 661*format_time_without_time_zone = \&format_time; 662 663=item * format_timetz($datetime) 664 665=item * format_time_with_time_zone($datetime) 666 667Given a C<DateTime> object, this method returns a string appropriate as 668input for the TIME WITH TIME ZONE type (also known as TIMETZ), which 669will contain the local part of the C<DateTime> object and a numerical 670time zone. 671 672You should not use the TIME WITH TIME ZONE type to store dates with 673floating time zones. If the time zone of the C<DateTime> part is 674floating, the resulting string will contain no time zone, which will 675result in the server's time zone being used. 676 677=cut 678 679sub _format_time_zone 680{ 681 my $dt = shift; 682 return '' if $dt->time_zone->is_floating; 683 return &DateTime::TimeZone::offset_as_string($dt->offset); 684} 685 686sub format_timetz 687{ 688 my ($self,$dt) = @_; 689 return $dt->hms(':')._format_fractional($dt)._format_time_zone($dt); 690} 691 692*format_time_with_time_zone = \&format_timetz; 693 694=item * format_date($datetime) 695 696Given a C<DateTime> object, this method returns a string appropriate as 697input for the DATE type, which will contain the date part of the 698C<DateTime> object. 699 700=cut 701 702sub format_date 703{ 704 my ($self,$dt) = @_; 705 if($dt->is_infinite) { 706 return $dt->isa('DateTime::Infinite::Future') ? 'infinity' : '-infinity'; 707 } elsif($dt->year()<=0) { 708 return sprintf('%04d-%02d-%02d BC', 709 1-$dt->year(), 710 $dt->month(), 711 $dt->day()); 712 } else { 713 return $dt->ymd('-'); 714 } 715} 716 717=item * format_timestamp($datetime) 718 719=item * format_timestamp_without_time_zone($datetime) 720 721Given a C<DateTime> object, this method returns a string appropriate as 722input for the TIMESTAMP type (also known as TIMESTAMP WITHOUT TIME 723ZONE), which will contain the local time of the C<DateTime> object and 724no time zone. 725 726=cut 727 728sub format_timestamp 729{ 730 my ($self,$dt,%param) = @_; 731 if($dt->is_infinite) { 732 return $dt->isa('DateTime::Infinite::Future') ? 'infinity' : '-infinity'; 733 } elsif($dt->year()<=0) { 734 return sprintf('%04d-%02d-%02d %s BC', 735 1-$dt->year(), 736 $dt->month(), 737 $dt->day(), 738 $dt->hms(':')._format_fractional($dt)); 739 } else { 740 return $dt->ymd('-').' '.$dt->hms(':')._format_fractional($dt); 741 } 742} 743 744*format_timestamp_without_time_zone = \&format_timestamp; 745 746=item * format_timestamptz($datetime) 747 748=item * format_timestamp_with_time_zone($datetime) 749 750Given a C<DateTime> object, this method returns a string appropriate as 751input for the TIMESTAMP WITH TIME ZONE type, which will contain the 752local part of the C<DateTime> object and a numerical time zone. 753 754You should not use the TIMESTAMP WITH TIME ZONE type to store dates with 755floating time zones. If the time zone of the C<DateTime> part is 756floating, the resulting string will contain no time zone, which will 757result in the server's time zone being used. 758 759=cut 760 761sub format_timestamptz 762{ 763 my ($self,$dt,%param) = @_; 764 if($dt->is_infinite) { 765 return $dt->isa('DateTime::Infinite::Future') ? 'infinity' : '-infinity'; 766 } elsif($dt->year()<=0) { 767 return sprintf('%04d-%02d-%02d', 768 1-$dt->year(), 769 $dt->month(), 770 $dt->day()). 771 ' '. 772 $dt->hms(':'). 773 _format_fractional($dt). 774 _format_time_zone($dt). 775 ' BC'; 776 } else { 777 return $dt->ymd('-').' '.$dt->hms(':'). 778 _format_fractional($dt). 779 _format_time_zone($dt); 780 } 781} 782 783*format_timestamp_with_time_zone = \&format_timestamptz; 784 785=item * format_duration($du) 786 787=item * format_interval($du) 788 789Given a C<DateTime::Duration> object, this method returns a string appropriate 790as input for the INTERVAL type. 791 792=cut 793 794sub format_duration { 795 shift if UNIVERSAL::isa($_[0], __PACKAGE__) || $_[0] eq __PACKAGE__; 796 my($du,%param) = @_; 797 croak 'DateTime::Duration object expected' unless UNIVERSAL::isa($du,'DateTime::Duration'); 798 799 my %deltas = $du->deltas(); 800 my $output = '@'; 801 802 if($deltas{'nanoseconds'}) { 803 $deltas{'seconds'} = 804 sprintf('%f', $deltas{'seconds'} + $deltas{'nanoseconds'} / 805 DateTime::Duration::MAX_NANOSECONDS); 806 } 807 808 foreach(qw(months days minutes seconds)) { 809 $output .= ' '.$deltas{$_}.' '.$_ if $deltas{$_}; 810 } 811 812 $output .= ' 0' if(length($output)<=2); 813 return $output; 814} 815 816*format_interval = \&format_duration; 817 818=back 819 820=cut 821 822 823 8241; 825 826__END__ 827 828=head1 LIMITATIONS 829 830Some output formats of PostgreSQL have limitations that can only be passed on 831by this class. 832 833As a general rules, none of these limitations apply to the 'ISO' output 834format. It is strongly recommended to use this format (and to use 835PostgreSQL's to_char function when another output format that's not 836supposed to be handled by a parser of this class is desired). 'ISO' is 837the default but you are advised to explicitly set it at the beginnig of 838the session by issuing a SET DATESTYLE TO 'ISO'; command in case the 839server administrator changes that setting. 840 841When formatting DateTime objects, this class always uses a format that's 842handled unambiguously by PostgreSQL. 843 844=head2 TIME ZONES 845 846If DateStyle is set to 'PostgreSQL', 'SQL', or 'German', PostgreSQL does 847not send numerical time zones for the TIMESTAMPTZ (or TIMESTAMP WITH 848TIME ZONE) type. Unfortunatly, the time zone names used instead can be 849ambiguous: For example, 'EST' can mean -0500, +1000, or +1100. 850 851You must set the 'server_tz' variable to a time zone that is identical to that 852of the PostgreSQL server. If the server is set to a different time zone (or the 853underlying operating system interprets the time zone differently), the parser 854will return wrong times. 855 856You can avoid such problems by setting the server's time zone to UTC 857using the SET TIME ZONE 'UTC' command and setting 'server_tz' parameter 858to 'UTC' (or by using the ISO output format, of course). 859 860=head2 EUROPEAN DATES 861 862For the SQL (for DATE and TIMSTAMP[TZ]) and the PostgreSQL (for DATE) 863output format, the server can send dates in both European-style 864'dd/mm/yyyy' and in US-style 'mm/dd/yyyy' format. In order to parse 865these dates correctly, you have to pass the 'european' option to the 866constructor or to the C<parse_xxx> routines. 867 868This problem does not occur when using the ISO or German output format 869(and for PostgreSQL with TIMESTAMP[TZ] as month names are used then). 870 871=head2 INTERVAL ELEMENTS 872 873C<DateTime::Duration> stores months, days, minutes and seconds 874separately. PostgreSQL only stores months and seconds and disregards the 875irregular length of days due to DST switching and the irregular length 876of minutes due to leap seconds. Therefore, it is not possitble to store 877C<DateTime::Duration> objects as SQL INTERVALs without the loss of some 878information. 879 880=head2 NEGATIVE INTERVALS 881 882In the SQL and German output formats, the server does not send an 883indication of the sign with intervals. This means that '1 month ago' and 884'1 month' are both returned as '1 mon'. 885 886This problem can only be avoided by using the 'ISO' or 'PostgreSQL' 887output format. 888 889=head1 SUPPORT 890 891Support for this module is provided via the datetime@perl.org email 892list. See http://lists.perl.org/ for more details. 893 894=head1 AUTHOR 895 896Daisuke Maki E<lt>daisuke@endeworks.jpE<gt> 897 898=head1 AUTHOR EMERITUS 899 900Claus A. Faerber <perl@faerber.muc.de> 901 902=head1 COPYRIGHT 903 904Copyright (c) 2003 Claus A. Faerber. Copyright (c) 2005-2007 Daisuke Maki 905 906This program is free software; you can redistribute it and/or modify it under 907the same terms as Perl itself. 908 909The full text of the license can be found in the LICENSE file included with 910this module. 911 912=head1 SEE ALSO 913 914datetime@perl.org mailing list 915 916http://datetime.perl.org/ 917 918=cut 919