how to insert a current_timestamp when an update is performed.

[eluser]reghan[/eluser]
I am unsure how to update my 'DateCompeted' field with the current timestamp, when a user submits and update in a form:

Here is my code:

Code:

$id = $this->uri->segment(3);
  
  $dept_info = $this->department_model->get_by_id($id);
$data['department'] = $dept_info->result();
$data['action'] = site_url('bia/addRes/' . $id);
$data['link_back'] = anchor(base_url(),'Back to list of departments',array('class'=>'back'));
$datecomp = now();
  
  $res = array('DepartmentName' => $this->input->post('dpt_name'),
    'DateCompleted' => $datecomp);
    
    
   $this->department_model->updateResponder($id,$res);

model

Code:

function updateResponder($id, $res)
    {
  
     $this->db->where('DepartmentId', $id);
     $this->db->update('department', $res);
  
  }

using the above code gives me this error:

Error Number: 1292

Incorrect datetime value: '1340288274' for column 'DateCompleted' at row 1

UPDATE `department` SET `DepartmentName` = 'reghan', `DateCompleted` = 1340288274 WHERE `DepartmentId` = '6'

Filename: C:\wamp\www\igniter\system\database\DB_driver.php

Line Number: 330


I am not really sure what to do at this point. I would like the datetime updated every time the user saves the form.

Hope this helps! thanks in advance!!

[eluser]jmadsen[/eluser]
This would be more easily done on the mysql database side - set a datatype of timestamp, and an attribute of "update with current timestamp", and don't update from the CI side at all

[eluser]Otemu[/eluser]
[quote author="jmadsen" date="1340289602"]This would be more easily done on the mysql database side - set a datatype of timestamp, and an attribute of "update with current timestamp", and don't update from the CI side at all[/quote]

You could also just use PHP date and insert it directly, just make sure that the date format is correct compared to the format used in the database and set a default timezone

Here a good topic with a number of solutions

[eluser]reghan[/eluser]
Thanks Everyone!

I will explore both options!

[eluser]CroNiX[/eluser]
I'd check what jmadsen said. It's better for this to just be in your table definition. Then you never have to tell it to update, it just will whenever the record updates.

Code:

fieldname timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

This will create the timestamp when a record is created and also update it whenever the record updates.

[eluser]johnpeace[/eluser]
I store unix timestamps as INT(11)...is there something wrong with that approach?

[eluser]CroNiX[/eluser]
I like to be able to use the built in date/time functions mysql offers without having to do a bunch of extra bloated conversions from unix timestamp to mysql timestamp. If you use a unix timestamp, you can't use built in functions like the one I posted above without extra sql to convert, so you'd also have to manually create it on insert and manually update it. I think it's just much simpler, uses less code and keeps this stuff out of php and lets the db handle it, which is more optimized for this task as they provide more specific functions to handle date/time calculations within sql.

[eluser]CroNiX[/eluser]
Also, when just looking at the raw database data, 2012-06-21 12:07:31 is a lot easier to read for me than some huge number. Unix timestamps also don't work for dates before Jan 1, 1970

[eluser]johnpeace[/eluser]

Unix timestamps also don’t work for dates before Jan 1, 1970

I thought they were just negative numbers before Jan 1 1970...

[eluser]CroNiX[/eluser]
Yes, hasty reply. Meant after 2038.