FROM_UNIXTIME and NOW timezone conflict

I wasn't aware of the change in FROM_UNIXTIME. That's a critical issue since the goal throughout the Date module is to use php and sql functions that will not do any timezone conversion so that the correct timezone conversion can be controlled based on the choices made when setting up the field. The automatic timezone conversions are way too unreliable (i.e. even if your choice is to use the site timezone, a sql conversion will convert to the timezone of the sql server but your site timezone might be something different)

This will take some thought, especially since behavior is not constant across different versions of the same OS. I need to find a method that does no timezone conversion on its own.

A fix was just committed to all branches. I am going to try the method used by Views to force the mysql server to use GMT. I don't have an installation with the right mysql version to test this but it looks like it should work. If not, please re-open the issue.

You're right that it wasn't the right fix. My intention is to find a way to remove any timezone adjustment the server might make to any value, then manually adjust by the correct offset value, so here's what I'm now thinking of using (still doing some testing on it). I added a function that will try to determine the amount of adjustment the server is making by default so I can adjust it out when necessary. Also, it looks to me like all versions of MYSQL (and I believe postgres) are automatically adjusting NOW() and TIMESTAMP() values by the server timezone, so I think all of them need to be adjusted back before applying the correct offset.

/**
 * Server timezone adjustment.
 *
 * Used to compute default timezone conversions made by SQL server
 * so server adjustment can be replaced with correct timezone adjustment values.
 *
 *  @return amount in seconds that server adjusts for timezone.
 */
function date_server_zone_adj() {
  static $server_zone_adj;
  if (!isset($server_zone_adj)) {
    switch ($db_type) {
      case ('mysql'):
      case ('mysqli'):
        if ($GLOBALS['db_type'] == 'mysqli' || version_compare(mysql_get_server_info(), '4.1.3', '>=')) {
          // Newest MYSQL versions allow us to set the server to GMT to eliminate adjustments.
          db_query("SET @@session.time_zone = '+00:00'");
          $server_zone_adj = 0;
        }
        elseif (version_compare(mysql_get_server_info(), '4.1.1', '>=')) {
          // In 4.1.1+ we can use difference between NOW() and UTC_TIMESTAMP().
          $server_zone_adj = db_result(db_query("SELECT NOW() - UTC_TIMESTAMP()"));
        }
        else {
          // There is no MYSQL query to produce the adjustment amount in older
          // MYSQL versions, so estimate it using the php timezone adjustment.
          $server_zone_adj = date('Z');
        }
        break;
      case ('postgres'):
        // The TIMEZONE function returns the timezone adjustment in seconds.
        $server_zone_adj = db_result(db_query("TIMEZONE"));
        break;
    }
    // If no value got set by this point,
    // fall back to using php timezone adjustment as an estimate.
    if (!isset($server_zone_adj)) {
      $server_zone_adj = date('Z');
    }
  }
  return $server_zone_adj;
}

/**
 *  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;

  // NOW() is timezone-adjusted by OS, so remove server adj,
  // then apply offset to compute NOW() in the correct timezone.
  if ($date_type == 'NOW' || $field == 'NOW()') {
    switch ($db_type) {
      case ('mysql'):
      case ('mysqli'):
        $field = "(NOW() + INTERVAL (-". date_server_zone_adj() ." $offset_op $offset) SECOND)";
        break;
      case ('postgres'):
        $field = "(NOW() + INTERVAL (-". date_server_zone_adj() ." $offset_op $offset) SECONDS)";
        break;
     }
  }
  elseif ($date_type == 'int' && $field) {
    // Convert integer field value to native date format.
    // FROM_UNIXTIME() and TIMESTAMP() are timezone adjusted,
    // so remove server adj, correct offset will get added back in later step.
    switch ($db_type) {
      case ('mysql'):
      case ('mysqli'):
        $field = "($field - INTERVAL ". date_server_zone_adj() ." SECOND)";
        break;
      case ('postgres'):
        $field = "($field - INTERVAL ". date_server_zone_adj() ." SECONDS)";
        break;
    }
    switch ($db_type) {
      case ('mysql'):
      case ('mysqli'):
        $field = "FROM_UNIXTIME($field)";
        break;
      case ('postgres'):
        $field = "TIMESTAMP($field::ABSTIME::INT4)";
        break;
    }
  }
  elseif ($date_type == 'iso' && $field) {
    // Get rid of the 'T' in ISO dates to match native date field.
    // This makes it possible to use SQL date functions on the value.
    $field = " REPLACE($field,'T',' ')";
  }

  // Now apply requested offset to the adjusted field.
  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)";
    }
  }
}

I've done some more checking and fixing and have committed something similar to the code above. It should at least be no worse than the current code and hopefully is now correctly adjusting timezone values.