Format date in MySQL SELECT as ISO 8601

Question

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?

Rozamond

Answer 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 

Hypotonic

This is not works when in some zones like spain. They needs time difference instead of Z after time +01:00. In php this works fine: date("c", strtotime($comment['datetime'])), – Ambros

Doesn't work for me either, the last format value is something like 530Z instead of +02:00 – Gad

@Ambros That's because this function assumes the DateTime is in UTC. – Dumbbell

@MladenJanjetović Z is not a parameter (otherwise it would have been preceded by %), it is just the convention used by ISO 8601 to specify that the timezone is UTC. Therefore if you store the datetime in a different time zone, first convert it to UTC and then use this format string. It is good practice to send around ISO 8601 dates in UTC time zone and not in your own server time zone. – Ruphina

CONVERT_TZ is the MySql function you need to convert your datetime to a different time zone. – Ruphina

@DomenicoDeFelice - sorry wrong expression, I see the Zulu time flag is not the param. Didn't work for me anyway. Maybe it's the zone problem as David said. I will try CONVERT_TZ next time I stuck with it – Seedman

Not exactly the question asked, but Google took me here: to get the ISO string format with milliseconds I had to use DATE_FORMAT(date, '%Y-%m-%dT%T.000Z') – Draper

Answer 2

This worked for me

DATE_FORMAT( CONVERT_TZ(`timestamp`, @@session.time_zone, '+00:00')  ,'%Y-%m-%dT%TZ')

Flagrant

It is the correct solution. Assuming UTC without a timezone conversion is an error that will generate unexpected results. – Unicameral

this will display current time, the OP request a time based on the field date – Childlike

Answer 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

Soughtafter

Answer 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"

Milurd

That's a PHP function, not a MySQL function. – Athanor

The OP expressed difficulty performing the operation on PHP. I was offering that portion of the answer. – Milurd

Answer 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.

Oberland

date("c", strtotime($sqldata['time'])); php date function – Schnorr

Just a a side note: by using PHP to convert dates you could incur in issues with server's memory, if there is a very big data set. – Priest

@GuntramBlohmsupportsMonica Perhaps not, but OP was, and so I offered a PHP solution. – Oberland

Answer 6

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 

Cordillera

Answer 7

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

Evaporite

Answer 8

Another solution to format the DateTime supporting any timezone:

SELECT CONCAT(DATE_FORMAT( `date` ,'%Y-%m-%dT%T'), TIME_FORMAT( TIMEDIFF(NOW(), UTC_TIMESTAMP), '+%H:%i') ) AS date_formatted;

It will add the timezone information of the date.

Unicameral