Convert datetimes from MySQL table to ISO8601 to create JSON feed in WordPress for use with FullCalendar

Question

I've written an action in WordPress that grabs the rows from a table and encodes them in JSON format, so I can use them with the FullCalendar javascript event calendar.

The date fields from the table need to be formatted ISO8601.

In other words, when the DB renders the date/time: 2017-08-06 10:22:20, I need it converted after the query to: 2017-08-06T10:22:20 for the date fields in the query.

I'm not concerned about timezone offsets.

My function:

add_action( 'getmyevents', 'get_my_events' );
function get_my_events( $atts = [], $content = null ) {

    // Use WordPress database functions
    global $wpdb;

    // List of events will be stored in JSON format
    $json = array();

    // Query retrieves list of events
    $mytable = $wpdb->prefix . "my_events";
    $myids = $wpdb->get_results("SELECT * FROM " . $mytable );

    // sending the encoded result to success page
    echo json_encode( $myids, JSON_UNESCAPED_SLASHES );

    // return JSON
    return $json;
}

Can someone give me a quick, direct way to convert the datetime strings in the query to ISO8601?

I think this is the answer that you are looking for. https://stackoverflow.com/a/813265/3868097 — Mark, Aug 06 '17 at 15:47
I tried what you suggested, but I'm getting errors when I do so. What I was really hoping for was a way to edit the data after I retrieve the query. Sorry that I wasn't clear about that. — sanitycheck, Aug 06 '17 at 16:12

score 0 · Answer 1 · answered Aug 06 '17 at 16:27

0

Maybe you can try something like this.

Although I don't know the name of your column. Uncomment the print_r to get the column name.

foreach ($myids as $key => $row) {
    // print_r($row);
    $date_reformatted = strtotime($row->date_col);
    $myids[$key]->date_col = date( 'c', $date_reformatted );
}

answered Aug 06 '17 at 16:27

Mark

373
2
11

Interesting idea. Unfortunately, $date_reformatted = strtotime($row->date_col); produces an error, "PHP Catchable fatal error: Object of class stdClass could not be converted to string in line XX" – sanitycheck Aug 06 '17 at 22:40
By the way, there are currently six columns, although more will be added soon, and it would be great if the code could automatically accommodate any new column. Two are currently datetime columns: start (datetime) and end (datetime). The other four columns are id (int), title (varchar), url (varchar), and allDay (varchar). – sanitycheck Aug 06 '17 at 22:59

score 0 · Answer 2 · answered Aug 07 '17 at 02:13

It isn't the ideal answer I was looking for, but I did come up with a working solution. Mark's suggestion about filtering during the query gave me the clue I needed for it.

add_action( 'getmyevents', 'get_my_events' );
function get_my_events( $atts = [], $content = null ) {
    global $wpdb;
    // Values sent via ajax to calendar from my_events table

    // List of events
    $json = array();

    // Query that retrieves events
    $mytable = $wpdb->prefix . "my_events";        
    $myids = $wpdb->get_results( 'SELECT id, title, url, DATE_FORMAT( start, "%Y-%m-%d\T%H:%i:%s" ) as start, DATE_FORMAT( end, "%Y-%m-%d\T%H:%i:%s" ) as end, allDay FROM ' . $mytable );


    // sending the encoded result to success page
    echo json_encode( $myids, JSON_UNESCAPED_SLASHES );

    // return JSON
    return $json;
}

However, if someone else can come up with an answer that doesn't require me to specify columns by name, that would be great. Even better would be not formatting within the query at all, but rather formatting afterward. I always like to minimize processor use by MySQL as much as possible.

I think you can use the table_name.*, date and it will give you all of the columns as well as the date one that you want to add. Also if you tell me the output of that print_r I can help you get that php code to work. — Mark, Aug 07 '17 at 02:58

Convert datetimes from MySQL table to ISO8601 to create JSON feed in WordPress for use with FullCalendar

2 Answers2