You are here:
Web Design > MySQL > Formatting Dates in MySQL.

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.

You are here:
Web Design > MySQL > Formatting Dates in MySQL.