Converting MySql DateTime type into something more friendly

Question

I have a Java web app that reads a MySql db and returns DateTime fields. What is the best way to convert the DateTime fields returned in the resultset into something more readable?

Currently the DateTime fields print as:

2008-12-14 16:30:00

but I would like something more user friendly like:

14 Dec 2008 at 16:30

I am populating an ArrayList with these dateTimes from a pojo. I would like to format them before adding to the arrayList, so then I can just print the contents of the arrayList in the JSP.

Answer 1

Mysql date_format()

mysql> select date_format(now(),'%d %b %Y at %H:%i') as formated_date;
+----------------------+
| formated_date        |
+----------------------+
| 20 Dec 2008 at 10:56 | 
+----------------------+

Answer 2

What kind of abstraction layer do you have over the database? It looks like a simple case of subclassing whatever you're using as the field class, and adding a method for your format.

Answer 3

Another option is to use the JSTL. The formatting library makes it easy to display a date in any format and it is i18n aware. The advantage is that you can leave the date as a Date object and manipulate it as such but only convert it when you need to display. The format tag will look like this:

<fmt:formatDate value="${myDate}" dateStyle="MEDIUM"/>

Like I said above, one big advantage is that it is i18n aware so you can display the date in a localized format.

The full syntax is:

 <fmt:formatDate value="expression" 
     timeZone="expression"
     type="field" dateStyle="style" 
     timeStyle="style"
     pattern="expression"
     var="name" scope="scope"/>

Answer 4

using the date_format function. This website will help you format the date http://www.mysqlformatdate.com

Answer 5

I would prefer to use a simpleDateFormat in the Java code so my code is not bound to any database function (weak reason, I know).

    Date date = ...
    DateFormat df = new SimpleDateFormat("dd MMM yyyy 'at' HH:mm");  // Locale?
    String text = df.format(date);

Be warned that SimpleDateFormat is not thread safe. From the documentation:

Date formats are not synchronized. It is recommended to create separate format instances for each thread. If multiple threads access a format concurrently, it must be synchronized externally.

Answer 6

I'd recommend the tag formatting option, because you have the real data (dates/times) and you format at page level where format is relevant (and you know.. by example, locale to apply).

If you anyway want to format it and put the strings into a List use java.text.SimpleDateFormat as Carlos Heuberger says.