I have manipulate with variety ways datetime attributes of a model
Each way has pros and cons
I want your opinions about the storing(in model)/manipulate(in controller)/formatted(in view) dates
for example the date 2014-12-14 (in mysql database) should be displayed as 14/12/2014 (EUROPE) or 12/14/2014 (USA) and use it for calculation as original format (database) so
Alternative ways
-no affect when load a model,
-displayed directly using for example DateTime::format
-using rule that check the date format of the user
-override beforeSave and convert user date to original database datetime format
-override afterfind and convert date to user format zone
-display without any affect
-using rule that check the date format of the user
-override beforesave and convert user date format to database format
-override afterfind and make a new variable ($formatted_date) that has the original date in user date format
-use the $formatted_date in views
-using rule that check the date format of the user
-override beforesave check if $formatted_date changed and store it in original date database format
I use UNIXTIMESTAMP in all our projects. In order to work with that with Yii1 we created a behavior. It is attached to this.
Edit: For some reason I couldn’t upload the file… here it is:
<?php
/**
* DateFormatBehavior class
*
* @author Antonio Ramirez <antonio@ramirezcobos.com>
*/
class DateFormatBehavior extends CActiveRecordBehavior
{
//.. array of columns that have dates to be converted
public $dateColumns = array();
public $dateTimeColumns = array();
public $dateFormat = 'm/d/Y';
public $dateTimeFormat = 'm/d/Y H:i';
/**
* Convert from $dateFormat to UNIX timestamp dates before saving
*/
public function beforeSave($event)
{
$this->format($this->dateColumns, $this->dateFormat);
$this->format($this->dateTimeColumns, $this->dateTimeFormat);
return parent::beforeSave($event);
}
/**
* Converts UNIX timestamp dates to $dateFormat after read from database
*/
public function afterFind($event)
{
$this->format($this->dateColumns, $this->dateFormat, false);
$this->format($this->dateTimeColumns, $this->dateTimeFormat, false);
return parent::afterFind($event);
}
/**
*
* Formats to UNIX timestamp or $dateFormat as specified. Note that
* if using $dateFormat then assumed timestamp value
* @param array $columns the columns attributes to format
* @param string $format the format to convert the date to
* @param boolean $strtotime if boolean, will convert to UNIX timestamp
* @return void
*/
protected function format($columns, $format, $strtotime=true)
{
if(empty($columns)) return;
foreach($this->getOwner()->getAttributes() as $key=>$value)
{
if(in_array($key, $columns) && !empty($value))
{
$dt = $this->getOwner()->{$key};
$this->getOwner()->{$key} = $strtotime ? strtotime($dt) : date($format,$dt);
}
}
}
}
one additional thought: i also flirt always with the idea of using timestamps instead of DATETIME (for the same advantages as quoted by posts before), but often chose to use DATETIME. The reason is, that if you develop a complex application, later in real life when it comes to some debugging issues, it’s a lot easier (at least for me) to analyzie big sets of data based on (human readable) dates as if everything comes along based on timestamps.
When it comes to analyze issues in data coming from systems completely based on timestamp format, the converting of the timestamps (in order to "understand" the "real" date of the record) results in some slowdown of the analyse.
I agree with you when it comes to analyze the data directly on the database but I do not do that often as I do visualize the data within the UI or Log data (with human readable format) written by the app. This is one big discussion I do have with my partner though (he is a data guy).
About the issues with date ranges… mmm, 2037 seems to be a bit far (I will be quite old at that time and I hope I will be retired in the Caribbean ) but ok… The example can be modified to suit your needs.
You could probably create something like a view in mysql with all timestamp fields converted using from_unixtime() but I too like using date and datetime over a bunch of numbers.
As I understand it, the problem is MORE than just historical or future data that falls outside the timestamp range.
A huge problem for example, is countries that frequently change their own daylight-saving-time rules. This means that their data which is 2 years old, might have to be handled differently from their data that is 1 year old.
I let php deal with it: i18n All-in-one format and timezone conversions for date, time, timestamp and datetime.
