Postgres compatibility for date functions like from_unixtime

I put this as a feature request, but not sure if that's the right category. We need some cross-database compatibility for SQL date functions in views queries. There is no way to get the proper results without using functions like FROM_UNIXTIME, but that won't work in postgres, and there are a number of other date functions that work differently in postgres. I was just updating the views functions in the date module and came up with the following function to use as a wrapper for SQL date functions that will return equivalent SQL for mysql and postgres. The idea is to call this function from any handlers that are constructing date SQL queries. It greatly simplifies the handlers and keeps all of this logic in one place to make it easier to fix bugs.

I need it in the views handlers for the date module and I'm going to need this in the event views module, and there are other views handlers and operators (node, taxonomy, user) that use date SQL functions, so I got to thinking that maybe it belongs somewhere in views.

The function is designed to work with date fields that are stored either as integers or as iso dates, because we're using iso dates in the date module.

I used the documentation for mysql version 4 and postgres version 7 to try to be sure to avoid functions that only work in later sql versions. I have no way to test the postgres parts of this, so it needs to be reviewed by someone who knows postgres.

I am posting the function I created here so you can get the idea. If you like, I can make it into a patch, but I'm not sure where you would want it (maybe views_query.inc ?)

What do you think of this approach?

/**
 *  Cross-database date SQL wrapper function
 *  allows use of normalized native date functions in both mysql and postgres
 *  designed to be extensible to other databases
 * 
 *  @param $result_type - NOW, DATE, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW, DOY, WEEK
 *  @param $field - the name of the date field to be analyzed
 *  @param $date_type - the type of date field being analyzed, int or iso
 *  @param $offset - timezone offset in seconds, either a value or fieldname
 *  @param $offset_op - the operation to perform on the offset, + or -
 *  @return a SQL statement appropriate for the $db_type
 * 
 *  example:          date_sql('WEEK', 'MYFIELD', 'int', 'MYOFFSETFIELD', '+')
 *  mysql returns:    WEEK(FROM_UNIXTIME(MYFIELD) + INTERVAL MYOFFSETFIELD SECOND, 3)
 *  postgres returns: EXTRACT(WEEK FROM(TIMESTAMP(MYFIELD::ABSTIME::INT4) + INTERVAL MYOFFSETFIELD SECONDS))
 */
function date_sql($result_type, $field, $date_type = 'int', $offset = '', $offset_op = '+') {
  global $db_type;

  if ($date_type == 'NOW') {
    // set up an initial value for now
    $field = 'NOW()';
  } 
  elseif ($date_type == 'int' && $field) {
    // convert integer field value to native date format
    switch ($db_type) {
     case ('mysql'):
     case ('mysqli'):
       $field = "FROM_UNIXTIME($field)";
       break;
     case ('postgres'):
       $field = "TIMESTAMP($field::ABSTIME::INT4)";
       break;
     }
  }
  else {
     // get rid of the 'T' in ISO dates to match native date field
     $field = " REPLACE($field,'T',' ')";
  }
  // adjust date for timezone offset
  if ($offset) {
    switch ($db_type) {
    case ('mysql'):
    case ('mysqli'):
      $field = "$field $offset_op INTERVAL $offset SECOND";
      break;
    case ('postgres'):
      $field = "$field $offset_op INTERVAL $offset SECONDS";
      break;
    }
  }
  // return requested sql
  // note there is no space after FROM to avoid db_rewrite problems
  // see http://drupal.org/node/79904
  switch ($result_type) {
  case ('NOW'):
  case ('DATE'):
    return $field;
  case ('YEAR'):
    return "EXTRACT(YEAR FROM($field))";
  case ('MONTH'):
    return "EXTRACT(MONTH FROM($field))";
  case ('DAY'):
    return "EXTRACT(DAY FROM($field))";
  case ('HOUR'):
    return "EXTRACT(HOUR FROM($field))";
  case ('MINUTE'):
    return "EXTRACT(MINUTE FROM($field))";
  case ('SECOND'):
    return "EXTRACT(SECOND FROM($field))";
  case ('WEEK'):  // ISO week number for date
    switch ($db_type) {
      case ('mysql'):
      case ('mysqli'):
        // WEEK using arg 3 in mysql should return the same value as postgres EXTRACT
        return "WEEK($field, 3)";
      case ('postgres'):
        return "EXTRACT(WEEK FROM($field))";
    }
  case ('DOW'):
    switch ($db_type) {
      case ('mysql'):
      case ('mysqli'):
        // mysql returns 1 for Sunday through 7 for Saturday
        // php date functions and postgres use 0 for Sunday and 6 for Saturday
        return "INTEGER(DAYOFWEEK($field) - 1)";
      case ('postgres'):
        return "DOW($field)";
    }
  case ('DOY'):
    switch ($db_type) {
      case ('mysql'):
      case ('mysqli'):
        return "DAYOFYEAR($field)";
      case ('postgres'):
        return "DOY($field)";
    }
  }
}