keep previous timestamp in mysql on update

Question

I don't want to update my timestamp field when update. I need to keep previous datetime in that field. But its updating automatically even I tried following

$data = array(
                'start_time' => $start,//previous datetime
                'user_id' => $agent_id,
                ....
            );
            $this->db->where('id', $chat_id)->update('chat_state', $data );

When I leave $start or force with previous datetime. Not work in both cases. Its updating with CURRENT TIMESTAMP in both cases

whats wrong here??

that is either baked into the [schema](http://stackoverflow.com/q/4897002) or codeigniter functionality I believe — Drew, Nov 23 '15 at 06:33
In database `start_time` field default value is `CURRENT_TIMESTAMP` and unremovable — Rejoanul Alam, Nov 23 '15 at 06:36
IS that from a database, or a session? if a database, check how the item has the time stamp set. — i am me, Nov 23 '15 at 06:36

score 2 · Answer 1 · answered Nov 23 '15 at 06:57

Connect to your mysql server using MySQL Workbench, to check your table's schema, by running the following queries:

USE mydatabase;    
SHOW CREATE TABLE mytable;

I bet you'll see something like ON UPDATE CURRENT_TIMESTAMP associated with your start_time field.

Note that, relying on DBMS to auto-update values is not a good practice; It gives you surprises, and you end up spending hours to figure out the issue.

Let's remove the "ON UPDATE CURRENT_TIMESTAMP" clause, like this:

ALTER TABLE mytable MODIFY start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

This should solve the problem.

Read more about TIMESTAMP's behaviors in different situations.

Abdulla Nilam · Answer 2 · 2015-11-23T06:49:21.667

0

Follow these steps

Select exist value (start_time)
Update exist value to new field
insert new value

01 Select current value in db

$query = $this->db->query("SELECT start_time FROM chat_state WHERE user_id = $chat_id");
$result = $query->result_array();

02 Update exist value

$data = array(
    'old_time' => $result[0]['start_time'] // move start_time to new field 
);
$this->db->where('id', $chat_id)->update('chat_state', $data );

03 insert new value

$data = array(
    'start_time' => date('Y-m-d H:i:s'),     // new Time stamp
    'user_id' => $agent_id
);
$this->db->where('id', $chat_id)->update('chat_state', $data );

edited Nov 23 '15 at 06:49

answered Nov 23 '15 at 06:41

Abdulla Nilam

36,589
17
64
85

thanks for effort. this is what i have done in my question. however it was work. problem was in my code – Rejoanul Alam Nov 23 '15 at 06:52
@RejoanulAlam happy to help :) – Abdulla Nilam Nov 23 '15 at 06:52

score 0 · Answer 3 · answered Nov 23 '15 at 06:58

Probably you have the column type as DATETIME; you need to change the type to TIMESTAMP and default null. It will not be updated automatically.

Then, while inserting the data

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

And when you want to print it in view

date('d-M-y', strtotime($record['start_time']));

keep previous timestamp in mysql on update

3 Answers3