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

Character count problem

You have a string of small letters a-e, which can be represented as a regular expression in the form of [a-e]*

Example: abcdef, eafdceeafe, etc etc

Problem: Write a program that counts maximum continuous occurrence of a character and their indexes. For example, if the string is "abbbbdcdef", it will be "b" : 4, starting at index 1 to 4.

public class TestStringCharCount {

   public static void main(String[] args) {

        int start = 0;
        int count = 0;
        int maxCount = 0;
        char maxChar = 'a';
        String str = "abcdefabbbbbbbbcdaeff";
        char[] chars = str.toCharArray();

        for (int i = 0; i < chars.length; i++) {
            count = 0;
            for (int j = i; j < chars.length; j++) {

                if (chars[i] == chars[j]) {
                    count++;
                } else {
                    break;
                }
                i = j;
            }

            if (count > maxCount) {
                maxCount = count;
                maxChar = chars[i];
                start = i;
            }

        }
        System.out.println("string = "+ str + ", length = "+ str.length());
        
        System.out.println(maxChar + ":" + maxCount + " starting at "
                + (start - maxCount) + " to " + start);
    }
}

Output: 

string = abcdefabbbbbbbbcdaeff, length = 21
b:8 starting at 6 to 14