CODEWITHSUNDEEP
mysqlunixdatetimecodeignitertimestamp
Jake
Jake

Reputation: 775

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

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.

Upvotes: 1

Views: 3770

Answers (2)

sdf
sdf

Reputation: 115

Avoid now(), cause now() returns the current time as a Unix timestamp, referenced either to your server's local time or GMT, based on the "time reference" setting in your config file. If you do not intend to set your master time reference to GMT (which you'll typically do if you run a site that lets each user set their own timezone settings) there is no benefit to using this function over PHP's time() function.

Upvotes: 0

Phil Sturgeon
Phil Sturgeon

Reputation: 30766

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.

Upvotes: 2

Related Questions