Formatting date in PHP/MySQL query
How would I, using the DATE_FORMAT, get my fields start and end (both stored as DATETIME) to appear like this: 16/08/2013 00:00:00 etc? It is displaying the data properly, but I am not quite sure how to get DATE_FORMAT working.
What is the problem you are experiencing? Have you seen this reference? w3schools.com/sql/func_date_format.asp
2 Answers 2
Pass the proper format string to DATE_FORMAT in your query:
SELECT *, DATE_FORMAT(start, '%d/%m/%Y %H:%i:%s') AS the_date FROM mystation Then the formatted date will be available in a the_date column when you loop through your rows.
The following formatting string ,suggested by Marty McVry below, also works:
+1 for the reference to the MySQL documentation, and an example that returns a string in the specified format. But it would also be good to show an example of how to assign an alias to that column in the resultset. AS start_dmyt
Beside the solution bellow, that is right: WHEN working with data — in your case passing a date using a REST-API or something — you usually don’t want to apply formating. You should return the data AS RAW as possible, to allow the finally processing application to Format data as required.
use the common Format ‘2013-08-14 20:45:00 GMT+2’ (‘Year-Month-Day Hour:minute:second timezone’) so every Client is able to refactor the data retrieved in an appropriate way.
Data should be converted the Moment it is presented to the user — no second earlier.
(On the raw data Format of a date you can perform < , >, equals comparissions or sorting operations, without doing anything wrong.)
php + mysql вывод даты в другом формате
Как можно сделать вывод даты из mysql в нужном мне формате, ни для кого не секрет что дата хранится в типе date как гггг-мм-дд , мне нужно конвертировать в дд.мм.гггг , при этом не при самом SELECT ‘e, т.е. когда выборка по * и не в цикле перебирать все элементы и конвертировать дату, возможно ли так? Может есть какая-то предустановка, как например кодировку задавать через SET NAMES ?
5 ответов 5
в MySQL нельзя изменить формат по-умолчанию YYYY-MM-DD HH:MI:SS
но вы можете сделать это с помоьюш дополнительных функций как
DATE_FORMAT( datecol , '%d.%m.%Y') AS datecol SELECT DATE_FORMAT(dateColumn,'%d/%m/%Y') AS dateColumn FROM table В MySQL можно изменить формат вывода даты. Собственно вы неплохо описали как это сделать. Наверное, вы все-таки имели в виду «изменить формат вывода даты по-умолчанию» или что-то еще.
Учитывая, что вопрос задан по MySQL + PHP, скорее всего форматирование даты нужно именно для PHP.
Давайте разберемся. MySQL — это слой данных, т.е. модель. А форматирование данных для пользователя — это уже вопрос отображения, т.е. представление.
Отсюда значит, что вопрос подразумевает смешивание слоев модели и представления прямо в модели. И не просто в модели, а прямо средствами СУБД. А это в корне не правильный подход и принесет в будущем разработчику массу проблем. Например, завтра разработчику нужно будет сделать разное форматирование даты для разных языков интерфейса, а также произоводить с датой некие вычисления. Мне не представляется простой путь, как это сделать?
Поэтому мне не вполне понятно, почему заминусовали предыдущий ответ. Т.к. он предлагает более правильный путь:
(new \IntlDateFormatter(‘ru_RU’, \IntlDateFormatter::SHORT, \IntlDateFormatter::NONE))->format(new \DateTime($mySqlResult[‘createdAt’])));
Convert from MySQL datetime to another format with PHP
I have a datetime column in MySQL. How can I convert it to the display as mm/dd/yy H:M (AM/PM) using PHP?
What we need to know is how the date is stored within the SQL. Is it Timestamp or Datetime or unixtime?
They are stored not in unix time, just like a normal date, PHP is the one that deal with it as seconds and stuff. I would recommend you to use the PHP OOP datetime functions, they are very easy to use.
Can I recommend and alternative to your date format? mm/dd/yy is very American, and those of us living in other parts of the world get more than a little irritable on trying to second-guess what is meant by 11-12-13 . The more universal standard is yyyy-mm-dd , and is part of the ISO 8601 standard. Failing that, you should use the month name, not the number.
18 Answers 18
If you’re looking for a way to normalize a date into MySQL format, use the following
$phpdate = strtotime( $mysqldate ); $mysqldate = date( 'Y-m-d H:i:s', $phpdate ); The line $phpdate = strtotime( $mysqldate ) accepts a string and performs a series of heuristics to turn that string into a unix timestamp.
The line $mysqldate = date( ‘Y-m-d H:i:s’, $phpdate ) uses that timestamp and PHP’s date function to turn that timestamp back into MySQL’s standard date format.
(Editor Note: This answer is here because of an original question with confusing wording, and the general Google usefulness this answer provided even if it didnt’ directly answer the question that now exists)
Change date format (in DB or output) to dd/mm/yyyy — PHP MySQL
MySQL stores the date in my database (by default) as ‘YYYY-MM-DD’ The field type for my date is ‘DATE’ (I do not need any time storage).. Is there a simple way to change it by default to DD/MM/YYYY ? I call up different dates in 2 different tables, and no where in any of my code do I have anything resembling a date variable or anything! Hopefully this is a straight forward change?
9 Answers 9
$timestamp = strtotime($date_from_db); echo date('d/m/Y', $timestamp); But this will only work for dates between 1970 and 2038, as timestamps are stored as 32 bits integers, counting from 1970-01-01.
In MySQL, I suppose the date_format function would do the trick.
For example :
mysql> select date_format(curdate(), '%d/%m/%Y'); +------------------------------------+ | date_format(curdate(), '%d/%m/%Y') | +------------------------------------+ | 19/03/2010 | +------------------------------------+ 1 row in set (0.03 sec) And, for the sake of completness, another solution, in PHP, that doesn’t suffer from the limitation of 1970-2038 would be to use the DateTime class, and, especially :
- DateTime::__construct to parse the date returned by the DB
- DateTime::format to format the date to whatever format you want.
For example, this portion of code :
$date = new DateTime('2010-03-19'); echo $date->format('d/m/Y'); would get you this output :
I’ve edited my answer to add more informations ; is your comment-question still valid ? If yes, which possible solution does «this date function» refer to ?
If you choose one of the PHP solutions, you can do that somewhere arround your display, as it’s a matter of presentation ;;; if you choose the SQL solution, well, obvisouly, you’ll do that in your SQL query (But considering this is presentation, I would prefer having this in the view/template, in PHP)
Ok, what would you say is the best solution to use if I am planning on sorting the table rows by date (afterwards), and also running a query that displays items with dates that have passed a deadline, from what the system date is (if that makes sense). e.g. system date is set as 20th march 2010, a deadline for activity is set as 21st march. i plan on changing the colour of the table row to red (just an example) to shows its passed date! Will using PHP or MySQL to format the date make any difference to my requirment?