Tech Erudition

CREATE TABLE record (createdon TIMESTAMP, modifiedon TIMESTAMP);
INSERT INTO  record (createdon, modifiedon) VALUES ('2010-10-01 11:01:02', '2010-10-01 11:01:02');

SELECT * FROM record;
+---------------------+---------------------+
| createdon           | modifiedon          |
+---------------------+---------------------+
| 2010-10-01 11:01:02 | 2010-10-01 11:01:02 |
+---------------------+---------------------+

ALTER TABLE record ADD COLUMN activeon TIMESTAMP;
UPDATE record SET activeon=modifiedon;

SELECT * FROM record;
+---------------------+---------------------+---------------------+
| createdon           | modifiedon          | activeon            |
+---------------------+---------------------+---------------------+
| 2012-10-11 01:47:41 | 2010-10-01 11:01:02 | 2010-10-01 11:01:02 |
+---------------------+---------------------+---------------------+

Why did createdon value change???

SHOW CREATE TABLE record;

CREATE TABLE `record` (
  `createdon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modifiedon` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `activeon` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
)

MySQL applies the default rule when timestamp data-type is specified during column creation.
The 1st column of table (without explicit rule) gets a rule for update CURRENT_TIMESTAMP

Which would mean, if during update of record a value is not provided for the column the
value will get updated automatically to current timestamp.

Which was the reason why createdon got changed when activeon was updated!

How to tackle this behavior?

If application is controlling the data value on timestamp columns, when creating the timestamp
column we should ensure to mention the default rule.

CREATE TABLE record (createdon TIMESTAMP NULL DEFAULT NULL, modifiedon TIMESTAMP NULL DEFAULT NULL);
ALTER TABLE record ADD COLUMN activeon TIMESTAMP NULL DEFAULT NULL;

This helps in avoiding surprises!

Encountered a "problem of sorting" today when attempting to display list of record
based on status values.

status value can be one of these (New, In Progress, Deferred, Closed)

ORDER BY status would result in (Closed, Deferred, In Progress, New)

It follows the alphabetical order. However, for our list New and In Progress carried more weight than Deferred and Closed.

MySQL provides CASE operator which can be handy in solving it.

ORDER BY CASE status WHEN 'New' THEN 1 WHEN 'In Progress' THEN 2 WHEN 'Deferred' THEN 3 ELSE 4 END;

MySQL also provides FIELD function that can be used to achieve the same:

ORDER BY FIELD (status, 'Closed', 'Deferred', 'In Progress', 'New') DESC;

CASE vs FIELD

* FIELD needed list of all values and DESC order in this case.
* CASE needed specific weight for each value and provided default handling (ELSE).
* CASE provides usage of LIKE and other operators on the target field.
  http://stackoverflow.com/questions/3579760/mysql-case-in-select-statement-with-like-operator

NOTE: 
Be sure to index the columns on which CASE / FIELD operates on to avoid performance penalty.
Avoiding LIKE comparison is also advised to take advantage of the indexing.