MySQL and Epoch Time

While working on MySQL, I found some useful functions to work with epoch time. They are well documented in the MySQL reference manual. Here is a list of few that I have used for my piece of work.

• UNIX_TIMESTAMP() : Return a UNIX timestamp i.e. current time in epoch. See some examples below.

mysql> SELECT UNIX_TIMESTAMP() as cuurent_epoch_time;
       ->1355992290

• UNIX_TIMESTAMP(DATE) : Return a UNIX timestamp i.e. current time in epoch. See some examples below.

mysql> SELECT UNIX_TIMESTAMP(NOW()) as cuurent_epoch_time;
       ->1355993021
mysql> SELECT UNIX_TIMESTAMP('2012-12-20 10:30:19');
       ->1355979619

• FROM_UNIXTIME(UNIX_TIMESTAMP) : Format UNIX timestamp as a date. See some examples below.

mysql> SELECT FROM_UNIXTIME(1355993021);
       ->2012-12-20 14:13:41
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
       ->2012-12-20 14:22:19

• FROM_UNIXTIME(UNIX_TIMESTAMP , format) : Format UNIX timestamp as per the format specified. See some examples below.

mysql> SELECT FROM_UNIXTIME(1355993021, "%Y");
       ->2012
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), "%M");
       ->December
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');
       ->2012 20th December 02:35:53 2012