Using json_encode to convert MYSQL datetime to Javascript compatible

Question

Possible Duplicate:
Convert date format yyyy-mm-dd => dd-mm-yyyy

I am extracting data from MySQL and passing it to a PHP page, part of which is a Javascript timeline graph.

I am using json_encode to convert the query result into a format that the Javascript timeline requires.

However json_encode outputs the following:

{"start":"2012-06-06 18:05:21", "content":"Start Date", }

Whereas the format the timeline needs is

{"start": new Date(2010,7,23,23,0,0), 'content': "Start Date", }

Can I convert the output to the required format either before, or as part of the json_encode process?

Solution 1

$date = DateTime::createFromFormat('Y-m-d H:i:s', '2012-06-06 18:05:21'); // your original DTO
$newFormat = $date->format('Y,m,d,H,i,s'); // your newly formatted date ready to be substituted into JS new Date();
  unset($date);

$json = json_encode(["start" => $newFormat, 'content' => "Start Date"]);

---

In JS you can do smth. similar to:

var objectFromJSON = some_json_decode_procedure(); // decoding JSON to native object
var dateArray = objectFromJSON.start.split(','); // splitting string to elements for new Date()
objectFromJSON.start = new Date(dateArray[0], dateArray[1], dateArray[2], dateArray[3], dateArray[4], dateArray[5]); // resetting Date() object in the object

Solution 2

Generally speaking, you'll be best off doing this in PHP after you pull the value from the database.

See the answer given Convert date format yyyy-mm-dd => dd-mm-yyyy and adjust the parameters according to http://php.net/date.

You won't be able to do exactly what you want though, as json_encode() will always wrap values in quotes and there is no way to have a native JavaScript statement in the data.

Your best bet will be converting the date into a normal string with the format Month Day, Year Hours:Minutes:Seconds.

New Date() can understand that format - then just do a New Date(data.start) in JavaScript after pulling the JSON.

Comments

[highfidelity]

Thanks, I tried this but ran into the problem you outlined - json_encode wraps the values in quotes which means I cant use it without further manipulation. So I have abandoned json_encode and just put the dates and instructions into an array. I used the code provided by Paul Rawkeen below, so I will accept his answer, hope you dont mind :)

[highfidelity]

See comments above, but using a combination of Pekka's instructions and your code I got this work with an array as opposed to json_encode. Thanks for the help

[Pekka]

@high no problem - but neither my nor Paul's suggestion makes it necessary to abandon json_encode()? Just pass the date as a normal string and do the New Date() in JavaScript. If you put it in M D,Y H:i:s format, you don't even need to split it on JS side

[Pekka]

Why not encode the date in a way that new Date() can understand without having to split it? (y m d, h:i:s)

[Paul T. Rawkeen]

@Pekka, It is just the way of implementation. Maybe not the simplest one, but working :). Pre-formatted date is also Ok, but using this approach we can use some of transmitted date elements somewhere later, but it doesn't really matter bec. we can extract it from Date() object in JS. :)

[Paul T. Rawkeen]

@highfidelity, You are welcome!