Date related functions of MySQL

Easy Solution Web : MySQL Tutorials

A developer has to deal with these below mentioned date related function of MySQL. So let’s understand these function in details.

List of MySQL Date function

  • NOW(): It will give you the current date and time.

For example:

MySQL > select NOW();
      > 2017-02-25 00:52:57;
  • CURDATE(): It will give you current date.

For example :

MySQL > select CURDATE();
> 2017-02-25;
  • UNIX_TIMESTAMP() : It will give you the current date and time in epoch format

For example:

MySQL > select UNIX_TIMESTAMP();
> 1487964177
MySQL > select UNIX_TIMESTAMP('2017-02-25 00:52:57');
> 1487964177
  • FROM_UNIXTIME(): It will give you DATE and TIME from the epoch time format.

For example:

MySQL > SELECT FROM_UNIXTIME(1447430881);
> '2015-11-13 10:08:01'
MySQL > SELECT FROM_UNIXTIME(1447430881) + 0;
> 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')
> 11-04-2014
MySQL > DATE_FORMAT(NOW(),’%b %d %Y %h:%i %p’)
>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 the 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