How to get Zend Date in SQL timestamp format e.g. MySQL YYYY-MM-DD HH:mm:ss
This is something I never remember how to do.
I spent along time looking for a suitable Zend Date Part to get a Zend_Date object to return a date in a format that is suitable for inserting into a MySQL 5 timestamp column.
This seems to work:
...create table...
`col_timestamp_date` timestamp NULL default NULL,
..
Example: getting a date FROM a database and putting it into Zend Date:
//preg for mysql timestamp (versions > 4.1)
$patt = '#[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]#';
if (!(preg_match($patt,$result_array['col_timestamp_date']))) {
$this->_messages['INVALID_FORMAT_']='We expected to match the pattern YYYY-MM-DD HH:MM:SS';
$this->_hasFailed = TRUE;
return;
}
$locale = 'en_GB';
$date_obj = new Zend_Date($result_array['col_timestamp_date'], Zend_Date::ISO_8601, $locale);
Example: getting a date FROM Zend Date and putting it into a database:
$sql_date_pattern = 'yyyy-MM-dd HH:mm:ss';
$query =
"UPDATE
tbl_name
SET
col_timestamp_date = '". $date_obj->get($sql_date_pattern) ."'
WHERE
col_foo =1";
//but i prefer:
$sql_date_pattern_digitsonly = 'yyyyMMddHHmmss';
//…
SET
col_timestamp_date = “. $date_obj->get($sql_date_pattern_digitsonly).”
//…
—-
Note the difference between YYYY and yyyy:
See: http://framework.zend.com/manual/en/zend.date.constants.html
Search page for: (”The Y specifier for the ISO year and the y specifier for the real year”)
——–
Resources
Here is the handy list of Zend Date Constants:
http://framework.zend.com/manual/en/zend.date.constants.html
A bit of discussion :
http://www.nabble.com/Zend_Date-and-MySQL-datetime-td12802179.html
About the formats MySQL accepts for dates and timestamps columns: http://dev.mysql.com/doc/refman/5.0/en/datetime.html