Format date in MySQL SELECT as ISO 8601
MysqlDatabaseDate FormatIso8601Mysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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 - Mysql
This worked for me
DATE_FORMAT( CONVERT_TZ(`timestamp`, @@session.time_zone, '+00:00') ,'%Y-%m-%dT%TZ')
Solution 3 - Mysql
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 - Mysql
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 - Mysql
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.
Solution 6 - Mysql
DATE_FORMAT(date, '%Y-%m-%dT%TZ') should be in select clause.
SELECT DATE_FORMAT(date, '%Y-%m-%dT%TZ')
FROM table_name
ORDER BY id DESC
Solution 7 - Mysql
You should move the DATE_FORMAT to the select part of your query like this:
SELECT *, DATE_FORMAT(date,"%Y-%m-%dT%TZ") AS date FROM table_name ORDER BY id DESC