Format timestamp from MYSQL query

Question

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

MySQL's Date_FORMAT()

John Conde
– John Conde

2013-03-28 19:35:40 +00:00
Commented Mar 28, 2013 at 19:35 — John Conde
– John Conde, Commented Mar 28, 2013 at 19:35

Taryn · Accepted Answer · 2013-03-28 19:54:39Z

8

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')

edited Mar 28, 2013 at 19:54

answered Mar 28, 2013 at 19:37

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1157800 Over a year ago

Is there any way to keep the seconds in this Friday 3/28/2013 12:52:02 PM

Taryn Over a year ago

@user1157800 See my edit, you will use '%W %m/%d/%Y %r %p' as the format

user1157800 Over a year ago

I am sorry but how can I keep 12 hour format with AM/PM respectfully?

Taryn Over a year ago

@user1157800 Sorry I used the wrong one '%W %m/%d/%Y %r %p'

brbcoding · Accepted Answer · 2013-03-28 19:38:18Z

1

DATE_FORMAT(a.CreationDate,'%W %c/%e/%Y %h:%i %p')

answered Mar 28, 2013 at 19:38

brbcoding

13.7k2 gold badges39 silver badges51 bronze badges

Collectives™ on Stack Overflow

Format timestamp from MYSQL query

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related