MySQL Useful Functions

Posted: February 16, 2010 in Uncategorized

Date Functions

Before looking at the date functions in detail it is worth revisiting the various date datatypes to gain a better understanding of the limitations of date formatting.

Date Datatypes

There are 5 MySQL date datatypes these are:

Datatype Format Info
DATETIME YYYY-MM-DD HH:MM:SS This stores both date and time.
DATE YYYY-MM-DD This only stores the date
TIMESTAMP(length) Varies See Below
TIME HH:MM:SS This stores only the time
YEAR YYYY Stores only the year

The timestamp datatype is somewhat different as it stores the time that a row was last changed. The format also varies according to the length. For example to store the same information as DATETIME, you would specify a length of 14 whereas to store the DATE you would specify a length of 8.

Timestamp Definition Format
TIMESTAMP(2) YY
TIMESTAMP(4) YYYY
TIMESTAMP(6) YYMMDD
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(14) YYYYMMDDHHMMSS

In the ‘cds’ table we have used the DATE for the ‘bought’ field.

mysql> SELECT cds.title, cds.bought 
    -> FROM cds;
+------------------------------+------------+
| title                        | bought     |
+------------------------------+------------+
| A Funk Odyssey               | 2001-10-10 |
| Now 49                       | 2001-10-15 |
| Eurovision Song contest 2001 | 2000-09-08 |
| Abbas Greatest Hits          | 2000-11-05 |
| Space Cowboy                 | 2001-10-10 |
| Sign of the times            | 1987-11-07 |
| The White Album              | 1994-07-20 |
| The Hits                     | 1993-10-07 |
| westlife                     | 2000-06-09 |
+------------------------------+------------+
9 rows in set (0.02 sec)

DATE_FORMAT()

This function allows the developer to format the date anyway that they wish by specifying a sequence of format strings. A string is composed of the percentage symbol '%' followed by a letter that signifies how you wish to display part of the date. These are some of the more common strings to use:
String Displays Example
%d The numeric day of the month 01....10....17....24 etc
%D The day of the month with a suffix 1st, 2nd, 3rd.... etc
%m The numeric month 01....04....08....11 etc
%M The Month name January....April....August etc
%b The Abbreviated Month Name Jan....Apr....Aug....Nov etc
%y Two digit year 98, 99, 00, 01, 02, 03 etc
%Y Four digit year 1998, 2000, 2002, 2003 etc
%W Weekday name Monday.... Wednesday....Friday etc
%a Abbreviated Weekday name Mon....Wed....Fri etc
%H Hour (24 hour clock) 07....11....16....23 etc
%h Hour (12 hour clock) 07....11....04....11 etc
%p AM or PM AM....PM
%i Minutes 01....16....36....49 etc
%s Seconds 01....16....36....49 etc
There are more, but that should be enough for now. There are a couple of things to note. Upper and Lowercase letters in the string make a difference and also that when arranging these strings into a sequence you can intersperse 'normal' characters. For example: The sequence '%d/%m/%y', with forward slashes separating the strings, would be displayed as 01/06/03. The next stage is to use the function DATE_FORMAT() to convert a stored time to a format we want. Syntax:
DATE_FORMAT(date, sequence)

Thus to change the format of the cds.bought field to DD-MM-YYYY we specify the field as the date and the sequence as '%d-%m-%Y'.

DATE_FORMAT(cds.bought, '%d-%m-%Y')

This function is then incorporated into our SQL statement in place of the exiting cds.bought field.

mysql> SELECT cds.title, DATE_FORMAT(cds.bought, '%d-%m-%Y') 
    -> FROM cds;
+------------------------------+-------------------------------------+
| title                        | DATE_FORMAT(cds.bought, '%d-%m-%Y') |
+------------------------------+-------------------------------------+
| A Funk Odyssey               | 10-10-2001                          |
| Now 49                       | 15-10-2001                          |
| Eurovision Song contest 2001 | 08-09-2000                          |
| Abbas Greatest Hits          | 05-11-2000                          |
| Space Cowboy                 | 10-10-2001                          |
| Sign of the times            | 07-11-1987                          |
| The White Album              | 20-07-1994                          |
| The Hits                     | 07-10-1993                          |
| westlife                     | 09-06-2000                          |
+------------------------------+-------------------------------------+
9 rows in set (0.00 sec)

Extraction Functions

As well as using DATE_FORMAT() there are other functions that allow you to extract specific information about a date (year, month, day etc). These include:
Function Displays Example
DAYOFMONTH(date) The numeric day of the month 01....10....17....24 etc
DAYNAME(date) The Name of the day Monday.... Wednesday....Friday etc
MONTH(date) The numeric month 01....04....08....11 etc
MONTHNAME(date) The Month name January....April....August etc
YEAR(date) Four digit year 1998, 2000, 2002, 2003 etc
HOUR(time) Hour (24 hour clock) 07....11....16....23 etc
MINUTE(time) Minutes 01....16....36....49 etc
SECOND(time) Seconds 01....16....36....49 etc
DAYOFYEAR(date) Numeric day of the year 1.....366
To give an example of one of these you can use DAYNAME() to work out which day you were born on. To do this you can specify the date directly to the function without referring to any tables or field. So for my birthday (20th July 1973):
mysql> SELECT DAYNAME('1973-07-20'); 
+-----------------------+
| DAYNAME('1973-07-20') |
+-----------------------+
| Friday                |
+-----------------------+
1 row in set (0.00 sec)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s