1package DBIx::Class::Helper::ResultSet::DateMethods1; 2$DBIx::Class::Helper::ResultSet::DateMethods1::VERSION = '2.036000'; 3# ABSTRACT: Work with dates in your RDBMS nicely 4 5use parent 'DBIx::Class::ResultSet'; 6 7use strict; 8use warnings; 9 10use DBI qw(:sql_types); 11use DBIx::Introspector; 12use Safe::Isa; 13 14sub _flatten_thing { 15 my ($self, $thing) = @_; 16 17 die 'you dummy' unless defined $thing; 18 my $ref = ref $thing; 19 20 return ('?', $thing) if !$ref; 21 22 if ($ref eq 'HASH' && exists $thing->{'-ident'}) { 23 my $thing = $thing->{'-ident'}; 24 $thing = $self->current_source_alias . $thing if $thing =~ m/^\./; 25 return $self->result_source->storage->sql_maker->_quote($thing) 26 } 27 28 return ${$thing} if $ref eq 'SCALAR'; 29 30 # FIXME: this should have the right bind type 31 return ('?', $self->utc($thing)) if $thing->$_isa('DateTime'); 32 return @{${$thing}}; 33} 34 35sub _introspector { 36 my $d = DBIx::Introspector->new(drivers => '2013-12.01'); 37 38 $d->decorate_driver_unconnected(MSSQL => now_utc_sql => 'GETUTCDATE()'); 39 $d->decorate_driver_unconnected(SQLite => now_utc_sql => q<DATETIME('now')>); 40 $d->decorate_driver_unconnected(mysql => now_utc_sql => 'UTC_TIMESTAMP()'); 41 $d->decorate_driver_unconnected(Oracle => now_utc_sql => 'sys_extract_utc(SYSTIMESTAMP)'); 42 $d->decorate_driver_unconnected(Pg => now_utc_sql => 'CURRENT_TIMESTAMP'); 43 MSSQL: { 44 my %part_map = ( 45 year => 'year', 46 quarter => 'quarter', 47 month => 'month', 48 day_of_year => 'dayofyear', 49 day_of_month => 'day', 50 week => 'week', 51 day_of_week => 'ISO_WEEK', 52 hour => 'hour', 53 minute => 'minute', 54 second => 'second', 55 millisecond => 'millisecond', 56 nanosecond => 'nanosecond', 57 non_iso_day_of_week => 'weekday', 58 timezone_as_minutes => 'TZoffset', 59 ); 60 61 $d->decorate_driver_unconnected(MSSQL => datepart_sql => sub { 62 sub { 63 my ($date_sql, $part) = @_; 64 65 my ($sql, @args) = @$date_sql; 66 67 return [ 68 "DATEPART($part_map{$part}, $sql)", 69 @args 70 ] 71 } 72 }); 73 74 75 my %diff_part_map = %part_map; 76 $diff_part_map{day} = delete $diff_part_map{day_of_year}; 77 delete $diff_part_map{day_of_month}; 78 delete $diff_part_map{day_of_week}; 79 80 $d->decorate_driver_unconnected(MSSQL => dateadd_sql => sub { 81 sub { 82 my ($date_sql, $unit, $amount_sql) = @_; 83 84 my ($d_sql, @d_args) = @{$date_sql}; 85 my ($a_sql, @a_args) = @{$amount_sql}; 86 87 return [ 88 "DATEADD($diff_part_map{$unit}, CAST($a_sql AS int), $d_sql)", 89 @a_args, @d_args, 90 ]; 91 } 92 }); 93 94 $d->decorate_driver_unconnected(MSSQL => datesubtract_sql => sub { 95 sub { 96 my ($date_sql, $unit, $amount_sql) = @_; 97 98 my ($d_sql, @d_args) = @{$date_sql}; 99 my ($a_sql, @a_args) = @{$amount_sql}; 100 101 return [ # no idea if this works.. 102 "DATEADD($diff_part_map{$unit}, -1 * CAST($a_sql AS int), $d_sql)", 103 @a_args, @d_args, 104 ]; 105 } 106 }); 107 } 108 109 SQLITE: { 110 my %part_map = ( 111 month => 'm', 112 day_of_month => 'd', 113 year => 'Y', 114 hour => 'H', 115 day_of_year => 'j', 116 minute => 'M', 117 second => 'S', 118 day_of_week => 'w', 119 week => 'W', 120 # maybe don't support these or prefix them with 'sqlite.'? 121 julian_day => 'J', 122 seconds_since_epoch => 's', 123 fractional_seconds => 'f', 124 ); 125 126 $d->decorate_driver_unconnected(SQLite => datepart_sql => sub { 127 sub { 128 my ($date_sql, $part) = @_; 129 130 my ($sql, @args) = @$date_sql; 131 132 return [ 133 "STRFTIME('%$part_map{$part}', $sql)", 134 @args 135 ] 136 } 137 }); 138 139 my %diff_part_map = ( 140 day => 'days', 141 hour => 'hours', 142 minute => 'minutes', 143 second => 'seconds', 144 month => 'months', 145 year => 'years', 146 ); 147 148 $d->decorate_driver_unconnected(SQLite => dateadd_sql => sub { 149 sub { 150 my ($date_sql, $unit, $amount_sql) = @_; 151 152 my ($d_sql, @d_args) = @{$date_sql}; 153 my ($a_sql, @a_args) = @{$amount_sql}; 154 155 die "unknown part $unit" unless $diff_part_map{$unit}; 156 157 return [ 158 "DATETIME($d_sql, $a_sql || ?)", 159 @d_args, @a_args, " $diff_part_map{$unit}" 160 ]; 161 } 162 }); 163 164 $d->decorate_driver_unconnected(SQLite => datesubtract_sql => sub { 165 sub { 166 my ($date_sql, $unit, $amount_sql) = @_; 167 168 my ($d_sql, @d_args) = @{$date_sql}; 169 my ($a_sql, @a_args) = @{$amount_sql}; 170 171 die "unknown part $unit" unless $diff_part_map{$unit}; 172 173 return [ 174 "DATETIME($d_sql, '-' || $a_sql || ?)", 175 @d_args, @a_args, " $diff_part_map{$unit}" 176 ]; 177 } 178 }); 179 } 180 181 PG: { 182 my %part_map = ( 183 century => 'century', 184 decade => 'decade', 185 day_of_month => 'day', 186 day_of_week => 'dow', 187 day_of_year => 'doy', 188 seconds_since_epoch => 'epoch', 189 hour => 'hour', 190 iso_day_of_week => 'isodow', 191 iso_year => 'isoyear', 192 microsecond => 'microseconds', 193 millenium => 'millenium', 194 millisecond => 'milliseconds', 195 minute => 'minute', 196 month => 'month', 197 quarter => 'quarter', 198 second => 'second', 199 timezone => 'timezone', 200 timezone_hour => 'timezone_hour', 201 timezone_minute => 'timezone_minute', 202 week => 'week', 203 year => 'year', 204 ); 205 206 my %diff_part_map = %part_map; 207 delete $diff_part_map{qw( 208 day_of_week day_of_year iso_day_of_week iso_year millenium quarter 209 seconds_since_epoch timezone timezone_hour timezone_minute 210 )}; 211 $diff_part_map{day} = delete $diff_part_map{day_of_month}; 212 213 $d->decorate_driver_unconnected(Pg => datepart_sql => sub { 214 sub { 215 my ($date_sql, $part) = @_; 216 217 my ($sql, @args) = @$date_sql; 218 @args = ([{ dbd_attrs => SQL_TIMESTAMP }, $args[0]]) 219 if $sql eq '?' && @args == 1; 220 221 return [ 222 "date_part(?, $sql)", 223 $part_map{$part}, @args 224 ] 225 } 226 }); 227 228 $d->decorate_driver_unconnected(Pg => dateadd_sql => sub { 229 sub { 230 my ($date_sql, $unit, $amount_sql) = @_; 231 232 my ($d_sql, @d_args) = @{$date_sql}; 233 my ($a_sql, @a_args) = @{$amount_sql}; 234 235 @d_args = ([{ dbd_attrs => SQL_TIMESTAMP }, $d_args[0]]) 236 if $d_sql eq '?' && @d_args == 1; 237 238 die "unknown part $unit" unless $diff_part_map{$unit}; 239 240 return [ 241 "($d_sql + $a_sql * interval '1 $diff_part_map{$unit}')", 242 @d_args, @a_args, 243 ]; 244 } 245 }); 246 247 $d->decorate_driver_unconnected(Pg => datesubtract_sql => sub { 248 sub { 249 my ($date_sql, $unit, $amount_sql) = @_; 250 251 my ($d_sql, @d_args) = @{$date_sql}; 252 my ($a_sql, @a_args) = @{$amount_sql}; 253 254 @d_args = ([{ dbd_attrs => SQL_TIMESTAMP }, $d_args[0]]) 255 if $d_sql eq '?' && @d_args == 1; 256 257 die "unknown part $unit" unless $diff_part_map{$unit}; 258 259 return [ 260 "($d_sql - $a_sql * interval '1 $diff_part_map{$unit}')", 261 @d_args, @a_args, 262 ]; 263 } 264 }); 265 } 266 267 MYSQL: { 268 my %part_map = ( 269 microsecond => 'MICROSECOND', 270 second => 'SECOND', 271 minute => 'MINUTE', 272 hour => 'HOUR', 273 day_of_month => 'DAY', 274 week => 'WEEK', 275 month => 'MONTH', 276 quarter => 'QUARTER', 277 year => 'YEAR', 278 second_microsecond => 'SECOND_MICROSECOND', 279 minute_microsecond => 'MINUTE_MICROSECOND', 280 minute_second => 'MINUTE_SECOND', 281 hour_microsecond => 'HOUR_MICROSECOND', 282 hour_second => 'HOUR_SECOND', 283 hour_minute => 'HOUR_MINUTE', 284 day_microsecond => 'DAY_MICROSECOND', 285 day_second => 'DAY_SECOND', 286 day_minute => 'DAY_MINUTE', 287 day_hour => 'DAY_HOUR', 288 year_month => 'YEAR_MONTH', 289 ); 290 291 my %diff_part_map = %part_map; 292 $diff_part_map{day} = delete $diff_part_map{day_of_month}; 293 delete $diff_part_map{qw( 294 second_microsecond minute_microsecond minute_second 295 hour_microsecond hour_second hour_minute day_microsecond 296 day_second day_minute day_hour year_month 297 )}; 298 299 $d->decorate_driver_unconnected(mysql => datepart_sql => sub { 300 sub { 301 my ($date_sql, $part) = @_; 302 303 my ($sql, @args) = @$date_sql; 304 305 return [ 306 "EXTRACT($part_map{$part} FROM $sql)", @args 307 ] 308 } 309 }); 310 311 $d->decorate_driver_unconnected(mysql => dateadd_sql => sub { 312 sub { 313 my ($date_sql, $unit, $amount_sql) = @_; 314 315 my ($d_sql, @d_args) = @{$date_sql}; 316 my ($a_sql, @a_args) = @{$amount_sql}; 317 318 die "unknown part $unit" unless $diff_part_map{$unit}; 319 320 return [ 321 "DATE_ADD($d_sql, INTERVAL $a_sql $diff_part_map{$unit})", 322 @d_args, @a_args, 323 ]; 324 } 325 }); 326 327 $d->decorate_driver_unconnected(mysql => datesubtract_sql => sub { 328 sub { 329 my ($date_sql, $unit, $amount_sql) = @_; 330 331 my ($d_sql, @d_args) = @{$date_sql}; 332 my ($a_sql, @a_args) = @{$amount_sql}; 333 334 die "unknown part $unit" unless $diff_part_map{$unit}; 335 336 return [ 337 "DATE_SUB($d_sql, INTERVAL $a_sql $diff_part_map{$unit})", 338 @d_args, @a_args, 339 ]; 340 } 341 }); 342 } 343 344 ORACLE: { 345 my %part_map = ( 346 second => 'SECOND', 347 minute => 'MINUTE', 348 hour => 'HOUR', 349 day_of_month => 'DAY', 350 month => 'MONTH', 351 year => 'YEAR', 352 ); 353 354 $d->decorate_driver_unconnected(Oracle => datepart_sql => sub { 355 sub { 356 my ($date_sql, $part) = @_; 357 358 my ($sql, @args) = @$date_sql; 359 360 return [ 361 "EXTRACT($part_map{$part} FROM TO_TIMESTAMP($sql))", @args 362 ] 363 } 364 }); 365 366 my %diff_part_map = %part_map; 367 $diff_part_map{day} = delete $diff_part_map{day_of_month}; 368 delete $diff_part_map{$_} for qw(year month); 369 $d->decorate_driver_unconnected(Oracle => dateadd_sql => sub { 370 sub { 371 my ($date_sql, $unit, $amount_sql) = @_; 372 373 my ($d_sql, @d_args) = @{$date_sql}; 374 my ($a_sql, @a_args) = @{$amount_sql}; 375 376 die "unknown unit $unit" unless $diff_part_map{$unit}; 377 378 return [ 379 "(TO_TIMESTAMP($d_sql) + NUMTODSINTERVAL($a_sql, ?))", 380 @d_args, @a_args, $diff_part_map{$unit} 381 ]; 382 } 383 }); 384 $d->decorate_driver_unconnected(Oracle => datesubtract_sql => sub { 385 sub { 386 my ($date_sql, $unit, $amount_sql) = @_; 387 388 my ($d_sql, @d_args) = @{$date_sql}; 389 my ($a_sql, @a_args) = @{$amount_sql}; 390 391 die "unknown unit $unit" unless $diff_part_map{$unit}; 392 393 return [ # no idea if this works.. 394 "(TO_TIMESTAMP($d_sql) - NUMTODSINTERVAL($a_sql, ?))", 395 @d_args, @a_args, $diff_part_map{$unit} 396 ]; 397 } 398 }); 399 } 400 return $d; 401} 402 403use namespace::clean; 404 405 406sub delete { 407 my $self = shift; 408 409 $self = $self->as_subselect_rs 410 if $self->_resolved_attrs->{_DBICH_DM1}; 411 412 return $self->next::method(@_); 413} 414 415sub update { 416 my $self = shift; 417 418 $self = $self->as_subselect_rs 419 if $self->_resolved_attrs->{_DBICH_DM1}; 420 421 return $self->next::method(@_); 422} 423 424sub utc { 425 my ($self, $datetime) = @_; 426 427 my $tz_name = $datetime->time_zone->name; 428 429 die "floating dates are not allowed" 430 if $tz_name eq 'floating'; 431 432 $datetime = $datetime->clone->set_time_zone('UTC') 433 unless $tz_name eq 'UTC'; 434 435 $_[0]->result_source->storage->datetime_parser->format_datetime($datetime) 436} 437 438sub dt_before { 439 my ($self, $l, $r) = @_; 440 441 my ($l_sql, @l_args) = _flatten_thing($self, $l); 442 my ($r_sql, @r_args) = _flatten_thing($self, $r); 443 444 return $self->search(\[ 445 "$l_sql < $r_sql", @l_args, @r_args 446 ], { _DBICH_DM1 => 1 }); 447} 448 449sub dt_on_or_before { 450 my ($self, $l, $r) = @_; 451 452 my ($l_sql, @l_args) = _flatten_thing($self, $l); 453 my ($r_sql, @r_args) = _flatten_thing($self, $r); 454 455 $self->search(\[ 456 "$l_sql <= $r_sql", @l_args, @r_args 457 ], { _DBICH_DM1 => 1 }); 458} 459 460sub dt_on_or_after { 461 my ($self, $l, $r) = @_; 462 463 my ($l_sql, @l_args) = _flatten_thing($self, $l); 464 my ($r_sql, @r_args) = _flatten_thing($self, $r); 465 466 return $self->search(\[ 467 "$l_sql >= $r_sql", @l_args, @r_args 468 ], { _DBICH_DM1 => 1 }); 469} 470 471sub dt_after { 472 my ($self, $l, $r) = @_; 473 474 my ($l_sql, @l_args) = _flatten_thing($self, $l); 475 my ($r_sql, @r_args) = _flatten_thing($self, $r); 476 477 return $self->search(\[ 478 "$l_sql > $r_sql", @l_args, @r_args 479 ], { _DBICH_DM1 => 1 }); 480} 481 482my $d; 483sub utc_now { 484 my $self = shift; 485 my $storage = $self->result_source->storage; 486 $storage->ensure_connected; 487 488 $d ||= _introspector(); 489 490 return \( $d->get($storage->dbh, undef, 'now_utc_sql') ); 491} 492 493sub dt_SQL_add { 494 my ($self, $thing, $unit, $amount) = @_; 495 496 my $storage = $self->result_source->storage; 497 $storage->ensure_connected; 498 499 $d ||= _introspector(); 500 501 return \( 502 $d->get($storage->dbh, undef, 'dateadd_sql')->( 503 [ _flatten_thing($self, $thing) ], 504 $unit, 505 [ _flatten_thing($self, $amount) ], 506 ) 507 ); 508} 509 510sub dt_SQL_subtract { 511 my ($self, $thing, $unit, $amount) = @_; 512 513 my $storage = $self->result_source->storage; 514 $storage->ensure_connected; 515 516 $d ||= _introspector(); 517 518 return \( 519 $d->get($storage->dbh, undef, 'datesubtract_sql')->( 520 [ _flatten_thing($self, $thing) ], 521 $unit, 522 [ _flatten_thing($self, $amount) ], 523 ) 524 ); 525} 526 527sub dt_SQL_pluck { 528 my ($self, $thing, $part) = @_; 529 530 my $storage = $self->result_source->storage; 531 $storage->ensure_connected; 532 533 $d ||= _introspector(); 534 535 return \( 536 $d->get($storage->dbh, undef, 'datepart_sql')->( 537 [ _flatten_thing($self, $thing) ], 538 $part, 539 ) 540 ); 541} 542 5431; 544 545__END__ 546 547=pod 548 549=head1 NAME 550 551DBIx::Class::Helper::ResultSet::DateMethods1 - Work with dates in your RDBMS nicely 552 553=head1 SYNOPSIS 554 555 package MySchema::ResultSet::Bar; 556 557 use strict; 558 use warnings; 559 560 use parent 'DBIx::Class::ResultSet'; 561 562 __PACKAGE__->load_components('Helper::ResultSet::DateMethods1'); 563 564 # in code using resultset 565 566 # get count per year/month 567 $rs->search(undef, { 568 columns => { 569 count => '*', 570 year => $rs->dt_SQL_pluck({ -ident => '.start' }, 'year'), 571 month => $rs->dt_SQL_pluck({ -ident => '.start' }, 'month'), 572 }, 573 group_by => [ 574 $rs->dt_SQL_pluck({ -ident => '.start' }, 'year'), 575 $rs->dt_SQL_pluck({ -ident => '.start' }, 'month'), 576 ], 577 }); 578 579 # mysql 580 (SELECT `me`.*, EXTRACT(MONTH FROM `me`.`start`), EXTRACT(YEAR FROM `me`.`start`) FROM `HasDateOps` `me` GROUP BY EXTRACT(YEAR FROM `me`.`start`), EXTRACT(MONTH FROM `me`.`start`)) 581 582 # SQLite 583 (SELECT "me".*, STRFTIME('%m', "me"."start"), STRFTIME('%Y', "me"."start") FROM "HasDateOps" "me" GROUP BY STRFTIME('%Y', "me"."start"), STRFTIME('%m', "me"."start")) 584 585=head1 DESCRIPTION 586 587See L<DBIx::Class::Helper::ResultSet/NOTE> for a nice way to apply it 588to your entire schema. 589 590This ResultSet component gives the user tools to do B<mostly> portable date 591manipulation in the database. Before embarking on a cross database project, 592take a look at L</IMPLEMENTATION> to see what might break on switching 593databases. 594 595This package has a few types of methods. 596 597=over 598 599=item Search Shortcuts 600 601These, like typical ResultSet methods, return another ResultSet. See 602L</dt_before>, L</dt_on_or_before>, L</dt_on_or_after>, and L</dt_after>. 603 604=item The date helper 605 606There is only one: L</utc>. Makes searching with dates a little easier. 607 608=item SQL generators 609 610These help generate more complex queries. The can be used in many different 611parts of L<DBIx::Class::ResultSet/search>. See L</utc_now>, L</dt_SQL_pluck>, 612and L</dt_SQL_add>. 613 614=back 615 616=head1 TYPES 617 618Because these methods are so limited in scope they can be a bit more smart 619than typical C<SQL::Abstract> trees. 620 621There are "smart types" that this package supports. 622 623=over 624 625=item * vanilla scalars (C<1>, C<2012-12-12 12:12:12>) 626 627bound directly as untyped values 628 629=item * hashrefs with an C<-ident> (C<< { -ident => '.foo' } >>) 630 631As usual this gets flattened into a column. The one special feature in this 632module is that columns starting with a dot will automatically be prefixed with 633L<DBIx::Class::ResultSet/current_source_alias>. 634 635=item * L<DateTime> objects 636 637C<DateTime> objects work as if they were passed to L</utc>. 638 639=item * C<ScalarRef> (C<< \'NOW()' >>) 640 641As usual in C<DBIx::Class>, C<ScalarRef>'s will be flattened into regular SQL. 642 643=item * C<ArrayRefRef> (C<< \["SELECT foo FROM bar WHERE id = ?", [{}, 1]] >>) 644 645As usual in C<DBIx::Class>, C<ArrayRefRef>'s will be flattened into SQL with 646bound values. 647 648=back 649 650Anything not mentioned in the above list will explode, one way or another. 651 652=head1 IMPLEMENTATION 653 654=encoding utf8 655 656The exact details for the functions your database engine provides. 657 658If a piece of functionality is flagged with ⚠, it means that the feature in 659question is not portable at all, and only supported on that engine. 660 661=head2 C<SQL Server> 662 663=over 664 665=item * L</utc_now> - L<GETUTCDATE|http://msdn.microsoft.com/en-us/library/ms178635.aspx> 666 667=item * L</dt_SQL_pluck> - L<DATEPART|http://msdn.microsoft.com/en-us/library/ms174420.aspx> 668 669Supported units 670 671=over 672 673=item * year 674 675=item * quarter 676 677=item * month 678 679=item * day_of_year 680 681=item * day_of_month 682 683=item * week 684 685=item * day_of_week 686 687=item * hour 688 689=item * minute 690 691=item * second 692 693=item * millisecond 694 695=item * nanosecond ⚠ 696 697=item * non_iso_day_of_week 698 699SQL Server offers both C<ISO_WEEK> and C<weekday>. For interop reasons 700C<weekday> uses the C<ISO_WEEK> version. 701 702=item * timezone_as_minutes ⚠ 703 704=back 705 706=item * L</dt_SQL_add> - L<DATEADD|http://msdn.microsoft.com/en-us/library/ms186819.aspx> 707 708Supported units 709 710=over 711 712=item * year 713 714=item * quarter 715 716=item * month 717 718=item * day 719 720=item * week 721 722=item * hour 723 724=item * minute 725 726=item * second 727 728=item * millisecond 729 730=item * nanosecond ⚠ 731 732=item * iso_day_of_week 733 734=item * timezone_as_minutes ⚠ 735 736=back 737 738=back 739 740=head2 C<SQLite> 741 742=over 743 744=item * L</utc_now> - L<DATETIME('now')|https://www.sqlite.org/lang_datefunc.html> 745 746=item * L</dt_SQL_pluck> - L<STRFTIME|https://www.sqlite.org/lang_datefunc.html> 747 748Note: C<SQLite>'s pluck implementation pads numbers with zeros, because it is 749implemented on based on a formatting function. If you want your code to work 750on SQLite you'll need to strip off (or just numify) what you get out of the 751database first. 752 753Available units 754 755=over 756 757=item * month 758 759=item * day_of_month 760 761=item * year 762 763=item * hour 764 765=item * day_of_year 766 767=item * minute 768 769=item * second 770 771=item * day_of_week 772 773=item * week 774 775=item * julian_day ⚠ 776 777=item * seconds_since_epoch 778 779=item * fractional_seconds ⚠ 780 781=back 782 783=item * L</dt_SQL_add> - L<DATETIME|https://www.sqlite.org/lang_datefunc.html> 784 785Available units 786 787=over 788 789=item * day 790 791=item * hour 792 793=item * minute 794 795=item * second 796 797=item * month 798 799=item * year 800 801=back 802 803=back 804 805=head2 C<PostgreSQL> 806 807=over 808 809=item * L</utc_now> - L<CURRENT_TIMESTAMP|http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT> 810 811=item * L</dt_SQL_pluck> - L<date_part|http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT> 812 813Available units 814 815=over 816 817=item * century ⚠ 818 819=item * decade ⚠ 820 821=item * day_of_month 822 823=item * day_of_week 824 825=item * day_of_year 826 827=item * seconds_since_epoch 828 829=item * hour 830 831=item * iso_day_of_week 832 833=item * iso_year 834 835=item * microsecond 836 837=item * millenium ⚠ 838 839=item * millisecond 840 841=item * minute 842 843=item * month 844 845=item * quarter 846 847=item * second 848 849=item * timezone ⚠ 850 851=item * timezone_hour ⚠ 852 853=item * timezone_minute ⚠ 854 855=item * week 856 857=item * year 858 859=back 860 861=item * L</dt_SQL_add> - Addition and L<interval|http://www.postgresql.org/docs/current/static/functions-datetime.html#OPERATORS-DATETIME-TABLE> 862 863To be clear, it ends up looking like: 864C<< ("some_column" + 5 * interval '1 minute') >> 865 866Available units 867 868=over 869 870=item * century ⚠ 871 872=item * decade ⚠ 873 874=item * day 875 876=item * hour 877 878=item * microsecond ⚠ 879 880=item * millisecond 881 882=item * minute 883 884=item * month 885 886=item * second 887 888=item * week 889 890=item * year 891 892=back 893 894=back 895 896=head2 C<MySQL> 897 898=over 899 900=item * L</utc_now> - L<UTC_TIMESTAMP|https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_utc-timestamp> 901 902=item * L</dt_SQL_pluck> - L<EXTRACT|https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_extract> 903 904Available units 905 906=over 907 908=item * microsecond 909 910=item * second 911 912=item * minute 913 914=item * hour 915 916=item * day_of_month 917 918=item * week 919 920=item * month 921 922=item * quarter 923 924=item * year 925 926=item * second_microsecond ⚠ 927 928=item * minute_microsecond ⚠ 929 930=item * minute_second ⚠ 931 932=item * hour_microsecond ⚠ 933 934=item * hour_second ⚠ 935 936=item * hour_minute ⚠ 937 938=item * day_microsecond ⚠ 939 940=item * day_second ⚠ 941 942=item * day_minute ⚠ 943 944=item * day_hour ⚠ 945 946=item * year_month ⚠ 947 948=back 949 950=item * L</dt_SQL_add> - L<DATE_ADD|https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add> 951 952Available units 953 954=over 955 956=item * microsecond 957 958=item * second 959 960=item * minute 961 962=item * hour 963 964=item * day 965 966=item * week 967 968=item * month 969 970=item * quarter 971 972=item * year 973 974=back 975 976=back 977 978=head2 C<Oracle> 979 980=over 981 982=item * L</utc_now> - L<sys_extract_utc(SYSTIMESTAMP)|http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions167.htm> 983 984=item * L</dt_SQL_pluck> - L<EXTRACT|docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm> 985 986Available units 987 988=over 989 990=item * second 991 992=item * minute 993 994=item * hour 995 996=item * day_of_month 997 998=item * month 999 1000=item * year 1001 1002=back 1003 1004=item * L</dt_SQL_add> - Addition and L<NUMTODSINTERVAL|http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions103.htm> 1005 1006To be clear, it ends up looking like: 1007C<< ("some_column" + NUMTODSINTERVAL(4, 'MINUTE') >> 1008 1009Available units 1010 1011=over 1012 1013=item * second 1014 1015=item * minute 1016 1017=item * hour 1018 1019=item * day 1020 1021=back 1022 1023=back 1024 1025=head1 CONTRIBUTORS 1026 1027These people worked on the original implementation, and thus deserve some 1028credit for at least providing me a reference to implement this based off of: 1029 1030=over 1031 1032=item Alexander Hartmaier (abraxxa) for Oracle implementation details 1033 1034=item Devin Austin (dhoss) for Pg implementation details 1035 1036=item Rafael Kitover (caelum) for providing a test environment with lots of DBs 1037 1038=back 1039 1040=head1 WHENCE dt_SQL_diff? 1041 1042The original implementation of these date helpers (originally dubbed date 1043operators) included a third operator called C<"diff">. It existed to 1044subtract one date from another and return a duration. After using it a few 1045times and getting bitten every time, I decided to stop using it and instead 1046compare against actual dates always. If someone can come up with a good use 1047case I am interested in re-implementing C<dt_SQL_diff>, but I worry that it 1048will be very unportable and generally not very useful. 1049 1050=head1 METHODS 1051 1052=head2 utc 1053 1054 $rs->search({ 1055 'some_date' => $rs->utc($datetime), 1056 })->all 1057 1058Takes a L<DateTime> object, updates the C<time_zone> to C<UTC>, and formats it 1059according to whatever database engine you are using. 1060 1061Dies if you pass it a date with a C<< floating time_zone >>. 1062 1063=head2 utc_now 1064 1065Returns a C<ScalarRef> representing the way to get the current date and time 1066in C<UTC> for whatever database engine you are using. 1067 1068=head2 dt_before 1069 1070 $rs->dt_before({ -ident => '.start' }, { -ident => '.end' })->all 1071 1072Takes two values, each an expression of L</TYPES>. 1073 1074=head2 dt_on_or_before 1075 1076 $rs->dt_on_or_before({ -ident => '.start' }, DateTime->now)->all 1077 1078Takes two values, each an expression of L</TYPES>. 1079 1080=head2 dt_on_or_after 1081 1082 $rs->dt_on_or_after(DateTime->now, { ident => '.end' })->all 1083 1084Takes two values, each an expression of L</TYPES>. 1085 1086=head2 dt_after 1087 1088 $rs->dt_after({ ident => '.end' }, $rs->get_column('datecol')->as_query)->all 1089 1090Takes two values, each an expression of L</TYPES>. 1091 1092=head2 dt_SQL_add 1093 1094 # which ones start in 3 minutes? 1095 $rs->dt_on_or_after( 1096 { ident => '.start' }, 1097 $rs->dt_SQL_add($rs->utc_now, 'minute', 3) 1098 )->all 1099 1100Takes three arguments: a date conforming to L</TYPES>, a unit, and an amount. 1101The idea is to add the given unit to the datetime. See your L</IMPLEMENTATION> 1102for what units are accepted. 1103 1104=head2 dt_SQL_subtract 1105 1106Same as L<dt_SQL_add>, but subtracts the amount. 1107 1108Only confirmed to work with Postgres, MySQL and SQLite. It should work with Oracle 1109and MSSQL, but due to lack of access to those DB engines the implementation was 1110done only based on docs. 1111 1112This method was implemented by L<Thomas Klausner|https://domm.plix.at> and 1113sponsored by L<Ctrl O|https://www.ctrlo.com/>. 1114 1115=head2 dt_SQL_pluck 1116 1117 # get count per year 1118 $rs->search(undef, { 1119 columns => { 1120 count => '*', 1121 year => $rs->dt_SQL_pluck({ -ident => '.start' }, 'year'), 1122 }, 1123 group_by => [$rs->dt_SQL_pluck({ -ident => '.start' }, 'year')], 1124 })->hri->all 1125 1126Takes two arguments: a date conforming to L</TYPES> and a unit. The idea 1127is to pluck a given unit from the datetime. See your L</IMPLEMENTATION> 1128for what units are accepted. 1129 1130=head1 AUTHOR 1131 1132Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com> 1133 1134=head1 COPYRIGHT AND LICENSE 1135 1136This software is copyright (c) 2020 by Arthur Axel "fREW" Schmidt. 1137 1138This is free software; you can redistribute it and/or modify it under 1139the same terms as the Perl 5 programming language system itself. 1140 1141=cut 1142