81

I'm trying to display a datetime from my MySQL database as an iso 8601 formated string with PHP but it's coming out wrong.

17 Oct 2008 is coming out as: 1969-12-31T18:33:28-06:00 which is clearly not correct (the year should be 2008 not 1969)

This is the code I'm using:

<?= date("c", $post[3]) ?>

$post[3] is the datetime (CURRENT_TIMESTAMP) from my MySQL database.

Any ideas what's going wrong?

John Slegers
  • 45,213
  • 22
  • 199
  • 169
Matthew James Taylor
  • 4,806
  • 5
  • 29
  • 33

6 Answers6

103

The second argument of date is a UNIX timestamp, not a database timestamp string.

You need to convert your database timestamp with strtotime.

<?= date("c", strtotime($post[3])) ?>
Paolo Bergantino
  • 480,997
  • 81
  • 517
  • 436
  • 1
    Please, use classes! Take a look at @John Conde solution, ;) – German Latorre May 28 '13 at 14:13
  • 3
    **The `date()` function decreases accuracy**, use John's solution instead! – Christian Aug 30 '13 at 19:37
  • @Christian what is 'The date() function decreases accuracy' ? – Yifan Jul 12 '18 at 02:50
  • It does not handle milliseconds (and smaller units) properly. – Christian Jul 13 '18 at 10:39
  • 1
    @Christian The 'c' format doesn't include fractions of seconds and John's answer currently only uses the 'c' format, so John's answer is not an improvement. You're right that DateTime() allows for a higher resolution, but it doesn't help you unless you're using a custom 8601 format with higher res. So, if you're only using 'c' format and you like functional programming, then I think this solution is good. For those that want higher resolution, Clary's answer provides some examples. – ADJenks Aug 27 '20 at 20:40
  • @ADJenks as far as I remember, it's not about the 'c' parameter but rather that date requires a timestamp in seconds as an integer - whereas the DateTime object supports smaller units. In other words, I'm concerned with the storage part, not the formatting part. – Christian Aug 27 '20 at 21:58
  • Yes of course @Christian, I said you were right about DateTime storing time at higher resolution. I would recommend using DateTime if you need that extra resolution, but neither John Conde or John Slegers use the resolution anywhere. I don't think a more powerful thing is better unless you need the power. You might argue that they may use it elsewhere later, sure, but John doesn't. So perhaps I would say "Use DateTime() instead of date() if you need to store time in higher resolution than seconds." but I wouldn't say "use John's solution instead", since they both successfully display ISO 8601. – ADJenks Aug 27 '20 at 22:33
48

Using the DateTime class available in PHP version 5.2 it would be done like this:

$datetime = new DateTime('17 Oct 2008');
echo $datetime->format('c');

As of PHP 5.4 you can do this as a one-liner:

echo (new DateTime('17 Oct 2008'))->format('c');
sinaza
  • 820
  • 6
  • 19
John Conde
  • 217,595
  • 99
  • 455
  • 496
23

Procedural style :

echo date_format(date_create('17 Oct 2008'), 'c');
// Output : 2008-10-17T00:00:00+02:00

Object oriented style :

$formatteddate = new DateTime('17 Oct 2008');
echo $datetime->format('c');
// Output : 2008-10-17T00:00:00+02:00

Hybrid 1 :

echo date_format(new DateTime('17 Oct 2008'), 'c');
// Output : 2008-10-17T00:00:00+02:00

Hybrid 2 :

echo date_create('17 Oct 2008')->format('c');
// Output : 2008-10-17T00:00:00+02:00

Notes :

1) You could also use 'Y-m-d\TH:i:sP' as an alternative to 'c' for your format.

2) The default time zone of your input is the time zone of your server. If you want the input to be for a different time zone, you need to set your time zone explicitly. This will also impact your output, however :

echo date_format(date_create('17 Oct 2008 +0800'), 'c');
// Output : 2008-10-17T00:00:00+08:00

3) If you want the output to be for a time zone different from that of your input, you can set your time zone explicitly :

echo date_format(date_create('17 Oct 2008')->setTimezone(new DateTimeZone('America/New_York')), 'c');
// Output : 2008-10-16T18:00:00-04:00
John Slegers
  • 45,213
  • 22
  • 199
  • 169
8

Here is the good function for pre PHP 5: I added GMT difference at the end, it's not hardcoded.

function iso8601($time=false) {
    if ($time === false) $time = time();
    $date = date('Y-m-d\TH:i:sO', $time);
    return (substr($date, 0, strlen($date)-2).':'.substr($date, -2));
}
Guillaume
  • 443
  • 5
  • 11
7

For pre PHP 5:

function iso8601($time=false) {
    if(!$time) $time=time();
    return date("Y-m-d", $time) . 'T' . date("H:i:s", $time) .'+00:00';
}
Newmania
  • 662
  • 1
  • 12
  • 17
  • 6
    why not escape T sign ... and the !$time condition is not necesary, as current time is used when no time parameter is given or null: date('Y-m-d\TH:i:s\Z', $time);//Z represents current time zone – gregor Oct 29 '10 at 15:15
7

The problem many times occurs with the milliseconds and final microseconds that many times are in 4 or 8 finals. To convert the DATE to ISO 8601 "date(DATE_ISO8601)" these are one of the solutions that works for me:

// In this form it leaves the date as it is without taking the current date as a reference
$dt = new DateTime();
echo $dt->format('Y-m-d\TH:i:s.').substr($dt->format('u'),0,3).'Z';
// return-> 2020-05-14T13:35:55.191Z

// In this form it takes the reference of the current date
echo date('Y-m-d\TH:i:s'.substr((string)microtime(), 1, 4).'\Z');
return-> 2020-05-14T13:35:55.191Z

// Various examples:
$date_in = '2020-05-25 22:12 03.056';
$dt = new DateTime($date_in);
echo $dt->format('Y-m-d\TH:i:s.').substr($dt->format('u'),0,3).'Z';
// return-> 2020-05-25T22:12:03.056Z

//In this form it takes the reference of the current date
echo date('Y-m-d\TH:i:s'.substr((string)microtime(), 1, 4).'\Z',strtotime($date_in));
// return-> 2020-05-25T14:22:05.188Z
Clary
  • 544
  • 1
  • 7
  • 8
  • 1
    For anyone coming here to find a solution between Swift `ISO8601DateFormatter` and your PHP server date formatting - the working one above is `$dt->format('Y-m-d\TH:i:s.').substr($dt->format('u'),0,3).'Z';` Thank you man!!! – Sherbieny Aug 02 '20 at 22:07