Default Timestamp Mysql

Question

Default Timestamp Mysql

Navigation

#1 by (4 votes)

2

How do I put the value of the field by default timestamp current?

I need to alterar my table to add a field timestamp not null which has the current default by default, so all previous records have the current timestamp too.

mysql timestamp

asked by anonymous 02.12.2014 / 14:34

1 answer

How to call a javascript function from an external file? JSON return in an array in LOOP

score 4 · Accepted Answer

When creating the field, define:

dataHora TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

If you are going to modify the structure of an existing table, use this query before of the modification:

UPDATE minhaTabela SET dataHora = CURRENT_TIMESTAMP WHERE dataHora IS NULL;

query to modify the field:

ALTER TABLE
   minhaTabela
CHANGE
   dataHora
   dataHora TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

Notes:

Remember that you can use DATETIME to also store dates that do not change.
If the field is defined this way:
```
dataHora TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
```
Your field will change in UPDATES as well. If it is not the desired effect, just use ALTER TABLE above without setting ON UPDATE .
To check the structure of the table use:
```
SHOW CREATE TABLE minhaTabela;
```
because EXPLAIN does not show ON UPDATE definitions.