MySql sorting fields in which certain order.

MySql provides nice functionality to sort fields in certain order. This is very useful in scenarios where we have an enum Field represented as varchar and the values that it can hold are fairly static.

CHANNELS
——————————–
| ID | NAME | TYPE |
——————————–
TYPE field is a varchar that can take only SD, HD and 3D.

PROBLEM:

To get a list of channels sorted by TYPE in an order of SD, HD and 3D. This can be done in couple of different ways:
-- Using by FIELD
SELECT * FROM CHANNELS ORDER BY FIELD(TYPE, 'SD', 'HD', '3D');

-- Using by FIND_IN_SET
SELECT * FROM CHANNELS ORDER BY FIND_IN_SET(TYPE, 'SD,HD,3D');

Conversion of MySQL Timestamp and DateTime to Unix based TimeStamp

About MySQL TimeStamp

* The Prior version of MySQL v4.1 the timestamp was formatted as like YYYYMMDDHHMMSS”
* since after MySQL v4.1 timestamp and datetime data types are formatted “YYYY-MM-DD HH:MM:SS”.

About Unix Based TimeStamp

* It is differ from MySQL. Unix’s timestamp is a integer value of seconds since January 1, 1970.


How To - Convert

(1) Using MySQL

* you can use Unix_Timestamp() function.
* Example:
SELECT Unix_Timestamp(Created) FROM CreatedItem;

(2) Using PHP
* you can use strtotime() function.
* Example:
$unixTimeStamp = strtotime($createdTimestamp);