When is a timestamp (auto) updated?

by

Give the command SHOW CREATE TABLE whatever

Then look at the table definition.

It probably has a line like this

logtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

in it. DEFAULT CURRENT_TIMESTAMP means that any INSERT without an explicit time stamp setting uses the current time. Likewise, ON UPDATE CURRENT_TIMESTAMP means that any update without an explicit timestamp results in an update to the current timestamp value.

You can control this default behavior when creating your table.

Or, if the timestamp column wasn’t created correctly in the first place, you can change it.

ALTER TABLE whatevertable
     CHANGE whatevercolumn 
            whatevercolumn TIMESTAMP NOT NULL
                           DEFAULT CURRENT_TIMESTAMP 
                           ON UPDATE CURRENT_TIMESTAMP;

This will cause both INSERT and UPDATE operations on the table automatically to update your timestamp column. If you want to update whatevertable without changing the timestamp, that is,

To prevent the column from updating when other columns change

then you need to issue this kind of update.

UPDATE whatevertable
   SET something = 'newvalue',
       whatevercolumn = whatevercolumn
 WHERE someindex = 'indexvalue'

This works with TIMESTAMP and DATETIME columns. (Prior to MySQL version 5.6.5 it only worked with TIMESTAMPs) When you use TIMESTAMPs, time zones are accounted for: on a correctly configured server machine, those values are always stored in UTC and translated to local time upon retrieval.

More Related Contents:

  1. Group mysql query by 15 min intervals
  2. How to select rows that have current day’s timestamp?
  3. Calculating time difference between 2 dates in minutes
  4. Which DB technology is sued to solve the following search issue? [closed]
  5. MySQL pivot row into dynamic number of columns
  6. Rank function in MySQL
  7. Count the number of occurrences of a string in a VARCHAR field?
  8. Find nearest latitude/longitude with an SQL query
  9. MySQL – Operand should contain 1 column(s)
  10. mysql pivot/crosstab query
  11. MySQL: Split comma separated list into multiple rows
  12. Most efficient way to get table row count
  13. Subqueries with EXISTS vs IN – MySQL
  14. What is this operator in MySQL?
  15. Remove Primary Key in MySQL
  16. 60 million entries, select entries from a certain month. How to optimize database?
  17. Get most recent row for given ID
  18. Is it possible to GROUP BY multiple columns using MySQL?
  19. MySQL monthly Sale of last 12 months including months with no Sale
  20. Is merge statement available in MySQL
  21. Mysql covering vs composite vs column index
  22. MYSQL syntax not evaluating not equal to in presence of NULL
  23. MySQL: LAST_INSERT_ID() returns 0
  24. Calculating a Moving Average MySQL?
  25. INSERT INTO or UPDATE with two conditions
  26. MySQL CURRENT_TIMESTAMP on create and on update
  27. Replace null with 0 in MySQL
  28. Get total hours worked in a day mysql
  29. Optimistic locking in MySQL
  30. Insert and set value with max()+1 problems

Leave a Comment