Date is being set to 0000-00-00 00:00:00, no solutions are working

[eluser]JMajek[/eluser]
Hello Everyone,

AS the title states I am having trouble with setting a date created field with Codeigniter. I have tried several things via searching the forums and Stack Overflow.

I've tried now(), time(), date(), the date helper in Codeigniter, strtotime() and I still have the same issue.

I just wanted to post my code and sql query to see if you guys seem something I'm not seeing.

Thanks.

Here's the code and sql

$this->db->set('screenid', $id);

Code:

//$now = date('Y-m-d H:i:s', strtotime('now'));
$this->db->set('datecreated', date('Y-m-d H:i:s'), FALSE);  
$this->db->insert('screens');

the sql

Code:

CREATE TABLE IF NOT EXISTS `screens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `screenid` mediumint(9) NOT NULL,
  `source` varchar(255) DEFAULT NULL,
  `datecreated` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM

Any help is greatly appreciated.

[eluser]johnpeace[/eluser]

Every call to a date/time function will generate a E_NOTICE if the time zone is not valid, and/or a E_STRICT or E_WARNING message if using the system settings or the TZ environment variable. See also date_default_timezone_set()

http://php.net/manual/en/function.date.php

Maybe you need to set the default timezone?

http://www.php.net/manual/en/function.da...ne-set.php

[eluser]CroNiX[/eluser]
Personally, I'd let the database take care of that using "DEFAULT CURRENT_TIMESTAMP" so you don't manually have to include it every time you create a record...it will do it for you.

Code:

CREATE TABLE IF NOT EXISTS `screens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `screenid` mediumint(9) NOT NULL,
  `source` varchar(255) DEFAULT NULL,
  `datecreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM

[eluser]johnpeace[/eluser]
[quote author="CroNiX" date="1330365450"]

Code:

`datecreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

[/quote]

THANK YOU!! I've always wanted to be able to do that but never new about that!!

[eluser]JMajek[/eluser]
[quote author="CroNiX" date="1330365450"]Personally, I'd let the database take care of that using "DEFAULT CURRENT_TIMESTAMP" so you don't manually have to include it every time you create a record...it will do it for you.

Code:

CREATE TABLE IF NOT EXISTS `screens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `screenid` mediumint(9) NOT NULL,
  `source` varchar(255) DEFAULT NULL,
  `datecreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM

[/quote]

Thanks, but when I run the sql it generates an error, #1067 - Invalid default value for 'datecreated'

johnpeace, I will look into setting a default time zone.

[eluser]CroNiX[/eluser]
Ah, sorry, it should be a 'timestamp' column type instead of 'datetime'

[eluser]InsiteFX[/eluser]

Code:

DATETIME uses NOW()
TIMESTAP uses CURRENT_TIMESTAMP

Date and Time Functions

[eluser]JMajek[/eluser]
Thanks, guys that did the trick. Should I change datemodified to timestamp, will that be easier to update when a record updates changes? Datemodified is the same issue as datecreated it doesn't update the date.

[eluser]InsiteFX[/eluser]
for datetime you can use this to set it.
EXAMPLE:

Code:

public function add_post()
{
    $now = date("Y-m-d H:i:s");

    $data = array(
        'title'       => $this->input->post('title', TRUE),
        'tags'        => $this->input->post('tags', TRUE),
        'status'      => $this->input->post('status', TRUE),
        'body'        => $this->input->post('body', TRUE),
        'category_id' => $this->input->post('category_id', TRUE),
        'user_id'     => $this->session->userdata('user_id'),
        'pub_date'    => $now,
    );

    $this->db->insert('posts', $data);  
}

[eluser]Aken[/eluser]
The problem you were having with your original code is that you were setting the third parameter of set() to FALSE, which was telling active record to not automatically escape the column. This is good when you don't want strings to automatically have the ' marks added around them. But when modifying DATETIME columns, their values act as strings, so they need apostrophes surrounding them. Example:

Code:

$this->db->set('column', date('Y-m-d H:i:s'), FALSE);
$this->db->insert('table');

// Generates BAD query:
// INSERT INTO `table` (`column`) VALUES (2012-02-27 22:56:56)

$this->db->set('column', date('Y-m-d H:i:s'));
$this->db->insert('table');

// Generates GOOD query:
// INSERT INTO `table` (`column`) VALUES ('2012-02-27 22:56:56')

You should still use the advice above about giving the columns a default if you just want to use the current time. Keep in mind, though, that it will be the current time of the MySQL database server, which might not necessarily be the same as your application. If you need really specific dates, or possibly flexibility for different time zones, that can be very important. But at least this will show you why your original code wasn't working.