Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Format date in MySQL SELECT as ISO 8601

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?

like image 482
Adam Avatar asked Feb 17 '12 01:02

Adam


People also ask

How do I format a date in MySQL?

MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' . The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format.

How do I change date format from YYYY-MM-DD in MySQL?

MySQL uses yyyy-mm-dd format for storing a date value. This format is fixed and it is not possible to change it. For example, you may prefer to use mm-dd-yyyy format but you can't. Instead, you follow the standard date format and use the DATE_FORMAT function to format the date the way you want.

What time zone is ISO 8601?

Time zones in ISO 8601 are represented as local time (with the location unspecified), as UTC, or as an offset from UTC.


1 Answers

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  
like image 165
ypercubeᵀᴹ Avatar answered Sep 28 '22 21:09

ypercubeᵀᴹ