why inserting current time in a datetime field result in all zeros

[adrianmak]

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

 

 

[Robin S]

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)

 

[adrianmak]

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 ?

[Robin S]

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.