Mysql timestamp created and updated field?

PHP
1

Devils in details someone says :slight_smile:
What it seems to be a routine job to add this two columns to fetch records of created and updated content, it become a huge problem. I’m interested to see how you guys solve problems like this.
I have columns like this
mysql columns

created timestamp default now(),
updated timestamp default now() on update now()

and I passing current time like this in my processing php file


$now = time();
$record->created = "$now";
$record->updated = "$now";

#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
I tried just for a test to store values in unsigned int and this work only for one column, after update my created date is null and updated content is set.
I need to store this values explicity in timestamp field cause I need to calculate them to local timezone.

Regards,
MK

2

I burned my fingers once with timestamp field.
Since then it never bothers me to set datetime manually.
Adding just one more value to the query isn’t that hard. But one careless query can spoil whole data.

Anyway you can read timestamp manual entry for your version of MySQL to see proper syntax.

3

Quote from mysql documentation:

For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

4

This should also be helpful:

The only way to have more than one TIMESTAMP column, when one is declared with either DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP or both, is to force the other timestamp column default to a valid timestamp value, such as 20070101000000.

I believe this is due to a TIMESTAMP column without a declared default uses the CURRENT_TIMESTAMP and thus throws this error

#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

even though it appears you are only trying to use it on one column.

At
http://dev.mysql.com/doc/refman/5.1/en/timestamp.html

5

thanks for replies, now I’m realy frustrated. As far as I understand there is no way to use two timestamp or any other field which is using now() method for current time and date. I also tried to use DATE field for created time and timestamp for updated which is using now() and now I’m getting this error message Call to undefined method CURDATE().
$record->Created = CURDATE();
Cause I’m realy tired about this I will ask you nicely how can I use two columns one for created DATETIME and one for updated TIMESTAMP
and how to write insert query for created field.

6

to insert
INSERT INTO table SET created=curdate(), updated=now(), data=‘data’, …

to update
UPDATE table SET updated=now(), data=‘data’, …

that’s all

7

yes, that’s something I tried but I got this error message
Fatal error: Call to undefined function curdate()
I already use this function on my earlier projects, don’t know how this function cannot be found now.

8

it is SQL, not PHP code

9

The trick is to place the update column before the created as MySQL will automatically update the first when the row is updated. However, it will leave the other one alone.


CREATE TABLE TEST (
     id TINYINT UNSIGNED NOT NULL
    ,updated_on_ts TIMESTAMP NOT NULL
	,created_on_ts TIMESTAMP NOT NULL
	,PRIMARY KEY(id)
);

Notice in the case shown below that when the id field is updated that rows updated_on_ts timestamp updates but the created_on_ts does not.

10

thanks to all of you, it’s been more complicated than I thought cause I’m using object vars and sanitized attributes

 public function create(){
        global $database;
        $attributes = $this->sanitized_attributes(); 
         
        $sql .= "INSERT INTO ".self::$table_name." (";
        $sql .= join(", ", array_keys($attributes));
        $sql .= ") VALUES ('";
        $sql .= join("', '", array_values($attributes));
        $sql .="')"; 
        if ($database->query($sql)){
            $this->ID = $database->insert_id();
            
            echo "insert content ok !"; 
            return true; 
        } else {
            echo "error"; 
            return false; 
        }
    }
11