0

As clearly indicated by Should I use field 'datetime' or 'timestamp'?, the major differences between a DATETIME and TIMESTAMP field are:

  1. TIMESTAMP in MySQL are generally used to track changes to records, and automatically updated every time the record is changed.
  2. DATETIME represents a date (as found in a calendar) and a time (as can be observed on a wall clock), while TIMESTAMP represents a well defined point in time.
  3. Only TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval.

So, I have a table which contains both a date_created column and a date_modified column. I made date_created as DATETIME so it doesn't get overridden every time the record is updated, and made date_modified as TIMESTAMP so it updates every time the record is updated.

How do I keep the two synchronized? For instance, after initially creating a record, the date_created and date_modified columns, they will be the same. Later if the record was never changed, they should remain the same regardless of timezones and daylight saving time, no?

Community
  • 1
  • 1
user1032531
  • 24,767
  • 68
  • 217
  • 387

1 Answers1

2

Point 1 in your list isn't exactly correct. DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP are options that can be applied to a TIMESTAMP field (and now in 5.6 a DATETIME field as well). They are not inherent properties of TIMESTAMP. If you are using a version before 5.6, you should probably use TIMESTAMP for both fields.

CREATE TABLE my_tbl
  (date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
   date_modified TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

date_created will get the current timestamp when the record is created and date_modified will get set to the current timestamp whenever the record is updated (you can prevent this with a date_modified = date_modified in the update).

If you are using MySQL 5.6, you could use DATETIME and get the same behavior.

See http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

Andrew
  • 4,574
  • 26
  • 31
  • Thanks Andrew. Sounds like the key point is to make them the same type. If I want the user to see the value (i.e. I display "last updated by John Doe on 12:00 PM 5/5/2013"), better to use timestamp or datetime? – user1032531 Apr 15 '13 at 18:15
  • Are you sure? I tried it and got: #1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause – user1032531 Apr 16 '13 at 01:31
  • With 5.5, I don't think your solution will work: One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column. – user1032531 Apr 16 '13 at 02:36
  • Sorry- it worked for me on the latest version. See here http://stackoverflow.com/questions/4489548/why-there-can-be-only-one-timestamp-column-with-current-timestamp-in-default-cla for some ideas for a solution to #1293. – Andrew Apr 16 '13 at 02:51
  • No worries! Planing to upgrade to 5.6. – user1032531 Apr 16 '13 at 06:01