Is there any real support for DATE and DATETIME columns in Yii?
Maybe I’m missing something obvious, but I don’t see any simple way to, for example, insert the current date and time into a column.
It also doesn’t look like DATETIME fields are validated any differently from, say, strings, and I don’t see anything in the validators folder that would support dates or times.
I see a CTimestamp class in utils, but this does not seem to be supported by models, or by Active Record - what gives??
I suppose these two field types can be very database-specific, so Yii provides only regex validators in such cases.
You may also find CDateFormatter and CDateTimeParser interesting.
I’m not really talking about formatting and parsing so much as mediation - most ORM systems have some sort of data mediator that ensure that what you get from (and put into) your models is a representation of the data type that is native or practical to the programming language.
As you point out, dates may be stored or retrieved in different ways on various DBMS - a mediator may need different drivers for different DBMS, but this should be of no concern to the programmer.
In PHP there is (or was) no native date or datetime type - now there is the DateTime class, but only for newer versions of PHP. There are other (non-native) date/time classes for PHP, e.g. the CTimestamp class in Yii.
But as this is not supported by ActiveRecord as such, it’s still up to the programmer to convert back and forth for whatever DBMS you happen to be using; which in turn defeats the point of using a database abstraction layer.
A data mediator is a much safer and simpler way to go - CTimestamp goes in, CTimestamp comes out, no guesswork, no error-prone type juggling or repetitive conversion and validation.
I think it’s definitely something worth considering.
Without automatic datetime support, you still use CActiveRecord, so it may be not that pointless.
I’d like to propose the use of DateTime classes, but that would also break backward-compatibility. Because of this, the only way is to use non-native classes, as you already mentioned.
I’m not sure, however, that this is what everybody wishes. Some of us intentionally chose DATETIME column type in MySQL and forget about formatting forever. Others store their timestamps as integers and show time range spent inbetween (two weeks ago, etc.).
Could you please show an example how you’d use this converting feature?
Hmm.
It doesn’t have to break backwards compatibility.
And like you said, some people aren’t going to want that feature.
So it could be done by some simple means, like maybe…
class Post extends CActiveRecord {
...
public function mediators() {
return array(
'posted'=>'date'
...
);
}
}
This would tie into the getter/setter magic, using a class (i.e. CDateMediator) to convert to/from database-compatible strings, as required.
The mediator would sit between accessors and validators, for example, so that validators would be operating on a CTimestamp, for example, rather than the string.
And mediators would have some kind of type-validation built in as well, so that validators wouldn’t be activated if, for example, a mediator failed to convert an input string to another datatype.
In fact, mediators could replace validators in some cases - for example, accepting an integer from a submitted form is really more than validation, it involves type conversion, which if mediators were implemented, would be the responsibility of those.
In turn, this would simplify some aspects of validators, as they could count on having the correct datatype delivered to them from a mediator - a date range validator, for example, would not need to perform any type validation or conversion; which simplifies having different validators that operate on the same datatypes, as these would not need to individually perform any conversion or basic type validation…
I can’t see the point of storing an integer in a timestamp class only to be read and sent to database to store. I normally don’t apply validators to datetimes as they are mostly auto-generated by my application. If I assign the value of time() to one of my attributes, of course it’ll be an integer.
I believe a mediator could easily be represented as a pair of behavior methods.
Some developers would want it that way - personally, I would want a mediator that simply converted to/from MySQL DATETIME format and PHP timestamps.
The point is that, when you’re writing the business logic of your application, you don’t have to convert back and forth all the time. So if I have somebody’s birthday stored somewhere, for example, and I need calculate their age, I can skip the strtotime($value) conversion and simply operate on the mediated value, which would already be a timestamp.
Having given this some more thought, I now think that mediator-aliases need to be registered somewhere, so that CDbConnection::initConnection() can register compatible mediators for each database engine - some engines might require a different mediator for DATETIME, for example, while some engines may be able to share the same mediator.
Also, mediators for CFormModel would be required - so that form input can be implicitly parsed and converted both ways. If using a CTimestamp mediator with a form model, using a CTimestamp mediator for a form that interacts with that model would be necessary.
I am going to give this some more thought, but I am going to attempt to build some class extensions to demonstrate this concept at some point. I’ll be posting and sharing 
Well, I still can’t see more reasons yet.
I think this causes the misunderstanding. I think one of the most common solutions for this is that data is converted to timestamps during load, and back to datetimes during save. This way you don’t have to deal with its type ‘all the time’.
Furthermore, I can’t exactly tell: why do you store dates as datetime in database, if you never use them in that form?
Yes, exactly - this is done manually, every time.
Several reasons:
transparency: timestamps stored as integers in the database are not human readable.
performance: DATETIME is an efficient way to stored date/time data, both in terms of performance and storage.
compatibility: you can perform meaningful queries on DATETIME fields, beyond filtering by date range.
When you perform queries on the data, you are using them in their DBMS-native storage form.
Another reason is that some datatypes can’t be represented in a DBMS.
The basic philosophy of data mediation, is that the programmer shouldn’t have to worry about the underlying media - so for instance, whether a timestamp is stored as a DATETIME field in MySQL, or as a plain string in a form field, when the value is exposed to you by the model, it is always exposed as a timestamp.
It’s a means of abstracting repetitive type-conversion and type-validation (not other types of validation) - repetition causes errors, and we avoid that by creating abstractions. An AR layer abstracts INSERT/UPDATE/SELECT queries and manages the construction of objects - repetitive and error-prone tasks. In the same way, data mediators abstracts the repetitive and error-prone tasks of converting types specific to the programming language to/from other media.
If you can’t see the beauty of this, you probably just have a different way of thinking than I do
If you don’t mind, maybe you could invite some other members of the dev team to participate in this discussion?
I would like to hear their thoughts on this subject.
Most of your data in db is not human readable either. That’s why we create an application on top of that data.
What can be faster than an integer number (apart from the boolean of course 
)?
Like? And, for the sake of completeness, you can filter by date range with timestamps, too.
Please note that I’m not against automatic conversion. I’m against this additional layer of abstraction, which can be easily done with behaviors.
Yes, good idea. Apparently, we two can’t agree on this one.
Sure, you can store everything as strings and integers - the way I was schooled, that is not considered good database design, and on a modern DBMS, that’s not going to give you any advantage, neither in terms of performance or data volume. Datatypes like DATETIME and ENUM have many advantages over the basic data types, transparency being just one of them.
Integers used to store dates, for example, are not very efficient when it comes to more advanced operations like, say, writing a query that finds every entry posted on the first of the month. I don’t even want to think what a query like that would look like on integer timestamps.
But we’re really getting into an entirely different discussion here - on a topic that was outdated 10 years ago, and thankfully made irrelevant by modern DBMS long ago…
By the way, looking at implementation strategies, I agree that behaviors is probably the way to go - thank you for that idea! This way, if a data mediator was added to the framework, using it would be entirely up to the developer, and it’s existence would not affect any existing code - you could choose to use it or not, which is always a great thing in a framework.
Having many useful features is good, but forcing them on developers (or making them pay in the form of overhead) is not
mindplay: thank you for bringing up the issue.
I would like to add a few thoughts.
I noticed that in many projects datetime fields do not seem to be that important, they are only used to have a created and changed field for your database rows.
You only learn about the beauty of datetime fields when you really have to use them which I did when I started to program a time recording system for my company.
Pestaa: Have a look at the date/time functions of PostgreSQL, you can do real magic with them, forget MySQL, forget integer columns. I also often use a datetime field instead of date because you can then use infinite and -infinite as a value in PostgreSQL and all the algebra still works beautifully.
In my time recording system I converted my datetime fields back and forward and was never happy with it, so I hoped I would find a nice solution in Yii. Unfortunately there is none, no dateValidator, no convertion for DB. If you want to have multiple locale support its not easy to come up with a good solution, but I think it would be worth it and it should be in the Yii core. Maybe another framework could deliver some inspiration (Django)?
The idea of a data mediator class comes from Java, or more accurately, from JSP.
If you just need to handle dates, you might want to take a look at this:
http://www.yiiframework.com/extension/i18n-datetime-behavior/
With a generic abstraction layer for type conversions, you could handle much more than dates - Rails, for example, has a plugin that abstracts filesystem references in the database, which means you can upload/download files referenced by filename in a column, for example, without manually converting to and from file reference objects.
Basically, with a type mediator, you can abstract any kind of datatype. Think about images managed in this way - you probably don’t want images stored in blobs, you would most likely rather have a filename reference in a column, but if your image was presented to you as an object straight from the model, you could do things like versioning (resizing, cropping, etc.) by simply calling methods directly on the objects in the model, as well as replacing (uploading) or deleting the referenced image file, and so on.
Personally, I love abstraction - the less grunt work you have to do, the more real work you can get done. And doing things like image management, file uploads, date handling or other type management, once and for all, means you don’t have to re-implement these features for every project - you will have stronger and more reliable features every time you improve on your solutions, while spending a fraction of the development time.
For some people this approach works, for some it doesn’t - a feature like this needs to be optional, so that anyone can use the framework in a way that fits their way of thinking…
I hammered out some example mediator code.
It comes in the form of a CAttribute class - an abstract base class for abstract data types, e.g. anything that cannot be represented by simple PHP variable types.
Note that this code has not been tested, and I have not yet attempted to integrate this with CActiveRecord - it’s just an idea at this point.
The integration would consist of a simple modification to CActiveRecord::setAttribute(), which would check if the attribute being set is a CAttribute, and if it is, it would call setValue() on it, rather than overwriting the attribute itself.
Modifications would be required (I’m not entirely sure yet where), when attributes are written into an UPDATE or INSERT statement, there needs to be a check to see if the attribute is CAttribute, and if it is, get the mediator for the current DB connection and call CAttributeMediator::getSqlValue() which converts the attribute to the SQL representation required for the connection’s driver.
Likewise, after a SELECT, when a CActiveRecord is constructed, CAttributeMediator::setSqlValue() needs to be used to convert the SQL string into a CAttribute representation.
I think I could implement most of this without actually hacking any part of the Yii codebase, just creating overrides, so I might continue with this proof of concept to see if I can pull that off - unless someone can convince me that this idea is somehow retarded in the first place 
But most ORMs have a mediation layer of some sort - in the case of PHP it’s even more necessary than in some other languages, because PHP only has a few primitive types of it’s own; a timestamp isn’t even discernible from an integer as such, a file path isn’t discernible from any other string, and so on.
Having more abstract type implementations, however, is only really interesting if we can somehow mediate between SQL and form posts, without relying on manual conversion everywhere - it’s tedious, and error prone
<?php
/**
* Abstract base class for an attribute class.
*
* An attribute object encapsulates an abstract value, which cannot be represented
* by a single, native PHP variable-type.
*/
abstract class CAttribute
{
/**
* Returns the mediator for the attribute
* @see CAttributeMediator
*/
public function getMediator($dbConnection=null)
{
if ($dbConnection===null)
$dbConnection = Yii::app()->db;
$driver = $dbConnection->getDriverName();
static $mediators=array();
$className = get_class($this).ucfirst($driverName).'Mediator';
if (isset($mediators[$className]))
return $mediators[$className];
if (class_exists($className))
return $mediators[$className] = new $className;
else
throw new CException("CAttribute::getMediator() : no mediator {$className} was found");
}
/**
* Returns a native PHP value that represents the internal value of the attribute
*/
abstract public function getValue();
/**
* Sets the internal value of this attribute to a native PHP value, or a string posted by an HTML <form>
* @param mixed a native PHP value
* @param boolean if true, $value will be handled as a string posted by an HTML <form>
*/
abstract public function setValue($value,$post=false);
}
/**
* Abstract base class for an attribute mediator.
*
* An attribute medidator is responsible for conversion of an abstract value,
*/
abstract class CAttributeMediator
{
/**
* Returns an SQL string representation of the internal value of the given attribute.
* @param CAttribute the attribute to convert to an SQL string
* @return mixed the SQL string representation of the value, or null if the attribute is unset
*/
abstract public function getSqlValue($attribute);
/**
* Sets the internal value of the attribute according to an SQL string value.
* @param CAttribute the attribute to be set
* @param string the SQL string value to be interpreted and applied to the given attribute
*/
abstract public function setSqlValue($attribute, $string);
}
/**
* This example attribute-type encapsulates a PHP timestamp.
*/
class CDatetimeAttribute extends CAttribute
{
protected $_timestamp;
public function getValue()
{
return $this->_timestamp;
}
public function setValue($value,$post=false)
{
if ($post)
return $this->_timestamp = !empty($value) ? strtotime($value) : null;
if (is_numeric($value) || $timestamp===null)
return $this->_timestamp = value;
throw new CException("CDatetimeAttribute::setValue() : incompatible value '{$value}'");
}
public function __isset()
{
return $this->_timestamp > 0;
}
}
/**
* The example mediator converts to and from a MySQL DATETIME formatted string representation
*/
class CDatetimeAttributeMysqlMediator extends CAttributeMediator
{
public function getSqlValue($attribute)
{
$value = $attribute->getValue();
return !empty($value) ? date('Y-m-d H:i:s', $value) : null;
}
public function setSqlValue($attribute, $string)
{
$attribute->setValue(
$string!='' ? strtotime($string) : null
);
}
}
I thought about how to integrate this - I think it needs to happen at the lowest level possible. So for conversion to SQL strings, that would be in CDbCommand::prepare() - and for conversion from SQL strings to attributes, in CDbDataReader.
Or not. The other possibility is that CActiveRecord could handle this all internally. If there is some way that CActiveRecord knows that it’s being populated by a finder, it could be responsible for the SQL conversions internally. Using __toString() magic, the CAttribute classes could themselves be responsible for the conversion back to SQL strings.
I think maybe this would approach would be preferable?
Rather than having complex conversion processes integrated in various classes, and at various levels of abstraction, if all of the required extensions could reside in a single class, that would be much cleaner.
Thoughts?
I was thinking about similar topic today.
I wanted to put a layer on __get and __set methods of AR class and implement some conversions in that layer. I would also configure the layer by setting the value of some class variable in AR so it knows which output/input instance of the layer to use.
Internally the values of attributes in AR would be held always in php native format.
The idea was simple.
When i output data from AR to presentation layer in a view i set $ar->setOutputLayer(‘presentation’) and do conversions to human readable formats internally by using just echo $ar->anAttribute.
When i need to save the data to db i call $ar->setOutputLayer(‘db’) and the layer communicates with db schema and db driver and does proper conversions when reading attribute values to save them. AR uses $ar->anAttribute to get the values so the layer would work here.
Similar thing with setting the value to the input layer.
The question is whether to use abstraction or just upgrade AR to do some simple tasks even if i’m sacrificing some concepts of mvc for a simple solution that can be implemented in one place.
As i understand MVC, it tells us that between presentation layer and db layer must be a controller layer. So AR can’t do what a controller should do.
But what if i inject some functionality into AR in a controller or
configure the input/output layer of AR class in a controller
and let him do the hard work?
Abstraction may come for a price. The price may be simplicity. Abstraction may also cause overhead if we wrap simple data into a more complex classes. In my opinion the choice is not simple.
The idea of abstraction can touch different aspects of the application - structure, processes, flows, states, actions, data itself etc. It’s not always a simple task to define what should we abstract or what shouldn’t we.
Currently i’ll stick with simple solutions and my thoughts are:
override __get to do conversions
override __set to do conversions
add functionality of configuration the conversion process
I’m still learning Yii’s functionality and concepts and I’m quite new here so i don’t want to hack what’s deep inside the framework until i fully understand the concept of each of the "C"Classes 
I brainstormed CAttribute a while back: http://www.yiiframework.com/forum/index.php?/topic/885-attributes-as-classes/
My concern though is performance. I know django uses this technique
Are you guys talking about casting the database fields to their equivalent CAttributeModel? e.g. text would be CTextAttribute, int CIntAttribute etc
Am I correct to assume this would then allow us to write $product->description->truncate(50); (after adding truncate to the CTextAttribute to string class…by using something like behaviours).