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

Leave a Reply