MySQL+PHP returns time along a clean date field

Question

A table on MySQL has several columns of date type, while browsing the table on phpMyAdmin and mysql web front I can see data as being plain dates.

However, when I run a simple script on PHP one of those columns comes back with a timestamp YYYY-MM-DD 00:00:00.

I migrated both the files and databases to a different system and I am no longer able to reproduce the bug.

Is this due to some misconfiguration of PHP/MySQL or could the data have been inserted in a way that it ended up corrupt and querying it returned an unexpected timestamp?

Example: A query like:

"SELECT `cf_719`,`cf_711` FROM `vtiger_ticketcf` WHERE `ticketid`=682"

where both cf_719 abd cf_711 are of type date would return

array(2) { ["cf_719"]=> string(19) "2015-04-08 00:00:00" ["cf_711"]=> string(10) "2015-04-08" } 

EDIT: It seems I found the solution. The problem was a seeming bug in their phpMyAdmin application, where it displayed the field as 'date', however, the mysql command line tool query DESC revealed it to be a datetime type. I don't even know what I should learn from this- trust nothing but command line.

Answer 1

The DATE field's values are a special subset of the DATETIME field, containing midnight (00:00) where the time should be.

I don't know why one instance of php displays only the dates, and another displays the dates and times. But I do know that's unpredictable behavior.

It's ordinarily necessary to format this kind of data before displaying it, so it shows the resolution you want. Even when you want times, you may only want hours and minutes.

This question and answer tells you how to do that in php. Getting time and date from timestamp with php . Something like this will work.

$timestamp = strtotime($row['DATETIMEAPP']);
$date = date('d-m-Y', $timestamp);