Format date in MySQL SELECT as ISO 8601
Solution 1
The DATE_FORMAT(DateColumn)
has to be in the SELECT
list:
SELECT DATE_FORMAT(date, '%Y-%m-%dT%TZ') AS date_formatted
FROM table_name
ORDER BY id DESC
Solution 2
This worked for me
DATE_FORMAT( CONVERT_TZ(`timestamp`, @@session.time_zone, '+00:00') ,'%Y-%m-%dT%TZ')
Solution 3
DATE_FORMAT only works on MySQL date columns, not timestamps.
A UNIX timestamp is an integer containing the number of seconds since Jan 1, 1970 UTC. To format this as an ISO 8601 date you need to use the FROM_UNIXTIME() function instead.
FROM_UNIXTIME takes the same format strings as DATE_FORMAT, so to format a column named 'created' you'd:
SELECT created /* e.g. 1288799488 */ ,
FROM_UNIXTIME(created,'%Y-%m-%dT%TZ') /* e.g. 2010-11-03T08:51:28Z */
FROM table_name
Solution 4
Loading the date field from the database and converting it to ISO format with PHP is straight-forward; see the c
format string to PHP date
: http://www.php.net/manual/en/function.date.php
echo date('c'); // expected "2013-03-08T14:45:37+05:00"
Solution 5
Why is it hard to do it in PHP?
date("Y-m-d\TH:i:sO",strtotime($sqldata['time']));
Anyway, that DATE_FORMAT
needs to be in the fields to select, not tacked on to the end.
Comments
-
Adam almost 2 years
I'm trying to grab the date from my database in a standard timestamp and display it as ISO 8601. I'm unable to easily do it in PHP so I'm trying to do it in my SELECT statement. This is what I have, but it displays an error:
SELECT * FROM table_name ORDER BY id DESC DATE_FORMAT(date,"%Y-%m-%dT%TZ")
What am I doing wrong?