Php mysql timestamp

Question

I need to track the date and time the user was created in my mysql database. I have a column called "created" and the data type is TIMESTAMP.

The problem is that when the user changes his password or other information, the TIMESTAMP value changes. How can I set this so as not to change ????

Answer 1

You may just want to set your default sentence to CURRENT_TIMESTAMP (like @ Mark and @ dcp in other answers):

 CREATE TABLE your_table ( ... `created_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 

Test case:

 CREATE TABLE tb (`a` int, `c` TIMESTAMP DEFAULT CURRENT_TIMESTAMP); Query OK, 0 rows affected (0.04 sec) INSERT INTO tb (a) VALUES (1); Query OK, 1 row affected (0.01 sec) SELECT * FROM tb; +------+---------------------+ | a | c | +------+---------------------+ | 1 | 2010-06-09 23:31:16 | +------+---------------------+ 1 row in set (0.00 sec) UPDATE tb SET a = 5; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM tb; +------+---------------------+ | a | c | +------+---------------------+ | 5 | 2010-06-09 23:31:16 | +------+---------------------+ 1 row in set (0.00 sec) 

---

EDIT:

In my original answer, I suggested using the DATETIME column with the DEFAULT clause set to CURRENT_TIMESTAMP . However, this is only possible when using the TIMESTAMP data TIMESTAMP , as specified in the documentation :

The DEFAULT value clause in the data type specification indicates the default value for the column. With one exception, the default value must be a constant; it cannot be a function or expression. This means, for example, that you cannot set the default value for a date column as a function value, such as NOW() or CURRENT_DATE . The exception is that you can specify CURRENT_TIMESTAMP as the default value for the TIMESTAMP column.

Answer 2

It looks like you definitely haven't set the timestamp column:

Check out the manual :

 * Auto-initialization and auto-update: ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP * Auto-initialization only: ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP * Auto-update only: ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP * Neither: ts TIMESTAMP DEFAULT 0 

Answer 3

Use the datetime column. Auto save by time.

Answer 4

Change it to DEFAULT CURRENT_TIMESTAMP , otherwise it will be auto-deleted. From the manual :

In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:

With the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has a current timestamp for the default value and is automatically updated.

Without DEFAULT or ON UPDATE clauses, this is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

With the DEFAULT CURRENT_TIMESTAMP clause and the ON UPDATE clause, the column has the current timestamp for the default value, but does not update automatically.

Emphasis on mine.

Answer 5

Make it a DateTime?

Answer 6

• Leave the format as if you were inserting a correctly inserted inserted row using date() .
• Then the user will need updating when the user changes his information.

Answer 7

When you create a table, you must omit ON UPDATE

 ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 

it should be

 ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP 

link

Answer 8

In the attributes, turn off "on update CURRENT_TIMESTAMP".

Answer 9

This is a bit dirty in your case, but you can temporarily turn off automatic updates by doing:

 UPDATE woot SET password=<value_to_set>, timestamp_colum_name=timestamp_colum_name; 

(Found there: http://www.xarg.org/2010/06/disable-on-update-current-timestamp-in-mysql/ )