adrianmak Posted July 25, 2016 Share Posted July 25, 2016 I declare a date field of a table in datetime type I insert a row of data with a datetime field from time() function however, the datetime field are all 0000-00-00 00:00:00 in the inserted row. Here is the query string. I omitted the serialized array in the following INSERT INTO cdata (cid, inamount, outdata, data_time, creation, modified) VALUES (7,119.74,'serialized array','1278028800','1469442446','1469442446') table schema CREATE TABLE `cdata` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cid` int(11) NOT NULL, `inamount` double unsigned NOT NULL, `outdata` blob NOT NULL, `data_time` datetime NOT NULL, `creation` datetime NOT NULL, `modified` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; $cdata = array(); $data_time = strtotime("2-7-2010"); $time = time(); $type = array ( 'paper' => 65.1, 'plastics' => 8.93, 'aluminium' => 0.51, 'glass' => 33, 'steel' => 2.7 ); $cdata[] = array ( 'cid' => 7, 'inamount' => 119.74, 'outdata' => serialize($type), 'data_time' => $data_time, 'creation' => $time, 'modified' => $time, ); the data_time, creation and modified are declared as datetime type in mysql Link to comment Share on other sites More sharing options...
Robin S Posted July 25, 2016 Share Posted July 25, 2016 strtotime() and time() both return a Unix timestamp, which is not the same format as MySQL datetime. To convert a timestamp to datetime format... date('Y-m-d H:i:s', $timestamp) 3 Link to comment Share on other sites More sharing options...
adrianmak Posted July 25, 2016 Author Share Posted July 25, 2016 Which format is more portable ? a unix timestamp or a date string representative format ? If i intend to use store a unix timestamp, which data type should be used ? Link to comment Share on other sites More sharing options...
Robin S Posted July 26, 2016 Share Posted July 26, 2016 You could follow the example of the core Datetime fieldtype module and convert back and forth from SQL datetime to Unix timestamp on sleep/wakeup. 1 Link to comment Share on other sites More sharing options...
Recommended Posts