my @tests = (
{
+ msg => '-dt_now works',
search => { 'me.created_on' => { -dt => $date } },
- sqlite => {
- select => 'me.starts_at, me.created_on, me.skip_inflation',
- where => 'me.created_on = ?',
- bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
- hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
- },
+ select_sql => 'me.starts_at, me.created_on, me.skip_inflation',
+ where => 'me.created_on = ?',
+ bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
+ hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
+ sqlite => { },
mssql => {
- select => 'me.starts_at, me.created_on, me.skip_inflation',
- where => 'me.created_on = ?',
bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12.000' ]],
hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000')],
},
- mysql => {
- select => 'me.starts_at, me.created_on, me.skip_inflation',
- where => 'me.created_on = ?',
- bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
- hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
- },
- postgres => {
- select => 'me.starts_at, me.created_on, me.skip_inflation',
- where => 'me.created_on = ?',
- bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
- hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
- },
+ mysql => { },
+ postgres => { },
oracle => {
- select => 'me.starts_at, me.created_on, me.skip_inflation',
- where => 'me.created_on = ?',
- bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
hri => [hri_thing('2010-12-12 00:00:00', '2010-12-14 12:12:12.000000', '2019-12-12 12:12:12.000000')],
},
- msg => '-dt_now works',
},
{
search => { 'me.id' => 1 },
select => [ [ -dt_year => { -ident => 'me.created_on' } ] ],
as => [ 'year' ],
+ where => "me.id = ?",
+ bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
+ hri => [{ year => 2010 }],
mssql => {
select => "DATEPART(year, me.created_on)",
- where => "me.id = ?",
- bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ year => 2010 }],
},
mysql => {
select => "EXTRACT(YEAR FROM me.created_on)",
- where => "me.id = ?",
- bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ year => 2010 }],
},
sqlite => {
select => "STRFTIME('%Y', me.created_on)",
- where => "me.id = ?",
- bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ year => 2010 }],
},
postgres => {
select => "date_part('year', me.created_on)",
- where => "me.id = ?",
- bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ year => 2010 }],
},
oracle => {
select => "EXTRACT(year FROM me.created_on)",
- where => "me.id = ?",
- bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ year => 2010 }],
},
},
search => { 'me.id' => 1 },
select => [ [ -dt_year => $date ] ],
as => [ 'year' ],
+ where => "me.id = ?",
+ hri => [{ year => 2010 }],
+ bind => [[{ sqlt_datatype => 'timestamp' } => '2010-12-14 12:12:12' ], [{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
mssql => {
select => "DATEPART(year, ?)",
- where => "me.id = ?",
bind => [[{ sqlt_datatype => 'timestamp' } => '2010-12-14 12:12:12.000' ], [{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ year => 2010 }],
},
mysql => {
select => "EXTRACT(YEAR FROM ?)",
- where => "me.id = ?",
- bind => [[{ sqlt_datatype => 'timestamp' } => '2010-12-14 12:12:12' ], [{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ year => 2010 }],
},
sqlite => {
select => "STRFTIME('%Y', ?)",
- where => "me.id = ?",
- bind => [[{ sqlt_datatype => 'timestamp' } => '2010-12-14 12:12:12' ], [{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ year => 2010 }],
},
postgres => {
select => "date_part('year', ?)",
- where => "me.id = ?",
- bind => [[{ sqlt_datatype => 'timestamp' } => '2010-12-14 12:12:12' ], [{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ year => 2010 }],
},
oracle => {
select => "EXTRACT(year FROM ?)",
- where => "me.id = ?",
- bind => [[{ sqlt_datatype => 'timestamp' } => '2010-12-14 12:12:12' ], [{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ year => 2010 }],
},
},
search => { 'me.id' => 1 },
select => [ [ -dt_get => [[qw(year month)], { -ident => 'me.created_on' }] ] ],
as => [ qw(year month) ],
+ where => "me.id = ?",
+ bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+ hri => [{ year => 2010, month => 12 }],
mssql => {
select => "DATEPART(year, me.created_on), DATEPART(month, me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ year => 2010, month => 12 }],
},
mysql => {
select => "EXTRACT(YEAR FROM me.created_on), EXTRACT(MONTH FROM me.created_on)",
- where => "me.id = ?",
- bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ year => 2010, month => 12 }],
},
sqlite => {
select => "STRFTIME('%Y', me.created_on), STRFTIME('%m', me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ year => 2010, month => 12 }],
},
postgres => {
select => "date_part('year', me.created_on), date_part('month', me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ year => 2010, month => 12 }],
},
oracle => {
select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ year => 2010, month => 12 }],
},
},
{
msg => '-dt_month works',
search => { 'me.id' => 1 },
- select => [ [ -dt_month => { -ident => 'me.created_on' } ] ],
- as => [ 'month' ],
+ select => [ [ -dt_month => { -ident => 'me.created_on' } ] ],
+ as => [ 'month' ],
+ where => "me.id = ?",
+ bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+ hri => [{ month => 12 }],
sqlite => {
select => "STRFTIME('%m', me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ month => 12 }],
},
mssql => {
select => "DATEPART(month, me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ month => 12 }],
},
mysql => {
select => "EXTRACT(MONTH FROM me.created_on)",
- where => "me.id = ?",
- bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ month => 12 }],
},
postgres => {
select => "date_part('month', me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ month => 12 }],
},
oracle => {
select => "EXTRACT(month FROM me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ month => 12 }],
},
},
{
msg => '-dt_day works',
search => { 'me.id' => 1 },
- select => [ [ -dt_day => { -ident => 'me.created_on' } ] ],
- as => [ 'day' ],
+ select => [ [ -dt_day => { -ident => 'me.created_on' } ] ],
+ as => [ 'day' ],
+ where => "me.id = ?",
+ bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+ hri => [{ day => 14 }],
sqlite => {
select => "STRFTIME('%d', me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ day => 14 }],
},
mssql => {
select => "DATEPART(day, me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ day => 14 }],
},
mysql => {
select => "EXTRACT(DAY FROM me.created_on)",
- where => "me.id = ?",
- bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ day => 14 }],
},
postgres => {
select => "date_part('day', me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ day => 14 }],
},
oracle => {
select => "EXTRACT(day FROM me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ day => 14 }],
},
},
{
msg => '-dt_hour works',
search => { 'me.id' => 1 },
- select => [ [ -dt_hour => { -ident => 'me.created_on' } ] ],
- as => [ 'hour' ],
+ select => [ [ -dt_hour => { -ident => 'me.created_on' } ] ],
+ as => [ 'hour' ],
+ where => "me.id = ?",
+ bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+ hri => [{ hour => 12 }],
sqlite => {
select => "STRFTIME('%H', me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ hour => 12 }],
},
mssql => {
select => "DATEPART(hour, me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ hour => 12 }],
},
mysql => {
select => "EXTRACT(HOUR FROM me.created_on)",
- where => "me.id = ?",
- bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ hour => 12 }],
},
postgres => {
select => "date_part('hour', me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ hour => 12 }],
},
oracle => {
select => "EXTRACT(hour FROM me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ hour => 12 }],
},
},
{
msg => '-dt_minute works',
search => { 'me.id' => 1 },
- select => [ [ -dt_minute => { -ident => 'me.created_on' } ] ],
- as => [ 'minute' ],
+ select => [ [ -dt_minute => { -ident => 'me.created_on' } ] ],
+ as => [ 'minute' ],
+ where => "me.id = ?",
+ bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+ hri => [{ minute => 12 }],
sqlite => {
select => "STRFTIME('%M', me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ minute => 12 }],
},
mssql => {
select => "DATEPART(minute, me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ minute => 12 }],
},
mysql => {
select => "EXTRACT(MINUTE FROM me.created_on)",
- where => "me.id = ?",
- bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ minute => 12 }],
},
postgres => {
select => "date_part('minute', me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ minute => 12 }],
},
oracle => {
select => "EXTRACT(minute FROM me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ minute => 12 }],
},
},
{
msg => '-dt_second works',
search => { 'me.id' => 1 },
- select => [ [ -dt_second => { -ident => 'me.created_on' } ] ],
- as => [ 'second' ],
+ select => [ [ -dt_second => { -ident => 'me.created_on' } ] ],
+ as => [ 'second' ],
+ where => "me.id = ?",
+ bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+ hri => [{ second => 12 }],
sqlite => {
select => "STRFTIME('%S', me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ second => 12 }],
},
mssql => {
select => "DATEPART(second, me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ second => 12 }],
},
mysql => {
select => "EXTRACT(SECOND FROM me.created_on)",
- where => "me.id = ?",
- bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
- hri => [{ second => 12 }],
},
postgres => {
select => "date_part('second', me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ second => 12 }],
},
oracle => {
select => "EXTRACT(second FROM me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
- hri => [{ second => 12 }],
},
},
{
msg => '-dt_diff (second) works',
search => { 'me.id' => 2 },
- select => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
- as => [ 'sec_diff' ],
+ select => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
+ as => [ 'sec_diff' ],
+ where => "me.id = ?",
+ bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ sec_diff => 2*24*60*60 }],
sqlite => {
select => "(STRFTIME('%s', me.created_on) - STRFTIME('%s', me.skip_inflation))",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ sec_diff => 2*24*60*60 }],
},
mssql => {
select => "DATEDIFF(second, me.skip_inflation, me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ sec_diff => 2*24*60*60 }],
},
mysql => {
select => "TIMESTAMPDIFF(SECOND, me.skip_inflation, me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ sec_diff => 2*24*60*60 }],
},
postgres => {
select => "date_part('EPOCH', me.created_on) - date_part('EPOCH', me.skip_inflation)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ sec_diff => 2*24*60*60 }],
},
oracle => {
select => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31 * 24 * 60 * 60)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ sec_diff => 2*24*60*60 }],
},
},
{
msg => '-dt_diff (day) works',
search => { 'me.id' => 2 },
- select => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
- as => [ 'day_diff' ],
+ select => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
+ as => [ 'day_diff' ],
+ where => "me.id = ?",
+ bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ day_diff => 2 }],
sqlite => {
select => "(JULIANDAY(me.created_on) - JULIANDAY(me.skip_inflation))",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ day_diff => 2 }],
},
mssql => {
select => "DATEDIFF(dayofyear, me.skip_inflation, me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ day_diff => 2 }],
},
mysql => {
select => "TIMESTAMPDIFF(DAY, me.skip_inflation, me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ day_diff => 2 }],
},
postgres => {
select => "date_part('DAY', me.created_on) - date_part('DAY', me.skip_inflation)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ day_diff => 2 }],
},
oracle => {
select => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ day_diff => 2 }],
},
},
{
- msg => '-dt_diff (year) works',
+ msg => '-dt_diff (year) works',
search => { 'me.id' => 2 },
- select => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ],
- as => [ 'year' ],
+ select => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ],
+ as => [ 'year' ],
+ where => "me.id = ?",
+ bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ year => -1 }],
sqlite => {
exception_like => qr/date diff not supported for part "year" with database "SQLite"/,
},
mssql => {
select => "DATEDIFF(year, me.created_on, me.starts_at)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ year => -1 }],
},
mysql => {
select => "TIMESTAMPDIFF(YEAR, me.created_on, me.starts_at)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ year => -1 }],
},
postgres => {
select => "date_part('YEAR', me.starts_at) - date_part('YEAR', me.created_on)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ year => -1 }],
},
oracle => {
select => "TRUNC(MONTHS_BETWEEN(me.starts_at, me.created_on) / 12)",
- where => "me.id = ?",
- bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ year => -1 }],
},
},
{
msg => '-dt_add (year) works',
search => { 'me.id' => 2 },
- select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ],
- as => [ 'date' ],
+ select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ],
+ as => [ 'date' ],
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2014-12-14 12:12:12' }],
sqlite => {
select => "(datetime(me.created_on, ? || ' years'))",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2014-12-14 12:12:12' }],
},
mssql => {
select => "(DATEADD(year, CAST(? AS INTEGER), me.created_on))",
- where => "me.id = ?",
bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2014-12-14 12:12:12.000' }],
},
mysql => {
select => "DATE_ADD(me.created_on, INTERVAL ? YEAR)",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2014-12-14 12:12:12' }],
},
postgres => {
select => "(me.created_on + ? * interval '1 YEAR')",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2014-12-14 12:12:12' }],
-
},
oracle => {
select => "(me.created_on + NUMTOYMINTERVAL(?, 'year'))",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2014-12-14 12:12:12.000000000' }],
},
},
{
msg => '-dt_add (month) works',
search => { 'me.id' => 2 },
- select => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ],
- as => [ 'date' ],
+ select => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ],
+ as => [ 'date' ],
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2012-03-14 12:12:12' }],
sqlite => {
select => "(datetime(me.created_on, ? || ' months'))",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2012-03-14 12:12:12' }],
},
mssql => {
select => "(DATEADD(month, CAST(? AS INTEGER), me.created_on))",
- where => "me.id = ?",
bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2012-03-14 12:12:12.000' }],
},
postgres => {
select => "(me.created_on + ? * interval '1 MONTH')",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2012-03-14 12:12:12' }],
},
mysql => {
select => "DATE_ADD(me.created_on, INTERVAL ? MONTH)",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2012-03-14 12:12:12' }],
},
oracle => {
select => "(me.created_on + NUMTOYMINTERVAL(?, 'month'))",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2012-03-14 12:12:12.000000000' }],
},
},
{
msg => '-dt_add (day) works',
search => { 'me.id' => 2 },
- select => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ],
- as => [ 'date' ],
+ select => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ],
+ as => [ 'date' ],
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2011-12-17 12:12:12' }],
sqlite => {
select => "(datetime(me.created_on, ? || ' days'))",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2011-12-17 12:12:12' }],
},
mssql => {
select => "(DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))",
- where => "me.id = ?",
bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2011-12-17 12:12:12.000' }],
},
postgres => {
select => "(me.created_on + ? * interval '1 DAY')",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2011-12-17 12:12:12' }],
},
-
mysql => {
select => "DATE_ADD(me.created_on, INTERVAL ? DAY)",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2011-12-17 12:12:12' }],
},
oracle => {
select => "(me.created_on + NUMTODSINTERVAL(?, 'day'))",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2011-12-17 12:12:12.000000000' }],
},
},
{
msg => '-dt_add (hour) works',
search => { 'me.id' => 2 },
- select => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ],
- as => [ 'date' ],
+ select => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ],
+ as => [ 'date' ],
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2011-12-14 15:12:12' }],
sqlite => {
select => "(datetime(me.created_on, ? || ' hours'))",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2011-12-14 15:12:12' }],
},
mssql => {
select => "(DATEADD(hour, CAST(? AS INTEGER), me.created_on))",
- where => "me.id = ?",
bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2011-12-14 15:12:12.000' }],
},
postgres => {
select => "(me.created_on + ? * interval '1 HOUR')",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2011-12-14 15:12:12' }],
},
mysql => {
select => "DATE_ADD(me.created_on, INTERVAL ? HOUR)",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2011-12-14 15:12:12' }],
},
oracle => {
select => "(me.created_on + NUMTODSINTERVAL(?, 'hour'))",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2011-12-14 15:12:12.000000000' }],
},
},
{
msg => '-dt_add (minute) works',
search => { 'me.id' => 2 },
- select => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ],
- as => [ 'date' ],
+ select => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ],
+ as => [ 'date' ],
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2011-12-14 12:15:12' }],
sqlite => {
select => "(datetime(me.created_on, ? || ' minutes'))",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2011-12-14 12:15:12' }],
},
mssql => {
select => "(DATEADD(minute, CAST(? AS INTEGER), me.created_on))",
- where => "me.id = ?",
bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2011-12-14 12:15:12.000' }],
},
postgres => {
select => "(me.created_on + ? * interval '1 MINUTE')",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2011-12-14 12:15:12' }],
},
mysql => {
select => "DATE_ADD(me.created_on, INTERVAL ? MINUTE)",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2011-12-14 12:15:12' }],
},
oracle => {
select => "(me.created_on + NUMTODSINTERVAL(?, 'minute'))",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2011-12-14 12:15:12.000000000' }],
},
},
{
msg => '-dt_add (second) works',
search => { 'me.id' => 2 },
- select => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ],
- as => [ 'date' ],
+ select => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ],
+ as => [ 'date' ],
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2011-12-14 12:12:15' }],
sqlite => {
select => "(datetime(me.created_on, ? || ' seconds'))",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2011-12-14 12:12:15' }],
},
mssql => {
select => "(DATEADD(second, CAST(? AS INTEGER), me.created_on))",
- where => "me.id = ?",
bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2011-12-14 12:12:15.000' }],
},
postgres => {
select => "(me.created_on + ? * interval '1 SECOND')",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2011-12-14 12:12:15' }],
},
mysql => {
select => "DATE_ADD(me.created_on, INTERVAL ? SECOND)",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2011-12-14 12:12:15' }],
},
oracle => {
select => "(me.created_on + NUMTODSINTERVAL(?, 'second'))",
- where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2011-12-14 12:12:15.000000000' }],
},
},
{
msg => 'nested -dt_add works',
search => { 'me.id' => 2 },
- select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ],
- as => [ 'date' ],
+ select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ],
+ as => [ 'date' ],
+ where => "me.id = ?",
+ hri => [{ date => '2011-12-15 12:12:15' }],
+ bind => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
sqlite => {
select => "(datetime((datetime(me.created_on, ? || ' days')), ? || ' seconds'))",
- where => "me.id = ?",
- bind => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
- hri => [{ date => '2011-12-15 12:12:15' }],
},
mssql => {
select => "(DATEADD(second, CAST(? AS INTEGER), (DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))))",
- where => "me.id = ?",
bind => [[{sqlt_datatype => 'integer'}, 3 ], [{sqlt_datatype => 'integer'}, 1], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
hri => [{ date => '2011-12-15 12:12:15.000' }],
},
postgres => {
select => "((me.created_on + ? * interval '1 DAY') + ? * interval '1 SECOND')",
- where => "me.id = ?",
- bind => [[unknown_col, 1], [unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2011-12-15 12:12:15' }],
},
mysql => {
select => "DATE_ADD(DATE_ADD(me.created_on, INTERVAL ? DAY), INTERVAL ? SECOND)",
- where => "me.id = ?",
- bind => [[unknown_col, 1], [unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
- hri => [{ date => '2011-12-15 12:12:15' }],
},
oracle => {
select => "((me.created_on + NUMTODSINTERVAL(?, 'day')) + NUMTODSINTERVAL(?, 'second'))",
- where => "me.id = ?",
- bind => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
hri => [{ date => '2011-12-15 12:12:15.000000000' }],
},
},
{
- msg => '-dt_before works',
- search => { 'me.created_on' => { -dt_before => '2011-12-14 12:12:12' } },
+ msg => '-dt_before works',
+ search => { 'me.created_on' => { -dt_before => '2011-12-14 12:12:12' } },
select => [ [ -ident => 'me.created_on' ] ],
as => [ 'date' ],
- sqlite => {
- select => "me.created_on",
- where => "me.created_on < ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }],
- },
- postgres => {
- select => "me.created_on",
- where => "me.created_on < ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }],
- },
- mysql => {
- select => "me.created_on",
- where => "me.created_on < ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }],
- },
- mssql => {
- select => "me.created_on",
- where => "me.created_on < ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }],
- },
- oracle => {
- select => "me.created_on",
- where => "me.created_on < ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }],
+ select_sql => "me.created_on",
+ where => "me.created_on < ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }],
+ sqlite => { },
+ postgres => { },
+ mysql => { },
+ mssql => {
+ hri => [{ date => '2010-12-14 12:12:12.000' }],
+ },
+ oracle => {
+ hri => [{ date => '2010-12-14 12:12:12.000000' }],
},
},
{
- msg => '-dt_on_or_before works',
- search => { 'me.created_on' => { -dt_on_or_before => '2011-12-14 12:12:12' } },
+ msg => '-dt_on_or_before works',
+ search => { 'me.created_on' => { -dt_on_or_before => '2011-12-14 12:12:12' } },
select => [ [ -ident => 'me.created_on' ] ],
as => [ 'date' ],
- sqlite => {
- select => "me.created_on",
- where => "me.created_on <= ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
- },
- postgres => {
- select => "me.created_on",
- where => "me.created_on <= ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
- },
- mysql => {
- select => "me.created_on",
- where => "me.created_on <= ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
- },
- mssql => {
- select => "me.created_on",
- where => "me.created_on <= ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
- },
- oracle => {
- select => "me.created_on",
- where => "me.created_on <= ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
+ select_sql => "me.created_on",
+ where => "me.created_on <= ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
+ sqlite => { },
+ postgres => { },
+ mysql => { },
+ mssql => {
+ hri => [{ date => '2010-12-14 12:12:12.000' }, { date => '2011-12-14 12:12:12.000' }],
+ },
+ oracle => {
+ hri => [{ date => '2010-12-14 12:12:12.000000' }, { date => '2011-12-14 12:12:12.000000' }],
},
},
{
- msg => '-dt_after works',
- search => { 'me.created_on' => { -dt_after => '2010-12-14 12:12:12' } },
+ msg => '-dt_after works',
+ search => { 'me.created_on' => { -dt_after => '2010-12-14 12:12:12' } },
select => [ [ -ident => 'me.created_on' ] ],
as => [ 'date' ],
- sqlite => {
- select => "me.created_on",
- where => "me.created_on > ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
- hri => [{ date => '2011-12-14 12:12:12' }],
- },
- postgres => {
- select => "me.created_on",
- where => "me.created_on > ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
- hri => [{ date => '2011-12-14 12:12:12' }],
- },
- mysql => {
- select => "me.created_on",
- where => "me.created_on > ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
- hri => [{ date => '2011-12-14 12:12:12' }],
- },
- mssql => {
- select => "me.created_on",
- where => "me.created_on > ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
- hri => [{ date => '2011-12-14 12:12:12' }],
- },
- oracle => {
- select => "me.created_on",
- where => "me.created_on > ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
- hri => [{ date => '2011-12-14 12:12:12' }],
+ select_sql => "me.created_on",
+ where => "me.created_on > ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
+ hri => [{ date => '2011-12-14 12:12:12' }],
+ sqlite => { },
+ postgres => { },
+ mysql => { },
+ mssql => {
+ hri => [{ date => '2011-12-14 12:12:12.000' }],
+ },
+ oracle => {
+ hri => [{ date => '2011-12-14 12:12:12.000000' }],
},
},
{
- msg => '-dt_on_or_after works',
- search => { 'me.created_on' => { -dt_on_or_after => '2010-12-14 12:12:12' } },
+ msg => '-dt_on_or_after works',
+ search => { 'me.created_on' => { -dt_on_or_after => '2010-12-14 12:12:12' } },
select => [ [ -ident => 'me.created_on' ] ],
as => [ 'date' ],
- sqlite => {
- select => "me.created_on",
- where => "me.created_on >= ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
- },
- postgres => {
- select => "me.created_on",
- where => "me.created_on >= ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
- },
- mysql => {
- select => "me.created_on",
- where => "me.created_on >= ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
- },
- mssql => {
- select => "me.created_on",
- where => "me.created_on >= ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
- },
- oracle => {
- select => "me.created_on",
- where => "me.created_on >= ?",
- bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
- hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
+ select_sql => "me.created_on",
+ where => "me.created_on >= ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
+ sqlite => { },
+ postgres => { },
+ mysql => { },
+ mssql => {
+ hri => [{ date => '2010-12-14 12:12:12.000' }, { date => '2011-12-14 12:12:12.000' }],
+ },
+ oracle => {
+ hri => [{ date => '2010-12-14 12:12:12.000000' }, { date => '2011-12-14 12:12:12.000000' }],
},
},
}
is_same_sql_bind(
$r,
- "(SELECT $db_test->{select} FROM event me WHERE $db_test->{where})",
- $db_test->{bind},
+ '(SELECT ' . ($db_test->{select} || $t->{select_sql}) . ' FROM event me WHERE ' . ($db_test->{where} || $t->{where}) . ')',
+ $db_test->{bind} || $t->{bind},
($t->{msg} ? "$t->{msg} ($db)" : ())
);
SKIP: {
- if (my $hri = $db_test->{hri}) {
+ if (my $hri = $db_test->{hri} || $t->{hri}) {
skip "Cannot test database we are not connected to ($db)", 1 unless $dbs_to_test{$db};
skip $db_test->{skip} . " ($db)", 1 if $db_test->{skip};
projects / dbsrgits/DBIx-Class.git / commitdiff