Format timestamp from MYSQL query

date, mysql, sql

Solution

You will want to use `DATE_FORMAT`:

SELECT a.ID, 
  date_format(a.CreationDate, '%W %m/%d/%Y %l:%i %p') CreationDate, 
  a.Content, 
  a.Type, 
  u.Nickname 
FROM Announcements a 
INNER JOIN Accounts u 
  ON a.FromAccountID = u.AccountID 
WHERE a.Status = '1' 
  AND u.Status = '1' 
ORDER BY a.ID DESC

The MySQL docs will show what specifiers you will use to get the format that you want (See SQL Demo).

If you want to keep seconds, then you will use:

date_format(a.CreationDate, '%W %m/%d/%Y %r')

Problem

I have a mysql query that has a column CreationDate in the format of "timestamp". Example: ``` 2013-03-27 18:32:45 ``` I would like for the sql query to format it in this fashion (12hr format with seconds): ``` Friday 3/28/2013 12:52:34 PM ``` I would like to handle this at the sql level rather than php or .js, etc. Query: ``` SELECT a.ID, a.CreationDate, a.Content, a.Type, u.Nickname FROM Announcements a INNER JOIN Accounts u ON a.FromAccountID = u.AccountID WHERE a.Status = '1' AND u.Status = '1' ORDER BY a.ID DESC ```

Original source