9 use DBIC::SqlMakerTest;
11 use DBIx::Class::SQLMaker::MSSQL;
13 use Data::Dumper::Concise;
17 sub unknown_col { +{ dbic_colname => '' } }
27 sqlite => DBICTest->init_schema( no_populate => 1 ),
29 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PASS/};
31 my $s = DBICTest::Schema->connect($dsn, $user, $pass);
32 try { $s->storage->ensure_connected };
34 $s->storage->dbh_do (sub {
35 my ($storage, $dbh) = @_;
36 eval { $dbh->do("DROP TABLE event") };
39 id INT IDENTITY NOT NULL,
40 starts_at DATE NOT NULL,
41 created_on DATETIME NOT NULL,
42 varchar_date VARCHAR(20),
43 varchar_datetime VARCHAR(20),
44 skip_inflation DATETIME,
45 ts_without_tz DATETIME,
50 $dbs_to_test{mssql} = 1;
54 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::MSSQL' )
58 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MYSQL_${_}" } qw/DSN USER PASS/};
60 my $s = DBICTest::Schema->connect($dsn, $user, $pass);
61 try { $s->storage->ensure_connected };
63 $s->storage->dbh_do (sub {
64 my ($storage, $dbh) = @_;
65 eval { $dbh->do("DROP TABLE event") };
68 id INT AUTO_INCREMENT NOT NULL,
69 starts_at DATE NOT NULL,
70 created_on DATETIME NOT NULL,
71 varchar_date VARCHAR(20),
72 varchar_datetime VARCHAR(20),
73 skip_inflation DATETIME,
74 ts_without_tz DATETIME,
79 $dbs_to_test{mysql} = 1;
83 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::mysql' )
86 ## copypasta'd for great justice
88 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_PG_${_}" } qw/DSN USER PASS/};
90 my $s = DBICTest::Schema->connect($dsn, $user, $pass);
91 try { $s->storage->ensure_connected };
93 $s->storage->dbh_do (sub {
94 my ($storage, $dbh) = @_;
95 eval { $dbh->do("DROP TABLE event") };
98 id SERIAL NOT NULL PRIMARY KEY,
99 starts_at DATE NOT NULL,
100 created_on TIMESTAMP NOT NULL,
101 varchar_date VARCHAR(20),
102 varchar_datetime VARCHAR(20),
103 skip_inflation TIMESTAMP,
104 ts_without_tz TIMESTAMP WITHOUT TIME ZONE
107 $dbs_to_test{postgres} = 1;
111 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Pg' )
115 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/};
117 my $s = DBICTest::Schema->connect($dsn, $user, $pass, { on_connect_call => 'datetime_setup' });
118 try { $s->storage->ensure_connected };
120 $s->storage->dbh_do (sub {
121 my ($storage, $dbh) = @_;
122 eval { $dbh->do("DROP TRIGGER trq_event_id") };
123 eval { $dbh->do("DROP SEQUENCE sq_event_id") };
124 eval { $dbh->do("DROP TABLE event") };
125 $dbh->do('CREATE SEQUENCE sq_event_id');
129 starts_at DATE NOT NULL,
130 created_on TIMESTAMP NOT NULL,
131 varchar_date VARCHAR(20),
132 varchar_datetime VARCHAR(20),
133 skip_inflation TIMESTAMP,
134 ts_without_tz TIMESTAMP,
135 CONSTRAINT PK_EVENT PRIMARY KEY (id)
139 CREATE TRIGGER trg_event_id
140 BEFORE INSERT ON event
142 new.id IS NULL OR new.id = 0
145 SELECT sq_event_id.nextval
150 $dbs_to_test{oracle} = 1;
154 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Oracle::Generic', on_connect_call => 'datetime_setup' )
159 my %rs = map { $_ => $schema{$_}->resultset('Event') } keys %schema;
161 for (grep { $schema{$_}->storage->connected } keys %rs) {
163 [qw(starts_at created_on skip_inflation)],
164 ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
165 ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
169 my $date = DateTime->new(
182 skip_inflation => $_[2]
186 my $date2 = $date->clone->set_day(16);
188 my $date3 = DateTime->new(
197 my $date4 = DateTime->new(
204 ## search => { dbic_search_code/params }
205 ## rdbms_name => literal_sql
209 msg => '-dt_now works',
210 search => { 'me.created_on' => { -dt => $date } },
211 select_sql => 'me.starts_at, me.created_on, me.skip_inflation',
212 where => 'me.created_on = ?',
213 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
214 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
217 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12.000' ]],
218 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000')],
223 hri => [hri_thing('2010-12-12 00:00:00', '2010-12-14 12:12:12.000000', '2019-12-12 12:12:12.000000')],
228 msg => '-dt_year works',
229 search => { 'me.id' => 1 },
230 select => [ [ -dt_year => { -ident => 'me.created_on' } ] ],
232 where => "me.id = ?",
233 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
234 hri => [{ year => 2010 }],
236 select => "DATEPART(year, me.created_on)",
239 select => "EXTRACT(YEAR FROM me.created_on)",
242 select => "STRFTIME('%Y', me.created_on)",
245 select => "date_part('year', me.created_on)",
248 select => "EXTRACT(year FROM me.created_on)",
253 msg => '-dt_year works with DateTime obj',
254 search => { 'me.id' => 1 },
255 select => [ [ -dt_year => $date ] ],
257 where => "me.id = ?",
258 hri => [{ year => 2010 }],
259 bind => [[{ sqlt_datatype => 'timestamp' } => '2010-12-14 12:12:12' ], [{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
261 select => "DATEPART(year, ?)",
262 bind => [[{ sqlt_datatype => 'timestamp' } => '2010-12-14 12:12:12.000' ], [{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
265 select => "EXTRACT(YEAR FROM ?)",
268 select => "STRFTIME('%Y', ?)",
271 select => "date_part('year', ?)",
274 select => "EXTRACT(year FROM ?)",
279 msg => '-dt_get (year, month) works',
280 search => { 'me.id' => 1 },
281 select => [ [ -dt_get => [[qw(year month)], { -ident => 'me.created_on' }] ] ],
282 as => [ qw(year month) ],
283 where => "me.id = ?",
284 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
285 hri => [{ year => 2010, month => 12 }],
287 select => "DATEPART(year, me.created_on), DATEPART(month, me.created_on)",
290 select => "EXTRACT(YEAR FROM me.created_on), EXTRACT(MONTH FROM me.created_on)",
293 select => "STRFTIME('%Y', me.created_on), STRFTIME('%m', me.created_on)",
296 select => "date_part('year', me.created_on), date_part('month', me.created_on)",
299 select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)",
304 msg => '-dt_get (year, month) works with DateTime obj',
305 search => { 'me.id' => 1 },
306 select => [ [ -dt_get => [[qw(year month)], $date ] ] ],
307 as => [ qw(year month) ],
308 where => "me.id = ?",
310 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
311 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
312 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]
314 hri => [{ year => 2010, month => 12 }],
316 select => "DATEPART(year, ?), DATEPART(month, ?)",
318 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
319 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
320 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]
324 select => "EXTRACT(YEAR FROM ?), EXTRACT(MONTH FROM ?)",
327 select => "STRFTIME('%Y', ?), STRFTIME('%m', ?)",
330 select => "date_part('year', ?), date_part('month', ?)",
333 select => "EXTRACT(year FROM ?), EXTRACT(month FROM ?)",
338 msg => '-dt_month works',
339 search => { 'me.id' => 1 },
340 select => [ [ -dt_month => { -ident => 'me.created_on' } ] ],
342 where => "me.id = ?",
343 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
344 hri => [{ month => 12 }],
346 select => "STRFTIME('%m', me.created_on)",
349 select => "DATEPART(month, me.created_on)",
352 select => "EXTRACT(MONTH FROM me.created_on)",
355 select => "date_part('month', me.created_on)",
358 select => "EXTRACT(month FROM me.created_on)",
363 msg => '-dt_month works with DateTime obj',
364 search => { 'me.id' => 1 },
365 select => [ [ -dt_month => $date ] ],
367 where => "me.id = ?",
368 bind => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
369 hri => [{ month => 12 }],
371 select => "STRFTIME('%m', ?)",
374 bind => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
375 select => "DATEPART(month, ?)",
378 select => "EXTRACT(MONTH FROM ?)",
381 select => "date_part('month', ?)",
384 select => "EXTRACT(month FROM ?)",
389 msg => '-dt_day works',
390 search => { 'me.id' => 1 },
391 select => [ [ -dt_day => { -ident => 'me.created_on' } ] ],
393 where => "me.id = ?",
394 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
395 hri => [{ day => 14 }],
397 select => "STRFTIME('%d', me.created_on)",
400 select => "DATEPART(day, me.created_on)",
403 select => "EXTRACT(DAY FROM me.created_on)",
406 select => "date_part('day', me.created_on)",
409 select => "EXTRACT(day FROM me.created_on)",
414 msg => '-dt_day works with DateTime obj',
415 search => { 'me.id' => 1 },
416 select => [ [ -dt_day => $date ] ],
418 where => "me.id = ?",
419 bind => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
420 hri => [{ day => 14 }],
422 select => "STRFTIME('%d', ?)",
425 select => "DATEPART(day, ?)",
426 bind => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
429 select => "EXTRACT(DAY FROM ?)",
432 select => "date_part('day', ?)",
435 select => "EXTRACT(day FROM ?)",
440 msg => '-dt_hour works',
441 search => { 'me.id' => 1 },
442 select => [ [ -dt_hour => { -ident => 'me.created_on' } ] ],
444 where => "me.id = ?",
445 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
446 hri => [{ hour => 12 }],
448 select => "STRFTIME('%H', me.created_on)",
451 select => "DATEPART(hour, me.created_on)",
454 select => "EXTRACT(HOUR FROM me.created_on)",
457 select => "date_part('hour', me.created_on)",
460 select => "EXTRACT(hour FROM me.created_on)",
465 msg => '-dt_hour works with DateTime obj',
466 search => { 'me.id' => 1 },
467 select => [ [ -dt_hour => $date ] ],
469 where => "me.id = ?",
470 bind => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
471 hri => [{ hour => 12 }],
473 select => "STRFTIME('%H', ?)",
476 select => "DATEPART(hour, ?)",
477 bind => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
480 select => "EXTRACT(HOUR FROM ?)",
483 select => "date_part('hour', ?)",
486 select => "EXTRACT(hour FROM ?)",
491 msg => '-dt_minute works',
492 search => { 'me.id' => 1 },
493 select => [ [ -dt_minute => { -ident => 'me.created_on' } ] ],
495 where => "me.id = ?",
496 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
497 hri => [{ minute => 12 }],
499 select => "STRFTIME('%M', me.created_on)",
502 select => "DATEPART(minute, me.created_on)",
505 select => "EXTRACT(MINUTE FROM me.created_on)",
508 select => "date_part('minute', me.created_on)",
511 select => "EXTRACT(minute FROM me.created_on)",
516 msg => '-dt_minute works with DateTime obj',
517 search => { 'me.id' => 1 },
518 select => [ [ -dt_minute => $date ] ],
520 where => "me.id = ?",
521 bind => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
522 hri => [{ minute => 12 }],
524 select => "STRFTIME('%M', ?)",
527 select => "DATEPART(minute, ?)",
528 bind => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
531 select => "EXTRACT(MINUTE FROM ?)",
534 select => "date_part('minute', ?)",
537 select => "EXTRACT(minute FROM ?)",
542 msg => '-dt_second works',
543 search => { 'me.id' => 1 },
544 select => [ [ -dt_second => { -ident => 'me.created_on' } ] ],
546 where => "me.id = ?",
547 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
548 hri => [{ second => 12 }],
550 select => "STRFTIME('%S', me.created_on)",
553 select => "DATEPART(second, me.created_on)",
556 select => "EXTRACT(SECOND FROM me.created_on)",
559 select => "date_part('second', me.created_on)",
562 select => "EXTRACT(second FROM me.created_on)",
567 msg => '-dt_second works with DateTime obj',
568 search => { 'me.id' => 1 },
569 select => [ [ -dt_second => $date ] ],
571 where => "me.id = ?",
572 bind => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
573 hri => [{ second => 12 }],
575 select => "STRFTIME('%S', ?)",
578 select => "DATEPART(second, ?)",
579 bind => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
582 select => "EXTRACT(SECOND FROM ?)",
585 select => "date_part('second', ?)",
588 select => "EXTRACT(second FROM ?)",
593 msg => '-dt_diff (second) works',
594 search => { 'me.id' => 2 },
595 select => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
596 as => [ 'sec_diff' ],
597 where => "me.id = ?",
598 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
599 hri => [{ sec_diff => 2*24*60*60 }],
601 select => "(STRFTIME('%s', me.created_on) - STRFTIME('%s', me.skip_inflation))",
604 select => "DATEDIFF(second, me.skip_inflation, me.created_on)",
607 select => "TIMESTAMPDIFF(SECOND, me.skip_inflation, me.created_on)",
610 select => "date_part('EPOCH', me.created_on) - date_part('EPOCH', me.skip_inflation)",
613 select => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31 * 24 * 60 * 60)",
618 msg => '-dt_diff (second) works with DateTime objs',
619 search => { 'me.id' => 2 },
620 select => [ [ -dt_diff => [second => $date2, $date ] ] ],
621 as => [ 'sec_diff' ],
622 where => "me.id = ?",
624 [{sqlt_datatype => 'timestamp'} => '2010-12-16 12:12:12' ],
625 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
626 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
628 hri => [{ sec_diff => 2*24*60*60 }],
630 select => "(STRFTIME('%s', ?) - STRFTIME('%s', ?))",
633 select => "DATEDIFF(second, ?, ?)",
635 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
636 [{sqlt_datatype => 'timestamp'} => '2010-12-16 12:12:12.000' ],
637 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
641 select => "TIMESTAMPDIFF(SECOND, ?, ?)",
643 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
644 [{sqlt_datatype => 'timestamp'} => '2010-12-16 12:12:12' ],
645 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
649 select => "date_part('EPOCH', ?) - date_part('EPOCH', ?)",
652 select => "TRUNC(MONTHS_BETWEEN(?, ?) * 31 * 24 * 60 * 60)",
657 msg => '-dt_diff (day) works',
658 search => { 'me.id' => 2 },
659 select => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
660 as => [ 'day_diff' ],
661 where => "me.id = ?",
662 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
663 hri => [{ day_diff => 2 }],
665 select => "(JULIANDAY(me.created_on) - JULIANDAY(me.skip_inflation))",
668 select => "DATEDIFF(dayofyear, me.skip_inflation, me.created_on)",
671 select => "TIMESTAMPDIFF(DAY, me.skip_inflation, me.created_on)",
674 select => "date_part('DAY', me.created_on) - date_part('DAY', me.skip_inflation)",
677 select => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31)",
682 msg => '-dt_diff (day) works with DateTime objs',
683 search => { 'me.id' => 2 },
684 select => [ [ -dt_diff => [day => $date2, $date ] ] ],
685 as => [ 'day_diff' ],
686 where => "me.id = ?",
688 [{sqlt_datatype => 'timestamp'} => '2010-12-16 12:12:12' ],
689 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
690 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
692 hri => [{ day_diff => 2 }],
694 select => "(JULIANDAY(?) - JULIANDAY(?))",
698 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
699 [{sqlt_datatype => 'timestamp'} => '2010-12-16 12:12:12.000' ],
700 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
702 select => "DATEDIFF(dayofyear, ?, ?)",
706 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
707 [{sqlt_datatype => 'timestamp'} => '2010-12-16 12:12:12' ],
708 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
710 select => "TIMESTAMPDIFF(DAY, ?, ?)",
713 select => "date_part('DAY', ?) - date_part('DAY', ?)",
716 select => "TRUNC(MONTHS_BETWEEN(?, ?) * 31)",
721 msg => '-dt_diff (year) works',
722 search => { 'me.id' => 2 },
723 select => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ],
725 where => "me.id = ?",
726 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
727 hri => [{ year => -1 }],
729 exception_like => qr/date diff not supported for part "year" with database "SQLite"/,
732 select => "DATEDIFF(year, me.created_on, me.starts_at)",
735 select => "TIMESTAMPDIFF(YEAR, me.created_on, me.starts_at)",
738 select => "date_part('YEAR', me.starts_at) - date_part('YEAR', me.created_on)",
741 select => "TRUNC(MONTHS_BETWEEN(me.starts_at, me.created_on) / 12)",
746 msg => '-dt_diff (year) works with DateTime objs',
747 search => { 'me.id' => 2 },
748 select => [ [ -dt_diff => [year => $date4, $date3 ] ] ],
750 where => "me.id = ?",
752 [{sqlt_datatype => 'timestamp'} => '2010-12-12 00:00:00' ],
753 [{sqlt_datatype => 'timestamp'} => '2011-12-14 12:12:12' ],
754 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
756 hri => [{ year => -1 }],
758 exception_like => qr/date diff not supported for part "year" with database "SQLite"/,
761 select => "DATEDIFF(year, ?, ?)",
763 [{sqlt_datatype => 'timestamp'} => '2011-12-14 12:12:12.000' ],
764 [{sqlt_datatype => 'timestamp'} => '2010-12-12 00:00:00.000' ],
765 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
769 select => "TIMESTAMPDIFF(YEAR, ?, ?)",
771 [{sqlt_datatype => 'timestamp'} => '2011-12-14 12:12:12' ],
772 [{sqlt_datatype => 'timestamp'} => '2010-12-12 00:00:00' ],
773 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
777 select => "date_part('YEAR', ?) - date_part('YEAR', ?)",
780 select => "TRUNC(MONTHS_BETWEEN(?, ?) / 12)",
785 msg => '-dt_add (year) works',
786 search => { 'me.id' => 2 },
787 select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ],
789 where => "me.id = ?",
790 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
791 hri => [{ date => '2014-12-14 12:12:12' }],
793 select => "(datetime(me.created_on, ? || ' years'))",
796 select => "(DATEADD(year, CAST(? AS INTEGER), me.created_on))",
797 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
798 hri => [{ date => '2014-12-14 12:12:12.000' }],
801 select => "DATE_ADD(me.created_on, INTERVAL ? YEAR)",
804 select => "(me.created_on + ? * interval '1 YEAR')",
807 select => "(me.created_on + NUMTOYMINTERVAL(?, 'year'))",
808 hri => [{ date => '2014-12-14 12:12:12.000000000' }],
813 msg => '-dt_add (year) works with DateTime obj',
814 search => { 'me.id' => 2 },
815 select => [ [ -dt_add => [year => 3, $date ] ] ],
817 where => "me.id = ?",
819 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
821 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
823 hri => [{ date => '2013-12-14 12:12:12' }],
825 select => "(datetime(?, ? || ' years'))",
828 select => "(DATEADD(year, CAST(? AS INTEGER), ?))",
830 [{sqlt_datatype => 'integer'}, 3],
831 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
832 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
834 hri => [{ date => '2013-12-14 12:12:12.000' }],
837 select => "DATE_ADD(?, INTERVAL ? YEAR)",
840 select => "(? + ? * interval '1 YEAR')",
843 select => "(? + NUMTOYMINTERVAL(?, 'year'))",
844 hri => [{ date => '2014-12-14 12:12:12.000000000' }],
849 msg => '-dt_add (month) works',
850 search => { 'me.id' => 2 },
851 select => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ],
853 where => "me.id = ?",
854 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
855 hri => [{ date => '2012-03-14 12:12:12' }],
857 select => "(datetime(me.created_on, ? || ' months'))",
860 select => "(DATEADD(month, CAST(? AS INTEGER), me.created_on))",
861 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
862 hri => [{ date => '2012-03-14 12:12:12.000' }],
865 select => "(me.created_on + ? * interval '1 MONTH')",
868 select => "DATE_ADD(me.created_on, INTERVAL ? MONTH)",
871 select => "(me.created_on + NUMTOYMINTERVAL(?, 'month'))",
872 hri => [{ date => '2012-03-14 12:12:12.000000000' }],
877 msg => '-dt_add (month) works with DateTime obj',
878 search => { 'me.id' => 2 },
879 select => [ [ -dt_add => [month => 3, $date ] ] ],
881 where => "me.id = ?",
883 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
885 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
887 hri => [{ date => '2011-03-14 12:12:12' }],
889 select => "(datetime(?, ? || ' months'))",
892 select => "(DATEADD(month, CAST(? AS INTEGER), ?))",
894 [{sqlt_datatype => 'integer'}, 3],
895 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
896 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
898 hri => [{ date => '2011-03-14 12:12:12.000' }],
901 select => "(? + ? * interval '1 MONTH')",
904 select => "DATE_ADD(?, INTERVAL ? MONTH)",
907 select => "(? + NUMTOYMINTERVAL(?, 'month'))",
908 hri => [{ date => '2012-03-14 12:12:12.000000000' }],
913 msg => '-dt_add (day) works',
914 search => { 'me.id' => 2 },
915 select => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ],
917 where => "me.id = ?",
918 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
919 hri => [{ date => '2011-12-17 12:12:12' }],
921 select => "(datetime(me.created_on, ? || ' days'))",
924 select => "(DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))",
925 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
926 hri => [{ date => '2011-12-17 12:12:12.000' }],
929 select => "(me.created_on + ? * interval '1 DAY')",
932 select => "DATE_ADD(me.created_on, INTERVAL ? DAY)",
935 select => "(me.created_on + NUMTODSINTERVAL(?, 'day'))",
936 hri => [{ date => '2011-12-17 12:12:12.000000000' }],
941 msg => '-dt_add (day) works with DateTime obj',
942 search => { 'me.id' => 2 },
943 select => [ [ -dt_add => [day => 3, $date ] ] ],
945 where => "me.id = ?",
947 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
949 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
951 hri => [{ date => '2010-12-17 12:12:12' }],
953 select => "(datetime(?, ? || ' days'))",
956 select => "(DATEADD(dayofyear, CAST(? AS INTEGER), ?))",
958 [{sqlt_datatype => 'integer'}, 3],
959 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
960 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
962 hri => [{ date => '2010-12-17 12:12:12.000' }],
965 select => "(? + ? * interval '1 DAY')",
968 select => "DATE_ADD(?, INTERVAL ? DAY)",
971 select => "(? + NUMTODSINTERVAL(?, 'day'))",
972 hri => [{ date => '2011-12-17 12:12:12.000000000' }],
977 msg => '-dt_add (hour) works',
978 search => { 'me.id' => 2 },
979 select => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ],
981 where => "me.id = ?",
982 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
983 hri => [{ date => '2011-12-14 15:12:12' }],
985 select => "(datetime(me.created_on, ? || ' hours'))",
988 select => "(DATEADD(hour, CAST(? AS INTEGER), me.created_on))",
989 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
990 hri => [{ date => '2011-12-14 15:12:12.000' }],
993 select => "(me.created_on + ? * interval '1 HOUR')",
996 select => "DATE_ADD(me.created_on, INTERVAL ? HOUR)",
999 select => "(me.created_on + NUMTODSINTERVAL(?, 'hour'))",
1000 hri => [{ date => '2011-12-14 15:12:12.000000000' }],
1005 msg => '-dt_add (hour) works with DateTime obj',
1006 search => { 'me.id' => 2 },
1007 select => [ [ -dt_add => [hour => 3, $date ] ] ],
1009 where => "me.id = ?",
1011 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
1013 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
1015 hri => [{ date => '2010-12-14 15:12:12' }],
1017 select => "(datetime(?, ? || ' hours'))",
1020 select => "(DATEADD(hour, CAST(? AS INTEGER), ?))",
1022 [{sqlt_datatype => 'integer'}, 3],
1023 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
1024 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
1026 hri => [{ date => '2010-12-14 15:12:12.000' }],
1029 select => "(? + ? * interval '1 HOUR')",
1032 select => "DATE_ADD(?, INTERVAL ? HOUR)",
1035 select => "(? + NUMTODSINTERVAL(?, 'hour'))",
1036 hri => [{ date => '2011-12-14 15:12:12.000000000' }],
1041 msg => '-dt_add (minute) works',
1042 search => { 'me.id' => 2 },
1043 select => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ],
1045 where => "me.id = ?",
1046 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
1047 hri => [{ date => '2011-12-14 12:15:12' }],
1049 select => "(datetime(me.created_on, ? || ' minutes'))",
1052 select => "(DATEADD(minute, CAST(? AS INTEGER), me.created_on))",
1053 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
1054 hri => [{ date => '2011-12-14 12:15:12.000' }],
1057 select => "(me.created_on + ? * interval '1 MINUTE')",
1060 select => "DATE_ADD(me.created_on, INTERVAL ? MINUTE)",
1063 select => "(me.created_on + NUMTODSINTERVAL(?, 'minute'))",
1064 hri => [{ date => '2011-12-14 12:15:12.000000000' }],
1069 msg => '-dt_add (minute) works with DateTime obj',
1070 search => { 'me.id' => 2 },
1071 select => [ [ -dt_add => [minute => 3, $date ] ] ],
1073 where => "me.id = ?",
1075 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
1077 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
1079 hri => [{ date => '2010-12-14 12:15:12' }],
1081 select => "(datetime(?, ? || ' minutes'))",
1084 select => "(DATEADD(minute, CAST(? AS INTEGER), ?))",
1086 [{sqlt_datatype => 'integer'}, 3],
1087 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
1088 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
1090 hri => [{ date => '2010-12-14 12:15:12.000' }],
1093 select => "(? + ? * interval '1 MINUTE')",
1096 select => "DATE_ADD(?, INTERVAL ? MINUTE)",
1099 select => "(? + NUMTODSINTERVAL(?, 'minute'))",
1100 hri => [{ date => '2011-12-14 12:15:12.000000000' }],
1105 msg => '-dt_add (second) works',
1106 search => { 'me.id' => 2 },
1107 select => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ],
1109 where => "me.id = ?",
1110 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
1111 hri => [{ date => '2011-12-14 12:12:15' }],
1113 select => "(datetime(me.created_on, ? || ' seconds'))",
1116 select => "(DATEADD(second, CAST(? AS INTEGER), me.created_on))",
1117 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
1118 hri => [{ date => '2011-12-14 12:12:15.000' }],
1121 select => "(me.created_on + ? * interval '1 SECOND')",
1124 select => "DATE_ADD(me.created_on, INTERVAL ? SECOND)",
1127 select => "(me.created_on + NUMTODSINTERVAL(?, 'second'))",
1128 hri => [{ date => '2011-12-14 12:12:15.000000000' }],
1133 msg => '-dt_add (second) works with DateTime obj',
1134 search => { 'me.id' => 2 },
1135 select => [ [ -dt_add => [second => 3, $date ] ] ],
1137 where => "me.id = ?",
1139 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
1141 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
1143 hri => [{ date => '2010-12-14 12:12:15' }],
1145 select => "(datetime(?, ? || ' seconds'))",
1148 select => "(DATEADD(second, CAST(? AS INTEGER), ?))",
1150 [{sqlt_datatype => 'integer'}, 3],
1151 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
1152 [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
1154 hri => [{ date => '2010-12-14 12:12:15.000' }],
1157 select => "(? + ? * interval '1 SECOND')",
1160 select => "DATE_ADD(?, INTERVAL ? SECOND)",
1163 select => "(? + NUMTODSINTERVAL(?, 'second'))",
1164 hri => [{ date => '2011-12-14 12:12:15.000000000' }],
1169 msg => 'nested -dt_add works',
1170 search => { 'me.id' => 2 },
1171 select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ],
1173 where => "me.id = ?",
1174 hri => [{ date => '2011-12-15 12:12:15' }],
1175 bind => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
1177 select => "(datetime((datetime(me.created_on, ? || ' days')), ? || ' seconds'))",
1180 select => "(DATEADD(second, CAST(? AS INTEGER), (DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))))",
1181 bind => [[{sqlt_datatype => 'integer'}, 3 ], [{sqlt_datatype => 'integer'}, 1], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
1182 hri => [{ date => '2011-12-15 12:12:15.000' }],
1185 select => "((me.created_on + ? * interval '1 DAY') + ? * interval '1 SECOND')",
1188 select => "DATE_ADD(DATE_ADD(me.created_on, INTERVAL ? DAY), INTERVAL ? SECOND)",
1191 select => "((me.created_on + NUMTODSINTERVAL(?, 'day')) + NUMTODSINTERVAL(?, 'second'))",
1192 hri => [{ date => '2011-12-15 12:12:15.000000000' }],
1197 msg => 'nested -dt_add works with DateTime obj',
1198 search => { 'me.id' => 2 },
1199 select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, $date ] } ] ] ],
1201 where => "me.id = ?",
1202 hri => [{ date => '2010-12-15 12:12:15' }],
1204 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
1207 [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]
1210 select => "(datetime((datetime(?, ? || ' days')), ? || ' seconds'))",
1213 select => "(DATEADD(second, CAST(? AS INTEGER), (DATEADD(dayofyear, CAST(? AS INTEGER), ?))))",
1215 [{sqlt_datatype => 'integer'}, 3 ],
1216 [{sqlt_datatype => 'integer'}, 1],
1217 [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
1218 [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]
1220 hri => [{ date => '2010-12-15 12:12:15.000' }],
1223 select => "((? + ? * interval '1 DAY') + ? * interval '1 SECOND')",
1226 select => "DATE_ADD(DATE_ADD(?, INTERVAL ? DAY), INTERVAL ? SECOND)",
1229 select => "((? + NUMTODSINTERVAL(?, 'day')) + NUMTODSINTERVAL(?, 'second'))",
1230 hri => [{ date => '2011-12-15 12:12:15.000000000' }],
1235 msg => '-dt_before works',
1236 search => { 'me.created_on' => { -dt_before => '2011-12-14 12:12:12' } },
1237 select => [ [ -ident => 'me.created_on' ] ],
1239 select_sql => "me.created_on",
1240 where => "me.created_on < ?",
1241 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
1242 hri => [{ date => '2010-12-14 12:12:12' }],
1247 hri => [{ date => '2010-12-14 12:12:12.000' }],
1250 hri => [{ date => '2010-12-14 12:12:12.000000' }],
1255 msg => '-dt_before works with DateTime obj',
1256 search => { 'me.created_on' => { -dt_before => $date3 } },
1257 select => [ [ -ident => 'me.created_on' ] ],
1259 select_sql => "me.created_on",
1260 where => "me.created_on < ?",
1261 bind => [[{sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
1262 hri => [{ date => '2010-12-14 12:12:12' }],
1267 hri => [{ date => '2010-12-14 12:12:12.000' }],
1268 bind => [[{sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12.000']],
1271 hri => [{ date => '2010-12-14 12:12:12.000000' }],
1276 msg => '-dt_on_or_before works',
1277 search => { 'me.created_on' => { -dt_on_or_before => '2011-12-14 12:12:12' } },
1278 select => [ [ -ident => 'me.created_on' ] ],
1280 select_sql => "me.created_on",
1281 where => "me.created_on <= ?",
1282 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
1283 hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
1288 hri => [{ date => '2010-12-14 12:12:12.000' }, { date => '2011-12-14 12:12:12.000' }],
1291 hri => [{ date => '2010-12-14 12:12:12.000000' }, { date => '2011-12-14 12:12:12.000000' }],
1296 msg => '-dt_on_or_before works with DateTime obj',
1297 search => { 'me.created_on' => { -dt_on_or_before => $date3 } },
1298 select => [ [ -ident => 'me.created_on' ] ],
1300 select_sql => "me.created_on",
1301 where => "me.created_on <= ?",
1302 bind => [[{sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
1303 hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
1308 hri => [{ date => '2010-12-14 12:12:12.000' }, { date => '2011-12-14 12:12:12.000' }],
1309 bind => [[{sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12.000']],
1312 hri => [{ date => '2010-12-14 12:12:12.000000' }, { date => '2011-12-14 12:12:12.000000' }],
1317 msg => '-dt_after works',
1318 search => { 'me.created_on' => { -dt_after => '2010-12-14 12:12:12' } },
1319 select => [ [ -ident => 'me.created_on' ] ],
1321 select_sql => "me.created_on",
1322 where => "me.created_on > ?",
1323 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
1324 hri => [{ date => '2011-12-14 12:12:12' }],
1329 hri => [{ date => '2011-12-14 12:12:12.000' }],
1332 hri => [{ date => '2011-12-14 12:12:12.000000' }],
1337 msg => '-dt_after works with DateTime obj',
1338 search => { 'me.created_on' => { -dt_after => $date } },
1339 select => [ [ -ident => 'me.created_on' ] ],
1341 select_sql => "me.created_on",
1342 where => "me.created_on > ?",
1343 bind => [[{sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
1344 hri => [{ date => '2011-12-14 12:12:12' }],
1349 hri => [{ date => '2011-12-14 12:12:12.000' }],
1350 bind => [[{sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12.000']],
1353 hri => [{ date => '2011-12-14 12:12:12.000000' }],
1358 msg => '-dt_on_or_after works',
1359 search => { 'me.created_on' => { -dt_on_or_after => '2010-12-14 12:12:12' } },
1360 select => [ [ -ident => 'me.created_on' ] ],
1362 select_sql => "me.created_on",
1363 where => "me.created_on >= ?",
1364 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
1365 hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
1370 hri => [{ date => '2010-12-14 12:12:12.000' }, { date => '2011-12-14 12:12:12.000' }],
1373 hri => [{ date => '2010-12-14 12:12:12.000000' }, { date => '2011-12-14 12:12:12.000000' }],
1378 msg => '-dt_on_or_after works with DateTime obj',
1379 search => { 'me.created_on' => { -dt_on_or_after => $date } },
1380 select => [ [ -ident => 'me.created_on' ] ],
1382 select_sql => "me.created_on",
1383 where => "me.created_on >= ?",
1384 bind => [[{sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
1385 hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
1390 hri => [{ date => '2010-12-14 12:12:12.000' }, { date => '2011-12-14 12:12:12.000' }],
1391 bind => [[{sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12.000']],
1394 hri => [{ date => '2010-12-14 12:12:12.000000' }, { date => '2011-12-14 12:12:12.000000' }],
1400 for my $t (@tests) {
1403 for my $db (keys %rs) {
1404 my $db_test = $t->{$db};
1406 ok 0, "$t->{msg} ($db not tested!)";
1414 ( exists $t->{select}
1415 ? ( select => $t->{select}, as => $t->{as} )
1416 : ( columns => [qw(starts_at created_on skip_inflation)] )
1419 $my_rs = $rs{$db}->search($t->{search}, $stuff);
1420 $r = $my_rs->as_query
1423 if ($db_test->{exception_like}) {
1426 $db_test->{exception_like},
1427 "throws the expected exception ($db_test->{exception_like})",
1430 if ($db_test->{warning_like}) {
1433 $db_test->{warning_like},
1434 "issues the expected warning ($db_test->{warning_like})"
1442 '(SELECT ' . ($db_test->{select} || $t->{select_sql}) . ' FROM event me WHERE ' . ($db_test->{where} || $t->{where}) . ')',
1443 $db_test->{bind} || $t->{bind},
1444 ($t->{msg} ? "$t->{msg} ($db)" : ())
1448 if (my $hri = $db_test->{hri} || $t->{hri}) {
1449 skip "Cannot test database we are not connected to ($db)", 1 unless $dbs_to_test{$db};
1450 skip $db_test->{skip} . " ($db)", 1 if $db_test->{skip};
1452 my $msg = ($t->{msg} ? "$t->{msg} ($db actually pulls expected data)" : '');
1454 my $got = [ $my_rs->hri_dump->all ];
1455 my $success = is_deeply $got, $hri, $msg;
1457 warn "$db: $t->{msg} got: " . Dumper $got;
1458 warn "$db: $t->{msg} expected: " . Dumper $hri;