I would like to know how I can achieve this using the CActiveRecord in Yii:
SELECT name, surname, UTC_TIMESTAMP(last_seen) FROM users WHERE id = '12345'
I have already written most of my code using this method: $model->findAll(...), but I cannot incorporate my favourite MySQL timestamp functions into the query.
I need this to be able to set the timezone using date_default_timezone_set() in PHP.
My timestamps are stored in the TIMESTAMP format in mysql table.
EDIT
I am not trying to pass the timezone to MySQL from PHP. I store my timestamp using CURRENT_TIMESTAMP as default value. The only time I want to use date_default_timezone_set() is when I want to display this date in the user's timezone.
Sorry if I wasn't clear enough and I appreciate you input :-)
After your recent comment, yes there is a way to pass date time functions. If you check the documentation of findAll(); can take 2 arguments, one for a condition or criteria, second for parameters to be bound to the generated sql.
We can use the first argument to pass a criteria and use CDbCriteria's select property, which
refers to the select clause of an sql statement
And hence we can do:
$dbCriteria=new CDbCriteria;
$dbCriteria->select='name, surname, UTC_TIMESTAMP() as some_alias'; // we need the alias so that the timestamp is easily accessible
// you can also pass * if you need to access all the values of the table, alongwith the timestamp, like '*, UTC_TIMESTAMP() as some_alias'
$model->findAll($dbCriteria);
But ofcourse to use some_alias you'll need to declare it in your model:
public $some_alias; // this will be automatically be populated by the alias, when you query the db, in findAll