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

Use a datetime column. Timestamp auto-updates.

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.

Answer 3

Making it a DateTime?

Answer 4

• 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.

Answer 5

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 6

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

Answer 7

Under attributes, disable "on update CURRENT_TIMESTAMP".

Answer 8

TIMESTAMP is a column that automatically changes on each UPDATE. Use a DATETIME column instead, like Femaref said.

Answer 9

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.

Answer 10

Like Daniel Vassallo wrote, it's better to have a DATETIME with TIMESTAMP as default value, and let the DB handle when the user account was created, this way you don't have to code the logic for that.