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 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 type and it's good enough for most of your needs and even transaction recording. I will elaborate on the different DATE and TIME types in a future article perhaps but for now we are using TIMESTAMP, okay?

Sample data and table

code:

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 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:

<?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:

<?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:

<?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. ;)