As is known, in MySQL, there are two main types of data to store dates: date and a datetime . The second embodiment, in fact, is an enhanced version of the first, and can store time and date of addition. When removing from the MySQL database of the type we get lines respectively in the following formats:

2012-03-03 12:00:00

However, in the majority of PHP date functions used so-called Unix timestamp format of the date, representing a number - the number of seconds between "Unix epoch" (Midnight, the first in January 1970) and the current time.

You can use the parsing obtained from the above-mentioned lines of the year, month, etc. and transfer them to the PHP-function of the mktime , but I suggest option easier. There is a MySQL-function UNIX_TIMESTAMP , which allows you to convert a date from MySQL format to Unix timestamp format to the stage of the request. It is used as follows:

SELECT created                 AS created_mysql,
Unix_timestamp(created) AS created_unix
FROM articles;

created field is of type datetime. After executing such a request, we obtain the values ​​of the field created in two formats - in MySQL format (created_mysql) and Unix timestamp (created_unix) format. The second embodiment can be directly used directly in the PHP-date functions, for example:

echo date('d.m.Y', $row['created_unix']);

or even as a function of formation of Russian Date:

echo rusdate($row['created_unix'], '%DAYWEEK%, j %MONTH% Y, G:i');

PHP to convert a date from format MYSQL TO NORMAL VIEW ...

I have a date field in php, which uses this code:

How do I convert it to MySql 0000-00-00 format for inclusion in the db. Whether it's in the lines:. Date ( 'Ymd' strtotime ($ date) The reason that I ask is that I have tried variations of this, and I can not get it to work either displayed as 1970 or kakoy-. another version of it. thank you very much

Read more:   1C Connection closed SERVER


php mysql

bollo set July 22 '11 at 16:45

source share

1. If your MySQL column DATEtype:

$date = date('Y-m-d', strtotime(str_replace('-', '/', $date)));

2. If the MySQL column DATETIMEtype:

$date = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date)));

You do not need to work strototime(), because it will not work with a comma dash -, he will try to do subtraction.

Update as your date is formatted, you can not use strtotime(), instead, use this code:

$date = preg_replace('#(\\d{2})/(\\d{2})/(\\d{4})\\s(.*)#', '$3-$2-$1 $4', $date);
echo $date;



Answered Shef July 22 '11 at 16:54

source share

At this site there are two fairly simple solution - just check the code I provided in the description of the case, if you want to - will save you a few clicks.

1. One common solution is to preserve in the date fields DATETIME and use functions date () and strtotime () PHP for converting between time marks PHP and MySQL DATETIME. Methods used are as follows:

$mysqldate = date( 'Y-m-d H:i:s', $phpdate );
$phpdate = strtotime( $mysqldate );

2. Our second option - to let MySQL do the job. In MySQL there are functions that we can use to transform data in a time when we refer to the database. UNIX_TIMESTAMP will convert from DATETIME timestamp in PHP, and FROM_UNIXTIME will convert from the temporary label in PHP DATETIME. Methods used in SQL-query. Therefore, we insert and update the date with the help of such requests like this -

$query = "UPDATE table SET
datetimefield = FROM_UNIXTIME($phpdate)
$query = "SELECT UNIX_TIMESTAMP(datetimefield)
FROM table WHERE...";


Answered John Humphreys - w00te July 22 '11 at 16:50

source share


Are you looking for MySQL functions FROM_UNIXTIME()and UNIX_TIMESTAMP().

Use them in your SQL, such as:

| 1311343579 |
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(1311343579);
| FROM_UNIXTIME(1311343579) |
| 2011-07-22 15:06:19 |
1 row in set (0.00 sec)


Answered Gus July 22 '11 at 16:49

source share


Answered Bailey Parker July 22 '11 at 16:52

source share

If you have a date in dd / mm / yyyy format, use the following command:

$date = explode('/', $_POST['posted_date']);
$new_date = $date[2].'-'.$date[1].'-'.$date[0];

If you have it in mm / dd / yyyy, just change the second line:

$new_date = $date[2].'-'.$date[0].'-'.$date[1];


Answered Warren Sergent 08 February. '12 at 11:36

source share

If you know the date format in the $ _POST [incoming_date], you can use explode for the year, month and time, and then combine to form the actual date mySql. For example, if you get something like 12/15/1988 in the day, you can do it like this

    $date = explode($_POST['intake_date'], '/');
mysql_date = $date[2].'-'$date[1].'-'$date[0];

although if you have a valid date is the date ( 'ym-d', strtotime ($ date)); You should also work


Answered Ehtesham July 22 '11 at 16:58

source share

PHP 5.3 has a function to create and reformat the DateTime object from any format that you specify:

$mysql_date = "2012-01-02";   // date in Y-m-d format as MySQL stores it
$date_obj = date_create_from_format('Y-m-d',$mysql_date);
$date = date_format($date_obj, 'm/d/Y');
echo $date;



MySQL can also control the formatting using the function STR_TO_DATE()when inserting / updating and DATE_FORMAT()when requesting.

$php_date = "01/02/2012";
$update_query = "UPDATE appointments SET start_time = STR_TO_DATE('" . $php_date . "', '%m/%d/%Y')";
$query = "SELECT DATE_FORMAT(start_time,'%m/%d/%Y') AS start_time FROM appointments";


Answered Charlie October 25. '14 at 0:19

source share


function my_date_parse($date)
if (!preg_match('/^(\\d+)\\.(\\d+)\\.(\\d+)$/', $date, $m))
return false;

$day = $m[1];
$month = $m[2];
$year = $m[3];

if (!checkdate($month, $day, $year))
return false;

return "$year-$month-$day";


Answered TMS July 22 '11 at 16:51

source share

Read in other languages

 BelorussianEnglish Deutsch Spanish French Italian Portuguese Turkish Arab Ukrainian Swedish Hungarian Bulgarian Estonian Chinese (Simplified) Vietnamese Romanian Thai Slovenian Slovak Serbian Malay Norwegian Latvian Lithuanian Korean Japanese Indonesian Hindi Hebrew Finnish Greek Dutch Czech Danish Croatian Chinese (Traditional) Philippine Urdu Azeybardzhansky ArmenianPolish Bengal Georgian Kazakh Catalan Mongolski Russian Tadzhitsky Tamilʹskij telugu Uzbetsky

Add a comment

Your e-mail will not be published. Required fields are marked *