php mysql timestamp

I need to track the date and time a user was created i开发者_开发百科n my mysql database. I have column called 'created' and the data type as TIMESTAMP.

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

Sounds like you don't have the timestamp column set up properly:

Check out the guide:

*

  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

You may simply want to set its default clause to CURRENT_TIMESTAMP (as @Mark and @dcp noted in the 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 a DATETIME column with a DEFAULT clause set to CURRENT_TIMESTAMP. However this is only possible when using the TIMESTAMP data type, as stated in documentation:

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

Use a datetime column. Timestamp auto-updates.

Change it to DEFAULT CURRENT_TIMESTAMP otherwise it will autoupdate. From the manual:

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

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

With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.

Emphasis mine.

Making it a DateTime?

• Leave the format as is but whe you insert formate the inserted string correctly with date().
• Then need to be updating when the user changes their info.

When you create the table, you should omit the ON UPDATE

ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

should be

ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

link

Under attributes, disable "on update CURRENT_TIMESTAMP".

This is a bit dirty in your case, but you can temporary disable auto update by doing :

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

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