Formatting Dates in MySQL

When you want to format dates stored in a MySQL database to output onto your web pages, you have a choice; either do it with PHP or do it directly in the MySQL query itself.

I usually do it in the MySQL query itself; unless I have a very good reason not to, which is rare.

MySQL Date and Time types

Out of the many Date and Time data types supported in MySQL, we will only discuss the one I use 95% of the time in my MySQL tables i.e. TIMESTAMP. Most people use this data/column type because it is good enough for nearly everything and even transaction recording. I will elaborate on the different Date and Time column types in a future article perhaps but for now we are using TIMESTAMP, okay?

Sample data and table

TABLE: tbl_messages
==================================
msg_id  |msg_time       |poster_id
--------+---------------+---------
 1      |20020922230743 | 1
 2      |20020923010930 | 2
 3      |20020924223015 | 1
 4      |20020926151515 | 1
 5      |20020930001504 | 1

Looking at the sample database table above, we already note that the column type for `msg_time` is TIMESTAMP(14).

Introducing DATE_FORMAT()

To help illustrate the uses of MySQL‘s DATE_FORMAT(), I will list below the different syntax and results for one particular row off our sample table for each situation.

Output date as: “Sep 23rd, 2002 at 01:09:30 hrs”

<?php
$sql = "SELECT DATE_FORMAT(msg_time,'%b %D, %Y at %T hrs')
 FROM tbl_messages
 WHERE poster_id=2";

//  returns "Sep 23rd, 2002 at 01:09:30 hrs"

//  %b = MONTH Text in 3 characters
//  %D = Numeric DAY in the month with suffix (e.g. 1st,2nd,3rd etc)
//  %Y = 4 digit YEAR
//  %T = TIME, 24 hour format
?>

Output date as: “Monday, 23-09-02″

<?php
$sql = "SELECT DATE_FORMAT(msg_time,'%W, %d-%m-%y')
 FROM tbl_messages
 WHERE poster_id=2";

//  returns "Monday, 23-09-02"

//  %W = WEEKDAY Text full
//  %d = Numeric DAY in the month with leading 0
//  %m = Numeric MONTH with leading 0
//  %y = 2 digit YEAR
?>

Output date as: “September 23, 2002 – 01:09 AM”

<?php
$sql = "SELECT DATE_FORMAT(msg_time,'%M %e, %Y - %h:%i %p')
 FROM tbl_messages
 WHERE poster_id=2";

//  returns "September 23, 2002 - 01:09 AM"

//  %M = MONTH full text
//  %e = Numeric DAY in the month, no leading 0
//  %Y = 4 digit YEAR
//  %h = 12 HOUR clock with leading 0
//  %i = MINUTES, numeric with leading 0
//  %p = AM/PM
?>

There are more formatting codes besides the ones listed in the sample queries above but you’d have to seek them out at the MySQL site yourself.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>