Telling MySQL How to Display Dates or Times

5.3.1 Problem

You want to display dates or times in a format other than what MySQL uses by default.

5.3.2 Solution

Use the DATE_FORMAT( ) or TIME_FORMAT( ) functions to rewrite them.

5.3.3 Discussion

As already noted, MySQL displays dates in ISO format unless you tell it otherwise. To rewrite date values into other formats, use the DATE_FORMAT( ) function, which takes two arguments: a DATE, DATETIME, or TIMESTAMP value, and a string describing how to display the value. Within the formatting string, you indicate what to display using special sequences of the form %c, where c specifies which part of the date to display. For example, %Y, %M, and %d signify the four-digit year, the month name, and the two-digit day of the month. The following query shows the values in the date_val table, both as MySQL displays them by default and as reformatted with DATE_FORMAT( ):

mysql> SELECT d, DATE_FORMAT(d,'%M %d, %Y') FROM date_val;
+------------+----------------------------+
| d | DATE_FORMAT(d,'%M %d, %Y') |
+------------+----------------------------+
| 1864-02-28 | February 28, 1864 |
| 1900-01-15 | January 15, 1900 |
| 1987-03-05 | March 05, 1987 |
| 1999-12-31 | December 31, 1999 |
| 2000-06-04 | June 04, 2000 |
+------------+----------------------------+

Clearly, DATE_FORMAT( ) tends to produce rather long column headings, so it's often useful to provide an alias to make a heading more concise or meaningful:

mysql> SELECT d, DATE_FORMAT(d,'%M %d, %Y') AS date FROM date_val;
+------------+-------------------+
| d | date |
+------------+-------------------+
| 1864-02-28 | February 28, 1864 |
| 1900-01-15 | January 15, 1900 |
| 1987-03-05 | March 05, 1987 |
| 1999-12-31 | December 31, 1999 |
| 2000-06-04 | June 04, 2000 |
+------------+-------------------+

The MySQL Reference Manual provides a complete list of format sequences. Some of the more common ones are shown in the following table:

Sequence

Meaning

%Y

Four-digit year

%y

Two-digit year

%M

Complete month name

%b

Month name, initial three letters

%m

Two-digit month of year (01..12)

%c

Month of year (1..12)

%d

Two-digit day of month (01..31)

%e

Day of month (1..31)

%r

12-hour time with AM or PM suffix

%T

24-hour time

%H

Two-digit hour

%i

Two-digit minute

%s

Two-digit second

%%

Literal %

The time-related format sequences shown in the table are useful only when you pass DATE_FORMAT( ) a value that has both date and time parts (a DATETIME or TIMESTAMP). The following query demonstrates how to display DATETIME values from the datetime_val table using formats that include the time of day:

mysql> SELECT dt,
 -> DATE_FORMAT(dt,'%c/%e/%y %r') AS format1,
 -> DATE_FORMAT(dt,'%M %e, %Y %T') AS format2
 -> FROM datetime_val;
+---------------------+----------------------+----------------------------+
| dt | format1 | format2 |
+---------------------+----------------------+----------------------------+
| 1970-01-01 00:00:00 | 1/1/70 12:00:00 AM | January 1, 1970 00:00:00 |
| 1987-03-05 12:30:15 | 3/5/87 12:30:15 PM | March 5, 1987 12:30:15 |
| 1999-12-31 09:00:00 | 12/31/99 09:00:00 AM | December 31, 1999 09:00:00 |
| 2000-06-04 15:45:30 | 6/4/00 03:45:30 PM | June 4, 2000 15:45:30 |
+---------------------+----------------------+----------------------------+

TIME_FORMAT( ) is similar to DATE_FORMAT( ), but understands only time-related specifiers in the format string. TIME_FORMAT( ) works with TIME, DATETIME, or TIMESTAMP values.

mysql> SELECT dt,
 -> TIME_FORMAT(dt, '%r') AS '12-hour time',
 -> TIME_FORMAT(dt, '%T') AS '24-hour time'
 -> FROM datetime_val;
+---------------------+--------------+--------------+
| dt | 12-hour time | 24-hour time |
+---------------------+--------------+--------------+
| 1970-01-01 00:00:00 | 12:00:00 AM | 00:00:00 |
| 1987-03-05 12:30:15 | 12:30:15 PM | 12:30:15 |
| 1999-12-31 09:00:00 | 09:00:00 AM | 09:00:00 |
| 2000-06-04 15:45:30 | 03:45:30 PM | 15:45:30 |
+---------------------+--------------+--------------+

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net