illegal 0000-00-00 00:00:00 timestamp being sent by php to mysql using codeigniter

Question

here is a Database_update_entry function in my CodeIgniter model which gets executed upon successful form submission.

<?php
    function update_entry(){
        $data = array(
            'sl' => $this->input->post('item_sl'),
            'item_name' => $this->input->post('item_name'),
            'img_url' => $this->input->post('img_url'),
            'sound_url' => $this->input->post('sound_url'),
            'price' => $this->input->post('price'),
            'unit' => $this->input->post('unit'),
            'timestamp' => time(),
        );
        echo "current time: ".  time();
        $this->db->update(MY_TABLE_NAME, $data);
    }
?>

the database query gets executed fine.... but the time that gets set into the mysql table is 0000-00-00 00:00:00 whereas the <?php echo time() ?> statement shows 1307018223 in the browser.... which is a valid UNIX timestamp... you can check the timestamp validity here.

here is a screenshot of the table that takes data from mySQL table after successful update_queryhttp://s3.amazonaws.com/awesome_screenshot/658523?AWSAccessKeyId=0R7FMW7AXRVCYMAPTPR2&Expires=1307020816&Signature=AncPjnzG9p9QTX7zebZp7c9teB0%3D

what am i doing wrong??? how can i pass a legal timestamp from my php to mySQL? [p.s. i have checked that the timestamp field in my mysql table is a timestamp field]

Answer 1

Use date('Y-m-d H:i:s', time());

;)

Answer 2

mysql timestamp field accept yyyy-mm-dd H:i:s format of date so you have to pass date into this format not the UNIX timestamp

instead of this

'timestamp' => time()

Use

'timestamp' => date('Y-m-d H:i:s')

If you really want to store UNIX time stamp change the field type to CHAR

Answer 3

You need to provide a time stamp string, not an integer. Try:

'timestamp' => date('Y-m-d H:i:s', time()),

Answer 4

format time : date('Y-m-d H:i:s', time())