Know Your MYSQL: Date functions

List of mySQL Date function:

For example:

MYSQL> select NOW();

Output: 2017-02-25 00:52:57;

For example :

MYSQL> select CURDATE();

Output: 2017-02-25;

UNIX_TIMESTAMP() : It will give you the current date and time in epoch format

For example:

MYSQL> select UNIX_TIMESTAMP();

Output: 1487964177

MYSQL> select UNIX_TIMESTAMP(‘2017-02-25 00:52:57’);

Output: 1487964177

FROM_UNIXTIME() : It will give you DATE and TIME from the epoch time format.

For example:

MYSQL> SELECT FROM_UNIXTIME(1447430881);

Output: ‘2015-11-13 10:08:01’

MYSQL> SELECT FROM_UNIXTIME(1447430881) + 0;

Output: 20151113100801

DATE_FORMAT() : It will let you decide in what format you want to show date.

For example :

MYSQL> select DATE_FORMAT(NOW(),’%m-%d-%Y’)

Output: 11-04-2014

MYSQL> DATE_FORMAT(NOW(),’%b %d %Y %h:%i %p’)

Output: Nov 04 2014 11:45 PM

Format you can use :

%a	Abbreviated weekday name (Sun-Sat)
%b	Abbreviated month name (Jan-Dec)
%c	Month, numeric (0-12)
%D	Day of month with English suffix (0th, 1st, 2nd, 3rd, �)
%d	Day of month, numeric (00-31)
%e	Day of month, numeric (0-31)
%f	Microseconds (000000-999999)
%H	Hour (00-23)
%h	Hour (01-12)
%I	Hour (01-12)
%i	Minutes, numeric (00-59)
%j	Day of year (001-366)
%k	Hour (0-23)
%l	Hour (1-12)
%M	Month name (January-December)
%m	Month, numeric (00-12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00-59)
%s	Seconds (00-59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00-53) where Sunday is the first day of week
%u	Week (00-53) where Monday is the first day of week
%V	Week (01-53) where Sunday is the first day of week, used with %X
%v	Week (01-53) where Monday is the first day of week, used with %x
%W	Weekday name (Sunday-Saturday)
%w	Day of the week (0=Sunday, 6=Saturday)
%X	Year for the week where Sunday is the first day of week, four digits, used with %V
%x	Year for the week where Monday is the first day of week, four digits, used with %v
%Y	Year, numeric, four digits
%y	Year, numeric, two digits

Technology Guru