Convert from MySQL datetime to another format with PHP

Question

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

User · Answer

To correctly format a DateTime object in PHP for storing in MySQL use the standardised format that MySQL uses  which is ISO 8601   PHP has had this format stored as a constant since version 5 1 1  and I highly recommend using it rather than manually typing the string each time    dtNow   new DateTime     mysqlDateTime    dtNow- gt format DateTime  ISO8601     This  and a list of other PHP DateTime constants are available at http   php net manual en class datetime php datetime constants types

User · Answer

Finally the right solution for PHP 5 3 and above   added optional Timezone to the Example like mentioned in the comments    date    DateTime  createFromFormat  Y-m-d H i s    mysql source date  new  DateTimeZone  UTC      date- gt setTimezone new  DateTimeZone  Europe Berlin        optional echo  date- gt format  m d y h i a

User · Answer

date       date  Y-m-d H i s   strtotime str replace  -          POST  date

User · Answer

This should format a field in an SQL query   SELECT DATE FORMAT   fieldname      d- m- Y    FROM tablename

User · Answer

Use the date function    lt  php     echo date  m d y g i  A     DB Date Field     gt

User · Answer

Forget all  Just use    date   date  Y-m-d H i s  strtotime str replace      -   date

User · Answer

You can also have your query return the time as a Unix timestamp  That would get rid of the need to call strtotime   and make things a bit less intensive on the PHP side     select  UNIX TIMESTAMP timsstamp  as unixtime from the table where id   1234    Then in PHP just use the date   function to format it whichever way you d like    lt  php   echo date  l jS  of F Y h i s A    row- gt unixtime     gt    or    lt  php   echo date  F j  Y  g i a    row- gt unixtime     gt    I like this approach as opposed to using MySQL s DATE FORMAT function  because it allows you to reuse the same query to grab the data and allows you to alter the formatting in PHP   It s annoying to have two different queries just to change the way the date looks in the UI

User · Answer

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

User · Answer

SELECT   DATE FORMAT demo dateFrom    e  M  Y   as dateFrom   DATE FORMAT demo dateUntil    e  M  Y   as dateUntil FROM demo   If you dont want to change every function in your PHP code  to show the expected date format  change it at the source - your database   It is important to name the rows with the as operator as in the example above  as dateFrom  as dateUntil   The names you write there are the names  the rows will be called in your result   The output of this example will be    Day of the month  numeric  0  31    Month name  January  December    Year  numeric  four digits   Example  5 August 2015  Change the dots with the separator of choice and check the DATE FORMAT date format  function for more date formats

User · Answer

valid date   date   m d y g i A   strtotime  date      Reference  http   php net manual en function date php

User · Answer

If you are using PHP 5  you can also try   oDate   new DateTime  row- gt createdate    sDate    oDate- gt format  Y-m-d H i s

User · Answer

To convert a date retrieved from MySQL into the format requested  mm dd yy H M  AM PM         datetime is something like  2014-01-31 13 05 59  time   strtotime  datetimeFromMysql    myFormatForView   date  m d y g i A    time       myFormatForView is something like  01 31 14 1 05 PM   Refer to the PHP date formatting options to adjust the format

User · Answer

Depending on your MySQL datetime configuration  Typically  2011-12-31 07 55 13 format  This very simple function should do the magic   function datetime         return date   Y-m-d H i s   time        echo datetime       display example  2011-12-31 07 55 13   Or a bit more advance to match the question   function datetime  date string   false        if    date string                 date string   time              return date  Y-m-d H i s   strtotime  date string

User · Answer

You can have trouble with dates not returned in Unix Timestamp  so this works for me     return date  F j  Y g i a   strtotime substr  datestring  0  15

User · Answer

The approach I suggest works like the following  First  you create a basic datetime object from a mysql-formatted string  and then you format it the way you like  Luckily  mysql datetime is ISO8601-compliant  so the code itself could look quite simple and elegant  Keep in mind though that datetime column doesn t have a timezone information  so you need to convert it appropriately   Here s the code    new ISO8601Formatted      new FromISO8601  2038-01-19 11 14 07         m d Y h iA         - gt value      It outputs 01 19 2038 11 14AM -- hopefully what you expect   This example uses meringue library  You can check out some more of it if you fancy

User · Answer

Using PHP version 4 4 9  amp  MySQL 5 0  this worked for me    oDate   strtotime  row  PubDate      sDate   date  m d y   oDate   echo  sDate   PubDate is the column in MySQL

User · Answer

An easier way would be to format the date directly in the MySQL query  instead of PHP   See the MySQL manual entry for DATE FORMAT   If you d rather do it in PHP  then you need the date function  but you ll have to convert your database value into a timestamp first

User · Answer

This will work     echo date  m d y H i  A   strtotime  data from mysql

[php] Convert from MySQL datetime to another format with PHP

Examples related to php

Examples related to mysql

Examples related to datetime