Make DateTime objects work for all ops

diff --git a/lib/DBIx/Class/SQLMaker/DateOps.pm b/lib/DBIx/Class/SQLMaker/DateOps.pm
index 8029b17..a955385 100644 (file)
--- a/lib/DBIx/Class/SQLMaker/DateOps.pm
+++ b/lib/DBIx/Class/SQLMaker/DateOps.pm
@@ -181,7 +181,13 @@ sub _dt_arg_non_date_transform {
   return ($sql, @bind);
 }
 
-sub _where_op_ADD_DATETIME_transform_args { $_[0]->_dt_arg_non_date_transform($_[2], $_[3]) }
+sub _where_op_ADD_DATETIME_transform_args {
+  if ($_[1] == 0) {
+     $_[0]->_dt_arg_non_date_transform($_[2], $_[3])
+  } else {
+     $_[0]->_dt_arg_transform($_[2], $_[3])
+  }
+}
 
 sub _where_op_ADD_DATETIME {
   my ($self) = @_;
@@ -206,11 +212,10 @@ sub _where_op_ADD_DATETIME {
 
   my (@all_sql, @all_bind);
   my $i = 0;
-  foreach my $val ($self->_reorder_add_datetime_vars(@rest)) {
-    my ($sql, @bind) = $self->_where_op_ADD_DATETIME_transform_args($i, $k, $val);
+  foreach my $val ($self->_reorder_add_datetime_vars(map [ $i++, $_ ], @rest)) {
+    my ($sql, @bind) = $self->_where_op_ADD_DATETIME_transform_args($val->[0], $k, $val->[1]);
     push @all_sql, $sql;
     push @all_bind, @bind;
-    $i++;
   }
 
   return $self->_datetime_add_sql($part, $all_sql[0], $all_sql[1]), @all_bind
@@ -247,7 +252,7 @@ sub _where_op_DIFF_DATETIME {
   @val = $self->_reorder_diff_datetime_vars(@val);
   my (@all_sql, @all_bind);
   foreach my $val (@val) {
-    my ($sql, @bind) = $self->_dt_arg_non_date_transform($k, $val);
+    my ($sql, @bind) = $self->_dt_arg_transform($k, $val);
     push @all_sql, $sql;
     push @all_bind, @bind;
   }
@@ -270,7 +275,7 @@ sub _where_op_CIRCA_DATETIME {
      $val = $_[3];
   }
 
-  my ($sql, @bind) = $self->_dt_arg_non_date_transform($k, $val);
+  my ($sql, @bind) = $self->_dt_arg_transform($k, $val);
 
   my ($equal, $before, $after) = $op =~ /dt_(on_or_)?(before)?(after)?/;
   my $sym = $before

diff --git a/lib/DBIx/Class/Storage/DBI/Pg.pm b/lib/DBIx/Class/Storage/DBI/Pg.pm
index 5c1e9b4..59b3771 100644 (file)
--- a/lib/DBIx/Class/Storage/DBI/Pg.pm
+++ b/lib/DBIx/Class/Storage/DBI/Pg.pm
@@ -183,6 +183,15 @@ sub bind_attribute_by_data_type {
     carp_once( __PACKAGE__.': DBD::Pg 2.9.2 or greater is strongly recommended'
       . "for BYTEA column support.\n" );
   }
+  my $bind_attributes = {
+    bytea => { pg_type => DBD::Pg::PG_BYTEA() },
+    blob  => { pg_type => DBD::Pg::PG_BYTEA() },
+    timestamp  => { pg_type => DBD::Pg::PG_TIMESTAMP() },
+  };
+
+  if( defined $bind_attributes->{$data_type} ) {
+    return $bind_attributes->{$data_type};
+  }
 
   # cache the result of _is_binary_lob_type
   if (!exists $type_cache->{$data_type}) {

diff --git a/t/sqlmaker/op_dt.t b/t/sqlmaker/op_dt.t
index 9d85043..5b28863 100644 (file)
--- a/t/sqlmaker/op_dt.t
+++ b/t/sqlmaker/op_dt.t
@@ -185,6 +185,21 @@ sub hri_thing {
 
 my $date2 = $date->clone->set_day(16);
 
+my $date3 = DateTime->new(
+   year => 2011,
+   month => 12,
+   day   => 14,
+   hour  => 12,
+   minute => 12,
+   second => 12,
+);
+
+my $date4 = DateTime->new(
+   year => 2010,
+   month => 12,
+   day   => 12,
+);
+
 ## test format:
 ##   search => { dbic_search_code/params }
 ##   rdbms_name => literal_sql
@@ -285,6 +300,40 @@ my @tests = (
     },
   },
 
+{
+    msg    => '-dt_get (year, month) works with DateTime obj',
+    search => { 'me.id' => 1 },
+    select => [ [ -dt_get => [[qw(year month)], $date ] ] ],
+    as     => [ qw(year month) ],
+    where  => "me.id = ?",
+    bind   => [
+      [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
+      [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
+      [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]
+    ],
+    hri    => [{ year => 2010, month => 12 }],
+    mssql => {
+      select => "DATEPART(year, ?), DATEPART(month, ?)",
+       bind   => [
+         [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
+         [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
+         [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]
+       ],
+    },
+    mysql => {
+      select => "EXTRACT(YEAR FROM ?), EXTRACT(MONTH FROM ?)",
+    },
+    sqlite => {
+      select => "STRFTIME('%Y', ?), STRFTIME('%m', ?)",
+    },
+    postgres => {
+      select => "date_part('year', ?), date_part('month', ?)",
+    },
+    oracle => {
+      select => "EXTRACT(year FROM ?), EXTRACT(month FROM ?)",
+    },
+  },
+
   {
     msg    => '-dt_month works',
     search => { 'me.id' => 1 },
@@ -311,6 +360,32 @@ my @tests = (
   },
 
   {
+    msg    => '-dt_month works with DateTime obj',
+    search => { 'me.id' => 1 },
+    select => [ [ -dt_month => $date ] ],
+    as     => [ 'month' ],
+    where  => "me.id = ?",
+    bind   => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+    hri    => [{ month => 12 }],
+    sqlite => {
+      select   => "STRFTIME('%m', ?)",
+    },
+    mssql => {
+      bind   => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+      select => "DATEPART(month, ?)",
+    },
+    mysql => {
+      select => "EXTRACT(MONTH FROM ?)",
+    },
+    postgres => {
+      select => "date_part('month', ?)",
+    },
+    oracle => {
+      select => "EXTRACT(month FROM ?)",
+    },
+  },
+
+  {
     msg    => '-dt_day works',
     search => { 'me.id' => 1 },
     select => [ [ -dt_day => { -ident => 'me.created_on' } ] ],
@@ -336,6 +411,32 @@ my @tests = (
   },
 
   {
+    msg    => '-dt_day works with DateTime obj',
+    search => { 'me.id' => 1 },
+    select => [ [ -dt_day => $date ] ],
+    as     => [ 'day' ],
+    where  => "me.id = ?",
+    bind   => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+    hri    => [{ day => 14 }],
+    sqlite => {
+      select   => "STRFTIME('%d', ?)",
+    },
+    mssql => {
+      select => "DATEPART(day, ?)",
+      bind   => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+    },
+    mysql => {
+      select => "EXTRACT(DAY FROM ?)",
+    },
+    postgres => {
+      select => "date_part('day', ?)",
+    },
+    oracle => {
+      select => "EXTRACT(day FROM ?)",
+    },
+  },
+
+  {
     msg    => '-dt_hour works',
     search => { 'me.id' => 1 },
     select => [ [ -dt_hour => { -ident => 'me.created_on' } ] ],
@@ -361,6 +462,32 @@ my @tests = (
   },
 
   {
+    msg    => '-dt_hour works with DateTime obj',
+    search => { 'me.id' => 1 },
+    select => [ [ -dt_hour => $date ] ],
+    as     => [ 'hour' ],
+    where  => "me.id = ?",
+    bind   => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+    hri    => [{ hour => 12 }],
+    sqlite => {
+      select   => "STRFTIME('%H', ?)",
+    },
+    mssql => {
+      select => "DATEPART(hour, ?)",
+      bind   => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+    },
+    mysql => {
+      select => "EXTRACT(HOUR FROM ?)",
+    },
+    postgres => {
+      select => "date_part('hour', ?)",
+    },
+    oracle => {
+      select => "EXTRACT(hour FROM ?)",
+    },
+  },
+
+  {
     msg    => '-dt_minute works',
     search => { 'me.id' => 1 },
     select => [ [ -dt_minute => { -ident => 'me.created_on' } ] ],
@@ -386,6 +513,32 @@ my @tests = (
   },
 
   {
+    msg    => '-dt_minute works with DateTime obj',
+    search => { 'me.id' => 1 },
+    select => [ [ -dt_minute => $date ] ],
+    as     => [ 'minute' ],
+    where  => "me.id = ?",
+    bind   => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+    hri    => [{ minute => 12 }],
+    sqlite => {
+      select   => "STRFTIME('%M', ?)",
+    },
+    mssql => {
+      select => "DATEPART(minute, ?)",
+      bind   => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+    },
+    mysql => {
+      select => "EXTRACT(MINUTE FROM ?)",
+    },
+    postgres => {
+      select => "date_part('minute', ?)",
+    },
+    oracle => {
+      select => "EXTRACT(minute FROM ?)",
+    },
+  },
+
+  {
     msg    => '-dt_second works',
     search => { 'me.id' => 1 },
     select => [ [ -dt_second => { -ident => 'me.created_on' } ] ],
@@ -411,6 +564,32 @@ my @tests = (
   },
 
   {
+    msg    => '-dt_second works with DateTime obj',
+    search => { 'me.id' => 1 },
+    select => [ [ -dt_second => $date ] ],
+    as     => [ 'second' ],
+    where  => "me.id = ?",
+    bind   => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+    hri    => [{ second => 12 }],
+    sqlite => {
+      select   => "STRFTIME('%S', ?)",
+    },
+    mssql => {
+      select => "DATEPART(second, ?)",
+      bind   => [[{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
+    },
+    mysql => {
+      select => "EXTRACT(SECOND FROM ?)",
+    },
+    postgres => {
+      select => "date_part('second', ?)",
+    },
+    oracle => {
+      select => "EXTRACT(second FROM ?)",
+    },
+  },
+
+  {
     msg    => '-dt_diff (second) works',
     search => { 'me.id' => 2 },
     select => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
@@ -436,6 +615,45 @@ my @tests = (
   },
 
   {
+    msg    => '-dt_diff (second) works with DateTime objs',
+    search => { 'me.id' => 2 },
+    select => [ [ -dt_diff => [second => $date2, $date ] ] ],
+    as     => [ 'sec_diff' ],
+    where  => "me.id = ?",
+    bind   => [
+      [{sqlt_datatype => 'timestamp'} => '2010-12-16 12:12:12' ],
+      [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
+      [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+    ],
+    hri    => [{ sec_diff => 2*24*60*60 }],
+    sqlite => {
+      select   => "(STRFTIME('%s', ?) - STRFTIME('%s', ?))",
+    },
+    mssql => {
+      select   => "DATEDIFF(second, ?, ?)",
+      bind   => [
+        [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
+        [{sqlt_datatype => 'timestamp'} => '2010-12-16 12:12:12.000' ],
+        [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+      ],
+    },
+    mysql => {
+      select   => "TIMESTAMPDIFF(SECOND, ?, ?)",
+      bind   => [
+        [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
+        [{sqlt_datatype => 'timestamp'} => '2010-12-16 12:12:12' ],
+        [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+      ],
+    },
+    postgres => {
+      select   => "date_part('EPOCH', ?) - date_part('EPOCH', ?)",
+    },
+    oracle => {
+      select   => "TRUNC(MONTHS_BETWEEN(?, ?) * 31 * 24 * 60 * 60)",
+    },
+  },
+
+  {
     msg    => '-dt_diff (day) works',
     search => { 'me.id' => 2 },
     select => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
@@ -461,6 +679,45 @@ my @tests = (
   },
 
   {
+    msg    => '-dt_diff (day) works with DateTime objs',
+    search => { 'me.id' => 2 },
+    select => [ [ -dt_diff => [day => $date2, $date ] ] ],
+    as     => [ 'day_diff' ],
+    where  => "me.id = ?",
+    bind   => [
+      [{sqlt_datatype => 'timestamp'} => '2010-12-16 12:12:12' ],
+      [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
+      [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+    ],
+    hri    => [{ day_diff => 2 }],
+    sqlite => {
+      select   => "(JULIANDAY(?) - JULIANDAY(?))",
+    },
+    mssql => {
+      bind   => [
+        [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
+        [{sqlt_datatype => 'timestamp'} => '2010-12-16 12:12:12.000' ],
+        [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+      ],
+      select   => "DATEDIFF(dayofyear, ?, ?)",
+    },
+    mysql => {
+      bind   => [
+        [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
+        [{sqlt_datatype => 'timestamp'} => '2010-12-16 12:12:12' ],
+        [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+      ],
+      select   => "TIMESTAMPDIFF(DAY, ?, ?)",
+    },
+    postgres => {
+      select   => "date_part('DAY', ?) - date_part('DAY', ?)",
+    },
+    oracle => {
+      select   => "TRUNC(MONTHS_BETWEEN(?, ?) * 31)",
+    },
+  },
+
+  {
     msg    => '-dt_diff (year) works',
     search => { 'me.id' => 2 },
     select => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ],
@@ -486,6 +743,45 @@ my @tests = (
   },
 
   {
+    msg    => '-dt_diff (year) works with DateTime objs',
+    search => { 'me.id' => 2 },
+    select => [ [ -dt_diff => [year => $date4, $date3 ] ] ],
+    as     => [ 'year' ],
+    where  => "me.id = ?",
+    bind   => [
+      [{sqlt_datatype => 'timestamp'} => '2010-12-12 00:00:00' ],
+      [{sqlt_datatype => 'timestamp'} => '2011-12-14 12:12:12' ],
+      [{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, ?, ?)",
+      bind   => [
+        [{sqlt_datatype => 'timestamp'} => '2011-12-14 12:12:12.000' ],
+        [{sqlt_datatype => 'timestamp'} => '2010-12-12 00:00:00.000' ],
+        [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+      ],
+    },
+    mysql => {
+      select   => "TIMESTAMPDIFF(YEAR, ?, ?)",
+      bind   => [
+        [{sqlt_datatype => 'timestamp'} => '2011-12-14 12:12:12' ],
+        [{sqlt_datatype => 'timestamp'} => '2010-12-12 00:00:00' ],
+        [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+      ],
+    },
+    postgres => {
+      select   => "date_part('YEAR', ?) - date_part('YEAR', ?)",
+    },
+    oracle => {
+      select   => "TRUNC(MONTHS_BETWEEN(?, ?) / 12)",
+    },
+  },
+
+  {
     msg    => '-dt_add (year) works',
     search => { 'me.id' => 2 },
     select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ],
@@ -514,6 +810,42 @@ my @tests = (
   },
 
   {
+    msg    => '-dt_add (year) works with DateTime obj',
+    search => { 'me.id' => 2 },
+    select => [ [ -dt_add => [year => 3, $date ] ] ],
+    as     => [ 'date' ],
+    where  => "me.id = ?",
+    bind   => [
+      [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
+      [unknown_col, 3],
+      [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+    ],
+    hri    => [{ date => '2013-12-14 12:12:12' }],
+    sqlite => {
+      select => "(datetime(?, ? || ' years'))",
+    },
+    mssql => {
+      select => "(DATEADD(year, CAST(? AS INTEGER), ?))",
+      bind   => [
+         [{sqlt_datatype => 'integer'}, 3],
+         [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
+         [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+      ],
+      hri    => [{ date => '2013-12-14 12:12:12.000' }],
+    },
+    mysql => {
+      select => "DATE_ADD(?, INTERVAL ? YEAR)",
+    },
+    postgres => {
+      select => "(? + ? * interval '1 YEAR')",
+    },
+    oracle => {
+      select => "(? + NUMTOYMINTERVAL(?, 'year'))",
+      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' } ] ] ],
@@ -532,7 +864,6 @@ my @tests = (
     postgres => {
       select => "(me.created_on + ? * interval '1 MONTH')",
     },
-
     mysql => {
       select => "DATE_ADD(me.created_on, INTERVAL ? MONTH)",
     },
@@ -543,6 +874,42 @@ my @tests = (
   },
 
   {
+    msg    => '-dt_add (month) works with DateTime obj',
+    search => { 'me.id' => 2 },
+    select => [ [ -dt_add => [month => 3, $date ] ] ],
+    as     => [ 'date' ],
+    where  => "me.id = ?",
+    bind   => [
+      [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
+      [unknown_col, 3],
+      [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+    ],
+    hri    => [{ date => '2011-03-14 12:12:12' }],
+    sqlite => {
+      select => "(datetime(?, ? || ' months'))",
+    },
+    mssql => {
+      select => "(DATEADD(month, CAST(? AS INTEGER), ?))",
+      bind   => [
+         [{sqlt_datatype => 'integer'}, 3],
+         [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
+         [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+      ],
+      hri    => [{ date => '2011-03-14 12:12:12.000' }],
+    },
+    postgres => {
+      select => "(? + ? * interval '1 MONTH')",
+    },
+    mysql => {
+      select => "DATE_ADD(?, INTERVAL ? MONTH)",
+    },
+    oracle => {
+      select => "(? + NUMTOYMINTERVAL(?, 'month'))",
+      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' } ] ] ],
@@ -571,6 +938,42 @@ my @tests = (
   },
 
   {
+    msg    => '-dt_add (day) works with DateTime obj',
+    search => { 'me.id' => 2 },
+    select => [ [ -dt_add => [day => 3, $date ] ] ],
+    as     => [ 'date' ],
+    where  => "me.id = ?",
+    bind   => [
+      [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
+      [unknown_col, 3],
+      [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+    ],
+    hri    => [{ date => '2010-12-17 12:12:12' }],
+    sqlite => {
+      select => "(datetime(?, ? || ' days'))",
+    },
+    mssql => {
+      select => "(DATEADD(dayofyear, CAST(? AS INTEGER), ?))",
+      bind   => [
+         [{sqlt_datatype => 'integer'}, 3],
+         [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
+         [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+      ],
+      hri    => [{ date => '2010-12-17 12:12:12.000' }],
+    },
+    postgres => {
+      select => "(? + ? * interval '1 DAY')",
+    },
+    mysql => {
+      select => "DATE_ADD(?, INTERVAL ? DAY)",
+    },
+    oracle => {
+      select => "(? + NUMTODSINTERVAL(?, 'day'))",
+      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' } ] ] ],
@@ -599,6 +1002,42 @@ my @tests = (
   },
 
   {
+    msg    => '-dt_add (hour) works with DateTime obj',
+    search => { 'me.id' => 2 },
+    select => [ [ -dt_add => [hour => 3, $date ] ] ],
+    as     => [ 'date' ],
+    where  => "me.id = ?",
+    bind   => [
+      [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
+      [unknown_col, 3],
+      [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+    ],
+    hri    => [{ date => '2010-12-14 15:12:12' }],
+    sqlite => {
+      select => "(datetime(?, ? || ' hours'))",
+    },
+    mssql => {
+      select => "(DATEADD(hour, CAST(? AS INTEGER), ?))",
+      bind   => [
+         [{sqlt_datatype => 'integer'}, 3],
+         [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
+         [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+      ],
+      hri    => [{ date => '2010-12-14 15:12:12.000' }],
+    },
+    postgres => {
+      select => "(? + ? * interval '1 HOUR')",
+    },
+    mysql => {
+      select => "DATE_ADD(?, INTERVAL ? HOUR)",
+    },
+    oracle => {
+      select => "(? + NUMTODSINTERVAL(?, 'hour'))",
+      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' } ] ] ],
@@ -627,6 +1066,42 @@ my @tests = (
   },
 
   {
+    msg    => '-dt_add (minute) works with DateTime obj',
+    search => { 'me.id' => 2 },
+    select => [ [ -dt_add => [minute => 3, $date ] ] ],
+    as     => [ 'date' ],
+    where  => "me.id = ?",
+    bind   => [
+      [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
+      [unknown_col, 3],
+      [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+    ],
+    hri    => [{ date => '2010-12-14 12:15:12' }],
+    sqlite => {
+      select => "(datetime(?, ? || ' minutes'))",
+    },
+    mssql => {
+      select => "(DATEADD(minute, CAST(? AS INTEGER), ?))",
+      bind   => [
+         [{sqlt_datatype => 'integer'}, 3],
+         [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
+         [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+      ],
+      hri    => [{ date => '2010-12-14 12:15:12.000' }],
+    },
+    postgres => {
+      select => "(? + ? * interval '1 MINUTE')",
+    },
+    mysql => {
+      select => "DATE_ADD(?, INTERVAL ? MINUTE)",
+    },
+    oracle => {
+      select => "(? + NUMTODSINTERVAL(?, 'minute'))",
+      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' } ] ] ],
@@ -655,6 +1130,42 @@ my @tests = (
   },
 
   {
+    msg    => '-dt_add (second) works with DateTime obj',
+    search => { 'me.id' => 2 },
+    select => [ [ -dt_add => [second => 3, $date ] ] ],
+    as     => [ 'date' ],
+    where  => "me.id = ?",
+    bind   => [
+      [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
+      [unknown_col, 3],
+      [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+    ],
+    hri    => [{ date => '2010-12-14 12:12:15' }],
+    sqlite => {
+      select => "(datetime(?, ? || ' seconds'))",
+    },
+    mssql => {
+      select => "(DATEADD(second, CAST(? AS INTEGER), ?))",
+      bind   => [
+         [{sqlt_datatype => 'integer'}, 3],
+         [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
+         [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]
+      ],
+      hri    => [{ date => '2010-12-14 12:12:15.000' }],
+    },
+    postgres => {
+      select => "(? + ? * interval '1 SECOND')",
+    },
+    mysql => {
+      select => "DATE_ADD(?, INTERVAL ? SECOND)",
+    },
+    oracle => {
+      select => "(? + NUMTODSINTERVAL(?, 'second'))",
+      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' } ] } ] ] ],
@@ -683,6 +1194,44 @@ my @tests = (
   },
 
   {
+    msg    => 'nested -dt_add works with DateTime obj',
+    search => { 'me.id' => 2 },
+    select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, $date ] } ] ] ],
+    as     => [ 'date' ],
+    where  => "me.id = ?",
+    hri    => [{ date => '2010-12-15 12:12:15' }],
+    bind   => [
+      [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12' ],
+      [unknown_col, 1],
+      [unknown_col, 3 ],
+      [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]
+    ],
+    sqlite => {
+      select   => "(datetime((datetime(?, ? || ' days')), ? || ' seconds'))",
+    },
+    mssql => {
+      select => "(DATEADD(second, CAST(? AS INTEGER), (DATEADD(dayofyear, CAST(? AS INTEGER), ?))))",
+    bind   => [
+      [{sqlt_datatype => 'integer'}, 3 ],
+      [{sqlt_datatype => 'integer'}, 1],
+      [{sqlt_datatype => 'timestamp'} => '2010-12-14 12:12:12.000' ],
+      [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]
+    ],
+      hri    => [{ date => '2010-12-15 12:12:15.000' }],
+    },
+     postgres => {
+      select => "((? + ? * interval '1 DAY') + ? * interval '1 SECOND')",
+    },
+    mysql => {
+      select => "DATE_ADD(DATE_ADD(?, INTERVAL ? DAY), INTERVAL ? SECOND)",
+    },
+    oracle => {
+      select => "((? + NUMTODSINTERVAL(?, 'day')) + NUMTODSINTERVAL(?, 'second'))",
+      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' } },
     select   => [ [ -ident => 'me.created_on' ] ],
@@ -703,6 +1252,27 @@ my @tests = (
   },
 
   {
+    msg      => '-dt_before works with DateTime obj',
+    search   => { 'me.created_on' => { -dt_before => $date3 } },
+    select   => [ [ -ident => 'me.created_on' ] ],
+    as       => [ 'date' ],
+    select_sql   => "me.created_on",
+    where    => "me.created_on < ?",
+    bind     => [[{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' }],
+      bind => [[{sqlt_datatype => 'timestamp' }, '2011-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' } },
     select   => [ [ -ident => 'me.created_on' ] ],
@@ -723,6 +1293,27 @@ my @tests = (
   },
 
   {
+    msg      => '-dt_on_or_before works with DateTime obj',
+    search   => { 'me.created_on' => { -dt_on_or_before => $date3 } },
+    select   => [ [ -ident => 'me.created_on' ] ],
+    as       => [ 'date' ],
+    select_sql   => "me.created_on",
+    where    => "me.created_on <= ?",
+    bind     => [[{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' }],
+      bind => [[{sqlt_datatype => 'timestamp' }, '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' } },
     select   => [ [ -ident => 'me.created_on' ] ],
@@ -743,6 +1334,27 @@ my @tests = (
   },
 
   {
+    msg      => '-dt_after works with DateTime obj',
+    search   => { 'me.created_on' => { -dt_after => $date } },
+    select   => [ [ -ident => 'me.created_on' ] ],
+    as       => [ 'date' ],
+    select_sql => "me.created_on",
+    where    => "me.created_on > ?",
+    bind     => [[{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' }],
+      bind     => [[{sqlt_datatype => 'timestamp' }, '2010-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' } },
     select   => [ [ -ident => 'me.created_on' ] ],
@@ -762,6 +1374,27 @@ my @tests = (
     },
   },
 
+  {
+    msg      => '-dt_on_or_after works with DateTime obj',
+    search   => { 'me.created_on' => { -dt_on_or_after => $date } },
+    select   => [ [ -ident => 'me.created_on' ] ],
+    as       => [ 'date' ],
+    select_sql => "me.created_on",
+    where    => "me.created_on >= ?",
+    bind     => [[{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' }],
+      bind => [[{sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12.000']],
+    },
+    oracle   => {
+      hri => [{ date => '2010-12-14 12:12:12.000000' }, { date => '2011-12-14 12:12:12.000000' }],
+    },
+  },
+
 );
 
 for my $t (@tests) {