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

Current epoch time

Perl                    time

PHP                     time()

Java                  long epoch = System.currentTimeMillis()/1000;

MySQL                 SELECT unix_timestamp(now())

Oracle PL/SQL         SELECT (SYSDATE - TO_DATE('01-01-1970 00:00:00','DD-MM-YYY HH24:MI:SS')) * 24 * 60 * 60 FROM DUAL

JavaScript           Math.round(new Date().getTime()/1000.0) getTime() returns time in milliseconds.

Unix/Linux Shell     date +%s