php mysql timestamp

Question

I need to track the date and time a user was created in 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????

Answer 1

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

Answer 2

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.