Chapter 4 ■ Data transformation
Notice the additional options after the DEFAULT option. These are added by default, and it shows that the
value is changed whenever the row is created or updated. So, yes, the timestamp column is not fixed once
you set it. Let’s see an example of adding a row with the new column.
INSERT INTO date_annotation_test VALUES (null, 91.34,15.04, null, 'Test timestamp.',null);
Here I pass in the NULL value, which tells the database server to use the default, and as we saw in the
earlier column definition, that is an update to the column. Notice also I left the date and time column (date_
saved) null, meaning do not update that column. Thus, this row should show no value for the date and time
column, but the date and time that the row was inserted for the timestamp column (data_changed). The
following shows the cumulative data:
mysql> SELECT * FROM date_annotation_test;
+----+-------------+------------+---------------------+-----------------+---------------------+
| id | temperature | barometric | date_saved | notes | data_changed |
+----+-------------+------------+---------------------+-----------------+---------------------+
| 1 | 90.82 | 15 | 2011-07-15 23:15:10 | NULL | 2015-11-08 15:55:50 |
| 2 | 91.34 | 15.04 | 2011-07-15 23:16:30 | Test datetime. | 2015-11-08 15:55:50 |
| 3 | 91.34 | 15.04 | NULL | Test timestamp. | 2015-11-08 15:57:27 |
+----+-------------+------------+---------------------+-----------------+---------------------+
3 rows in set (0.00 sec)
Notice the last two rows have the same data. I did this intentionally so you could see the difference
between adding a row with a datetime column and adding a row with a timestamp column.
Thus, using a timestamp column means the database does the work for you, so you can keep track of
when the data was added or changed. However, there are limitations to using the timestamp data type. First,
you can have only one per table. Second, the values will be overwritten on any change to the row. Thus, if
you want to save a specific date and time but later want to update the row (say to add notes), you may either
want to reconsider using the timestamp or add one or more datetime columns for storing static date and
time values.
But wait, why are the data_changed values the same for the first two rows? You may have noticed
that the data_changed column for the first two rows have the same value. This is because I added the new
column to an existing table. Internally, the database server must update all rows in order to accommodate
the change, and thus each row that already exists in the table gets changed; and since the column uses the ON
UPDATE CURRENT TIMESTAMP (by default), the new column was updated. Keep this in mind when changing
the schema of an existing table.
■Tip You should consider the effects of adding a timestamp column to an existing table as well as how the
timestamp column is updated to ensure you are saving the data you want.
Data Type Transformations
Changing the type of a data element is something you should take some time to think about and plan
carefully. This is because it is easy to lose data if you do not perform the conversion properly. This goes
beyond changing a float to a string and back (although you can have issues there too if you strip off too many
decimals). You must consider the amount of storage required as well as range of values. For example, an