MySQL datetime into PHP

Question

I have found a proper solution to my "problem" but even after reading mysql pages, I don't understand the logic behind it.

I currently store registration information in my system in a "datetime" formatted field in one of my tables (YYYY-MM-DD hh:mm:ss).

When I want to display the data on one of my php pages, simply posting the exact field data shows the format mentioned above.

I would THINK simply using date("Y-m-d",$row["DATE"]) where $row["DATE"] corresponds to the particular row value would return the desired format.

Instead I have to use:date("Y-m-d", strtotime($row["DATE"])).

Why is this? My $row["DATE"] field is not a string in the first place. Should I be able to simple rearrange the data stored in a datetime field? Wasn't that the purpose of rebuilding my entire tableset to accomodate datetime?

Answer 1

MySQL has a built in function called date_format which you can use to display the date how you want to.

SELECT DATE_FORMAT(date_field, '%Y-%m-%d') as date_field FROM table_name

The manual has the list of formats and the variables needed to display it that way. Using this method there will be no need to have PHP convert it etc. Plus it is less code on PHP side for something MySQL can handle easily.

EDIT

Sorry, just read you were looking for an explanation.

PHP's date function takes in a UNIX timestamp, which MySQL is not using. MySQL uses a real date format IE: YYYY-MM-DD HH:MM:SS, as you know, this is to be compliant for years later. The UNIX timestamp has a limited range from something like 1969 to 2037 that it is valid for, which makes it really useful for "timestamping" of items such as a chat box message or items they are not expected to be around post those dates, where as the MySQL DATETIME should not die out until the year changes to 5 digits or the world ends.

Read the WIKI on UNIX timestamp for more information on it.

Answer 2

MySQL does allow you to select dates in unix timestamp format, which allows them to be used more easily in PHP, exactly as you requested.

The previous answer seemed to ignore this point, or downplay it due to the range restriction on the unix timestamp, but if it's what you're looking for...

SELECT UNIX_TIMESTAMP(datefield) as u_datefield FROM table

will give you the date in timestamp format, which you can use as you suggested in PHP:

<?php
$showdate = date("Y-m-d",$row['u_datefield']);
?>

As the previous answer suggests, unix timestamps do have a limited range, so if you need dates prior to 1970 or after 2038 it may not be suitable, but for everyday use today it's great.

The main advantage of using timestamps over date strings is that timestamps can be added and subtracted, which is much harder with a date in string format.