MYSQL TIMESTAMP when adding DATE_FORMAT then the output is blank, PHP conflict?

Question

Usingthe following shortcode function, I have the code building a table by querying the DB. That all works fine. BUT when I add any type of formatting to the TimeStamp column such as DATE_FORMAT or TIME the output for that column only goes blank. No errors are in the console to look at. I am trying to extract only the time and put it in a 12 hour format instead of the typical TIMESTAMP format of date and time in the 24hr set up.

Is there a conflict with some of the PHP that causes it not to display the timestamp?

 function test2() {
        global $wpdb;
    $results = $wpdb->get_results("SELECT `name`, `partysize`, `phonenumber`, `emailaddress`, DATE_FORMAT(`Time_stamp`, '%h %i' ), `Wait`, `currentstatus` FROM mytablename m RIGHT JOIN (SELECT wdt_ID, CONCAT(ROUND(Time_to_sec(TIMEDIFF (NOW() ,`Time_stamp`))/60,0), ' Min') AS Wait FROM mytablename) as t on m.wdt_ID = t.wdt_ID WHERE Time_stamp >= date_sub(now(),interval 3 hour)  "); 
        if(!empty($results))
        {
            echo "<table width='100%' border='0' style='display:inline-table'>
            <th>Name</th>
            <th>Party Size</th>
            <th>Phone Number</th>
            <th>Email Address</th>
            <th>Time Stamp</th>
            <th>Status</th>
            <th>Wait</th>
            <th>Action</th>     ";
            echo "<tbody>";      
            foreach($results as $row){ 
                echo "<tr>";
                //echo "<td>" . $row->wdt_ID . "</td>";
                echo "<td>" . $row->name . "</td>";
                echo "<td>" . $row->partysize . "</td>";
                echo "<td>" . $row->phonenumber . "</td>";
                echo "<td>" . $row->emailaddress . "</td>";
                echo "<td>" . $row->Time_stamp . "</td>";
                echo "<td id='tdid_".$row->wdt_ID."'>" . $row->currentstatus . "</td>";
                echo "<td>" . $row->Wait . "</td>";
                echo '<td> <a href="javascript:void(0)" class="button seated-btn" onclick="seatclick('.$row->wdt_ID.')" data-id="'.$row->wdt_ID.'"></a></td>';
                echo "</tr>";
            }
            echo "</tbody>";
            echo "</table>"; 
        }
    }
    add_shortcode('test2', 'test2');

EDIT 1 As per Eric7777777 I have added the print and its output is below when using just the Time_stamp:

Array
(
    [0] => stdClass Object
        (
            [wdt_ID] => 9
            [name] => test2
            [partysize] => 3
            [phonenumber] => 465
            [emailaddress] => dis@doc.com
            [Time_stamp] => 2020-09-24 19:02:47
            [currentstatus] => Waiting
            [Wait] => 138 Min
        )

)

and with the DATE_FORMAT(Time_stamp:

Array
(
    [0] => stdClass Object
        (
            [DATE_FORMAT(Time_stamp, '%h %i %p')] => 07 02 PM
        )

)

@GMB it is a TYPE - timestamp as its default value is CURRENT_TIMESTAMP — michael, Sep 24 '20 at 23:05

score 1 · Answer 1 · answered Sep 25 '20 at 00:59

1

In Database you need add a type of TIMESTAMP UTF8 general_ci. It may be help.

And show us what displaying in var_dump` or print_r of your array.

Special code is

print_r($your_array);

or

echo '<pre>';
   print_r($your_array);
echo'</pre>';

answered Sep 25 '20 at 00:59

Eric7777777

111
7

thanks! I would love to add the UTF but since it is NOT null, it will not accept any collation. I am updating with an edit above for the print results – michael Sep 25 '20 at 01:21

score 0 · Answer 2 · answered Sep 25 '20 at 07:57

Is there a conflict with some of the PHP that causes it not to display the timestamp?

What are you trying to output? $row->Time_stamp.

Now your original object had such a property, as your debug output showed:

[Time_stamp] => 2020-09-24 19:02:47

Now look closely at your second debug output, what’s in there now?

[DATE_FORMAT(Time_stamp, '%h %i %p')] => 07 02 PM

That is a totally different property name than before.

Now while you could try and access that via the curly braces syntax ($row->{' DATE_FORMAT(Time_stamp, '%h %i %p')'}), it makes much more sense, that you give this column a different name in your result set, by specifying an ALIAS in your query:

$results = $wpdb->get_results("SELECT …, DATE_FORMAT(`Time_stamp`, '%h %i' ) AS Time_stamp, …");

With that alias, you will be able to access the value as $row->Time_stamp again.

No errors are in the console to look at.

Browser console shows client-side errors, but yours here was on the server side.

You should enable proper PHP error reporting - in this case, PHP would have told you, that you are trying to access a non-existing object property. See How do I get PHP errors to display? for details.

thanks. Maybe I didnt explain it well. I have been trying to extract only the time in the 12 hours format from the Time_stamp by adding DATE_FORMAT or TIME to the column named Time_stamp. When I add either of these in the MYSQL query it blanks the entire return. Interestingly enough this above worked but I am unclear why? — michael, Sep 25 '20 at 15:30

MYSQL TIMESTAMP when adding DATE_FORMAT then the output is blank, PHP conflict?

2 Answers2