MySQL date and time functions, Part 2 - Page 2

April 15, 2003

Date formats

The DATE_FORMAT function is a powerful function that allows you to return a specified date in a number of different ways. For example, those who use American time (MM-DD-YYYY, as you can see at the beginning of this article) instead of the standard International time, this function provides a painless way to convert. Below is a list of format specifiers:

%aAbbreviation of the day (from Sun-Sat)
%bAbbreviation of the month (from Jan-Dec)
%cNumeric month (from 1-12)
%DNumeric day of the month with suffix (1st, 2nd, and so on)
%dNumeric day of the month with two digits(from 00-31)
%eNumeric day of the month with one or two digits(from 0-31)
%HHour (from 00-23)
%hHour (from 01-12)
%iMinutes (from 00-59)
%IHour (from 01-12)
%jDay of the year (from 001-366)
%kHour with one or two digits (from 0-23)
%lHour with one digit (from 1-12)
%MMonth name (from January-December)
%mNumeric month (from 01-12)
%pA.M. or P.M.
%r12-hour time (hh:mm:ss A.M.or P.M.)
%SSeconds (from 00-59)
%sSeconds (from 00-59)
%T24 hour time (hh:mm:ss)
%UWeek (from 00-53, Sunday being the first day of the week)
%uWeek (from 00-53, Monday being the first day of the week)
%VWeek (from 01-53, Sunday being the first day of the week)
%vWeek (from 01-53, Monday being the first day of the week)
%WName of the day in the week (from Sunday-Saturday)
%wDay of the week (from 0 - Sunday, to 6 - Saturday)
%XFour-digit numeric year for the week (Sunday being the first day of the week)
%xFour-digit numeric year for the week (Monday being the first day of the week)
%YFour-digit numeric year
%yTwo-digit numeric year
%%Percentage sign (escaped)
Let's look at converting a standard date into a format used in the US.
mysql> SELECT DATE_FORMAT('2003-07-14','%b %d,%Y');
| DATE_FORMAT('2003-07-14','%b %d,%Y') |
| Jul 14,2003                          |
A subset of this function is the TIME_FORMAT() function, which is identical, but allows you to use those formats to do with time.
The last function we are going to look at specifically will be meaningful to those of you familiar with the concept of Unix time. Unix time is the time in seconds since midnight 1 January 1970, and is used by many applications. The UNIX_TIMESTAMP() function returns the Unix time of the current time When called without a parameter, or converts a specified date if one is supplied.
|       1050267998 |

mysql> SELECT UNIX_TIMESTAMP('2003-07-14');
| UNIX_TIMESTAMP('2003-07-14') |
|                   1058133600 |
Looking at the possibilities available with this function, along with the date and time calculations we've looked at already, you can see there's not always that much need to do this inside the application!

Below is a reference for the MySQL date and time functions. Once you have mastered what we've covered so far, none of the other functions will present anything tricky, but you should give the list a read through - you never know when you'll need them.