⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Unix timestamps



silva96
  • profile picture
  • Member

Posted 20 November 2012 - 16:06 PM

I've Been searching and didn't find the solution so I coded it myself:

Scenario:

Storing dates as unix timestamps (Integer with the seconds passed since 1 january 1970) is very useful when your app needs to do much stuff with dates, you simply operate the date as seconds, so you can go to the future or past adding or substracting seconds.

then display it to the user as

<?php echo date('your desired format',$timestamp);


I came with the problem of using this format with groceryCRUD and solved it by hardcoding my own input types into the core, probably would be nice if you add them so I don't have to write them again If I update the library

here they go:

before, locate the change_list_value function

add this cases inside it:


case 'unixtimestamp':
if(!empty($value))
{
$value=date($this->php_date_format." H:i:s",$value);
}
else{
$value = '';
}
break;
case 'unixdatestamp':
if(!empty($value))
{
$value=date($this->php_date_format,$value);
}
else{
$value = '';
}
break;


find get_field_input function and add this 2 values at the end (inside) the $types_array


'unixdatestamp', 'unixtimestamp'


add this function (probably you want your code to be tidy, so add it right after the get_date_input function)

/**
* Allows you to use unix timestamps as imput and display it as default
* Date formate defined for all dates
* @param type $field_info
* @param type $value
* @return string
* @author Benjamin Silva
*/
protected function get_unixtimestamp_input($field_info,$value)
{
$this->set_css($this->default_css_path.'/ui/simple/'.grocery_CRUD::JQUERY_UI_CSS);
$this->set_css($this->default_css_path.'/jquery_plugins/jquery.ui.datetime.css');
$this->set_css($this->default_css_path.'/jquery_plugins/jquery-ui-timepicker-addon.css');
$this->set_js($this->default_javascript_path.'/jquery_plugins/ui/'.grocery_CRUD::JQUERY_UI_JS);
$this->set_js($this->default_javascript_path.'/jquery_plugins/jquery-ui-timepicker-addon.min.js');

if($this->language !== 'english')
{
include($this->default_config_path.'/language_alias.php');
if(array_key_exists($this->language, $language_alias))
{
$i18n_date_js_file = $this->default_javascript_path.'/jquery_plugins/ui/i18n/datepicker/jquery.ui.datepicker-'.$language_alias[$this->language].'.js';
if(file_exists($i18n_date_js_file))
{
$this->set_js($i18n_date_js_file);
}

$i18n_datetime_js_file = $this->default_javascript_path.'/jquery_plugins/ui/i18n/timepicker/jquery-ui-timepicker-'.$language_alias[$this->language].'.js';
if(file_exists($i18n_datetime_js_file))
{
$this->set_js($i18n_datetime_js_file);
}
}
}

$this->set_js($this->default_javascript_path.'/jquery_plugins/config/jquery-ui-timepicker-addon.config.js');

if(!empty($value)){
$datetime = date($this->php_date_format." H:i:s",$value);
}
else
{
$datetime = '';
}
$input = "<input id='field-{$field_info->name}' name='{$field_info->name}' type='text' value='$datetime' maxlength='19' class='datetime-input' />
<a class='datetime-input-clear' tabindex='-1'>".$this->l('form_button_clear')."</a>
({$this->ui_date_format}) hh:mm:ss";
return $input;
}



/**
* Allows you to use unix timestamps as imput and display it as default
* Date formate defined for all dates
* @author Benjamin Silva
* @param type $field_info
* @param type $value
* @return string
*/
protected function get_unixdatestamp_input($field_info,$value)
{
$this->set_css($this->default_css_path.'/ui/simple/'.grocery_CRUD::JQUERY_UI_CSS);
$this->set_js($this->default_javascript_path.'/jquery_plugins/ui/'.grocery_CRUD::JQUERY_UI_JS);

if($this->language !== 'english')
{
include($this->default_config_path.'/language_alias.php');
if(array_key_exists($this->language, $language_alias))
{
$i18n_date_js_file = $this->default_javascript_path.'/jquery_plugins/ui/i18n/datepicker/jquery.ui.datepicker-'.$language_alias[$this->language].'.js';
if(file_exists($i18n_date_js_file))
{
$this->set_js($i18n_date_js_file);
}
}
}

$this->set_js($this->default_javascript_path.'/jquery_plugins/config/jquery.datepicker.config.js');

if(!empty($value))
{

$date = date($this->php_date_format, $value);
}
else
{
$date = '';
}

$input = "<input id='field-{$field_info->name}' name='{$field_info->name}' type='text' value='$date' maxlength='10' class='datepicker-input' />
<a class='datepicker-input-clear' tabindex='-1'>".$this->l('form_button_clear')."</a> (".$this->ui_date_format.")";
return $input;
}


add a callback_before_insert and callback_before_update to change back to int those fields:


$this->grocery_crud->callback_before_insert(array($this,'from_date_to_unix'));
$this->grocery_crud->callback_before_update(array($this,'from_date_to_unix'));


and this is the callback function


public function from_date_to_unix($array_post){
$date_delimiter='/';//assuming uk-date as defined date format
$posted_dates=array('fecha'=>$array_post['fecha']);//fecha is the name of the field posted
//if have more than one field add it 'yourfield'=>$array_post['yourfield] to the array
foreach($posted_dates as $key=>$posted_date){
$date_array=explode($date_delimiter,$posted_date);
$date=strtotime(implode('-', array($date_array[2], $date_array[1], $date_array[0])));//indexed may vary from us-date and uk-date
//you need to know implode must receive('-',array(YEAR,MONTH,DAY)
$array_post[$key]=$date;
}
return $array_post;
}


please notice Im using uk-date format and im not making all the other format cases because im not going to use them and im in a hurry now.
Also this callback_before_... will work only for unixdatestamp input, please feel free to code the unixtimestamp input case inside this callback yourself if you need it, i don't need it for now since user want's to see just dates, not time in my case.

Here is the code in action

The database table data:

[img]http://imageshack.us/a/img248/657/feriados4.png [/img]

The CRUD list

[img]http://imageshack.us/a/img11/8008/feriados1.png [/img]

the CRUD edit

[img]http://imageshack.us/a/img341/5769/feriados2.png [/img]

[img]http://imageshack.us/a/img442/3734/feriados3.png [/img]

Bob Crowley
  • profile picture
  • Member

Posted 18 February 2013 - 11:14 AM

Thank you for posting this topic.

 

This is an absolute must for Grocery CRUD.

 

Nearly every project we do in our company uses dates as ints in the DB.

 

I hope that someone from the project here puts this in ASAP.

 

Modifying their code works but allows fo no more updates.


Bob Crowley
  • profile picture
  • Member

Posted 18 February 2013 - 13:11 PM

I confirm after playing around a good it that this is a decent to get way to get it to work.

In all I had to added about 6 parts and it all works.

 

Dear maintainers please consider this for your future releases. No one in the "internet" business actually uses Date Times from mysql. It's all in timestamps.

 

Part 1) I added this function to the get_XYZ_input()s area line 2000+.

It is a copy of the get_date_input. Except I modified the value at the end to use integers instead of date strings.

protected function get_date_timestamp_input($field_info,$value) {
  $this->set_css($this->default_css_path.'/ui/simple/'.grocery_CRUD::JQUERY_UI_CSS);
  $this->set_js($this->default_javascript_path.'/jquery_plugins/ui/'.grocery_CRUD::JQUERY_UI_JS);

  if($this->language !== 'english')
  {
    include($this->default_config_path.'/language_alias.php');
    if(array_key_exists($this->language, $language_alias))
    {
      $i18n_date_js_file = $this->default_javascript_path.
        '/jquery_plugins/ui/i18n/datepicker/jquery.ui.datepicker-'.$language_alias[$this->language].'.js';
        if(file_exists($i18n_date_js_file))
        {
          $this->set_js($i18n_date_js_file);
        }
      }
    }

    $this->set_js($this->default_javascript_path.'/jquery_plugins/config/jquery.datepicker.config.js');

    if(intval($value) == 0) {
      $value = time();
    }
    $date = date("d/m/Y", $value);

    $input = "<input id='field-{$field_info->name}' name='{$field_info->name}' type='text' value='$date' maxlength='10' class='datepicker-input' />
    <a class='datepicker-input-clear' tabindex='-1'>".$this->l('form_button_clear')."</a> (".$this->ui_date_format.")";
    return $input;
  }

Part 2) Added this function just after _convert_to_sql_date() function.

protected function _convert_date_to_integer($date)
{
 $date = str_replace("/","-",$date);
 $date = strtotime($date);
 return $date;
}

Part 3) Added this extra elseif to the mega elseifs section in db_insert:

elseif(isset($types[$field->field_name]->crud_type) && $types[$field->field_name]->crud_type == 'date_timestamp')
{
  $insert_data[$field->field_name] = $this->_convert_date_to_integer($post_data[$field->field_name]);
}

Part 4) Added this extra elseif to the mega elseifs section in the db_update.

elseif(isset($types[$field->field_name]->crud_type) && $types[$field->field_name]->crud_type == 'date_timestamp')
{
  $update_data[$field->field_name] = $this->_convert_date_to_integer($post_data[$field->field_name]);
}  

Part 5) Added this case on the change_list_value() function:

case 'date_timestamp':
        $value = date("d/m/Y",$value);
      break;

 

Part 6) Added this to the end of the types in get_field_input()

 $types_array = array(
          'integer',
          'text',
          'true_false',
          'string',
          'date',
          'datetime',
          'enum',
          'set',
          'relation',
          'relation_n_n',
          'upload_file',
          'hidden',
          'password',
          'readonly',
          'dropdown',
          'multiselect',
          'date_timestamp' // Does dates as integers
      );