I need show a postgres timestamp field in Views 7. The field is in my database, and i've used the hook_views_data() for do it.

I don't want convert the postgres timestamp to unix format, I want to show the field like timestamp, like postgres.

I'm shure that this has been done. Didn't?

Comments

abel_osorio’s picture

I did two views handlers: ldbi_handler_field_pg_timestamp and ldbi_handler_filter_pg_timestamp. (My module's name is ldbi).

Ok, the code:

ldbi_handler_field_pg_timestamp.inc


/**
 * $Id: ldbi_handler_field_pg_timestamp.inc,v 1.1 2012-10-26 00:35:37 aosorio Exp $
 **
 * @file
 * Definición de ldbi_handler_field_pg_timestamp.
 */

/**
 * Un handler que provee la presentación apropiada para timestamps de postgres.
 */
class ldbi_handler_field_pg_timestamp extends views_handler_field_date {

  function render($values) {
    $value = strtotime($this->get_value($values));
    $format = $this->options['date_format'];
    if (in_array($format, array('custom', 'raw time ago', 'time ago',
        'raw time hence', 'time hence', 'raw time span', 'time span',
        'raw time span', 'inverse time span', 'time span'))
       ) {
      $custom_format = $this->options['custom_date_format'];
    }

    if ($value) {
      $timezone = !empty($this->options['timezone'])
                    ? $this->options['timezone']
                    : NULL;
      $time_diff = REQUEST_TIME - $value; // will be positive for a datetime in
                                          // the past (ago), and negative for
                                          // a datetime in the future (hence)
      switch ($format) {
        case 'raw time ago':
          return format_interval($time_diff,
            is_numeric($custom_format) ? $custom_format : 2);
        case 'time ago':
          return t('%time ago',
            array('%time' => format_interval($time_diff,
              is_numeric($custom_format) ? $custom_format : 2)));
        case 'raw time hence':
          return format_interval(-$time_diff,
            is_numeric($custom_format) ? $custom_format : 2);
        case 'time hence':
          return t('%time hence',
            array('%time' => format_interval(-$time_diff,
              is_numeric($custom_format) ? $custom_format : 2)));
        case 'raw time span':
          return ($time_diff < 0 ? '-' : '') . format_interval(abs($time_diff),
            is_numeric($custom_format) ? $custom_format : 2);
        case 'inverse time span':
          return ($time_diff > 0 ? '-' : '') . format_interval(abs($time_diff),
            is_numeric($custom_format) ? $custom_format : 2);
        case 'time span':
          return t(($time_diff < 0 ? '%time hence' : '%time ago'),
            array('%time' => format_interval(abs($time_diff),
              is_numeric($custom_format) ? $custom_format : 2)));
        case 'custom':
          if ($custom_format == 'r') {
            return format_date($value, $format, $custom_format, $timezone,
              'en');
          }
          return format_date($value, $format, $custom_format, $timezone);
        default:
          return format_date($value, $format, '', $timezone);
      }
    }
  }
}

ldbi_handler_filter_pg_timestamp.inc


/**
 * $Id: ldbi_handler_filter_pg_timestamp.inc,v 1.2 2012-10-26 22:35:43 aosorio Exp $
 **
 * Definición de ldbi_handler_filter_pg_timestamp.
 */

/**
 * Filtro para manejar fechas almacenadas como timestamps de postgres.
 */
class ldbi_handler_filter_pg_timestamp extends views_handler_filter_date {
  private $pg_timestamp_format = 'Y-m-d H:i:s';

  /**
   * Add a type selector to the value form
   */
  function value_form(&$form, &$form_state) {
    if (empty($form_state['exposed'])) {
      $form['value']['type'] = array(
        '#type' => 'radios',
        '#title' => t('Value type'),
        '#options' => array(
          'date' => t('A date in any machine readable format. CCYY-MM-DD ' .
                      'HH:MM:SS is preferred.'),
          'offset' => t('An offset from the current time such as "!example1" ' .
                        'or "!example2"', array(
            '!example1' => '+1 day',
            '!example2' => '-2 hours -30 minutes')
          ),
        ),
        '#default_value' => !empty($this->value['type'])
                              ? $this->value['type']
                              : 'date',
      );
      parent::value_form($form, $form_state);
    } else {
      if ($this->value['type'] == 'date') {
        $form['value'] = array(
          '#type' => 'date_popup',
          '#date_format' => 'd-m-Y',
          '#date_label_position' => 'within',
          '#default_value' => !empty($this->value) ? $this->value : NULL
        );
      }
    }
  }

  function op_between($field) {
    $a = date($this->pg_timestamp_format, strtotime($this->value['min'], 0));
    $b = date($this->pg_timestamp_format, strtotime($this->value['max'], 0));

    if ($this->value['type'] == 'offset') {
      $a = '***CURRENT_TIME***' . sprintf('%+d', $a); // keep sign
      $b = '***CURRENT_TIME***' . sprintf('%+d', $b); // keep sign
    }

    // This is safe because we are manually scrubbing the values.
    // It is necessary to do it this way because $a and $b are formulas when
    // using an offset.
    $operator = strtoupper($this->operator);
    $this->query->add_where_expression($this->options['group'],
      "$field $operator '$a' AND '$b'");
  }

  function op_simple($field) {
    $value = date($this->pg_timestamp_format, strtotime($this->value['value'],
      0));
    if (!empty($this->value['type']) && $this->value['type'] == 'offset') {
      $value = '***CURRENT_TIME***' . sprintf('%+d', $value); // keep sign
    }
    // This is safe because we are manually scrubbing the value.
    // It is necessary to do it this way because $value is a formula when using
    // an offset.
    $this->query->add_where_expression($this->options['group'],
      "$field $this->operator '$value'");
  }
}

So, that's all. I hope this is helpful.

Greetings!