setting codeigniter mysql datetime column to time() always sets it to 0

Question

Hi guys. I'm using Codeigniter for a small project, and my model works correctly except for the dates. I have a column defined:

created_at datetime not null

and my model code includes in its array passed into db->insert:

'created_at' => time()

This produces a datetime value of 0000-00-00 00:00:00.

When I change it to:

'created_at' => "from_unixtime(" . time() . ")"

it still produces the 0 datetime value.

What am I doing wrong? How can I set this field to the given unix time? Also, I know mysql sets TIMESTAMP columns automatically for you - I'm not interested in that solution here.

So far I can't find a complete example of this on the web.

Answer 1

datetime requires a string of YYYY-MM-DD, where-as time() produces a UNIX timestamp which is an integer of seconds since 1st January 1970. The two dates are entirely different.

If you wish to use PHP to populate your MySQL datetime field you have to use:

date('Y-m-d h:i:s');

Or something along those lines anyway.