sql date hour conversion

Question

I have SQL like this:

`table1`.`DateField` >= DATE_SUB(NOW(), INTERVAL {$days} DAY   

My table1.DateField is in the format of 2010-03-10 10:05:50.

The value of {$days} could be 4 hours or 3 days. How can I make the above sql smart enough to know that? Should I use

`table1`.`DateField` >= DATE_SUB(NOW(), INTERVAL {$days} DAY_HOUR

Answer 1

You can't, you have to work around that:

"`table1`.`DateField` >= DATE_SUB(NOW(),
    INTERVAL {$hoursOrDays} ".($isDays?"DAY":"HOUR")

Answer 2

"could be 4 hours or 3 days" - and it can't be 4 HOUR or 3 DAY?
Do you have to worry about invalid units or even malicious (user) input?
You could test the given string and "convert" the units to something MySQL understand, e.g.

<?php
$pattern = '!^(\d+) (.+)$!';
$units = array(
  'days'=>'DAY',
  'hours'=>'HOUR',
  'minutes'=>'MINUTE'
);

foreach( array('14 days', '7 hours', '11 hours', '1 or 1=1') as $userinput) {
  echo "\n", $userinput, ': ';
  if ( !preg_match($pattern, $userinput, $m) || !isset($units[$m[2]]) ) {
    echo "error, cause: ...\n";
    continue;
  }

  $sql = sprintf(' ...
    WHERE
      `table1`.`DateField` >= Now() - INTERVAL %d %s', $m[1], $units[$m[2]]
  );
  echo $sql, "\n";
}

prints

14 days:  ...
    WHERE
      `table1`.`DateField` >= Now() - INTERVAL 14 DAY

7 hours:  ...
    WHERE
      `table1`.`DateField` >= Now() - INTERVAL 7 HOUR

11 hours:  ...
    WHERE
      `table1`.`DateField` >= Now() - INTERVAL 11 HOUR

1 or 1=1: error, cause: ...