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;


$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 =
col_timestamp_date = '". $date_obj->get($sql_date_pattern) ."'
col_foo =1";

//but i prefer:

$sql_date_pattern_digitsonly = 'yyyyMMddHHmmss';

col_timestamp_date = “. $date_obj->get($sql_date_pattern_digitsonly).”

Note the difference between YYYY and yyyy:
Search page for: (”The Y specifier for the ISO year and the y specifier for the real year”)



Here is the handy list of Zend Date Constants:

A bit of discussion :

About the formats MySQL accepts for dates and timestamps columns:

Leave a Reply