Home  >  Article  >  Backend Development  >  PHP date() format when inserting datetime in MySQL

PHP date() format when inserting datetime in MySQL

藏色散人
藏色散人Original
2019-01-29 13:58:015128browse


MySQL retrieves and displays the DATETIME value in 'YYYY-MM-DD HH:MM:SS' format. Dates can only be stored in this type, but it can be used with many time format functions to change and display it.

PHP date() format when inserting datetime in MySQL

#When writing a query in MySQL using PHP, its suitability is checked against MySQL itself. So use the default date and time format provided by MySQL, which is 'YYYY-MM-DD'

Example:

ATE: YYYY-MM-DD
Example: 2019-01-28

DATETIME: YYYY-MM-DD HH:MI:SS
Example: 2019-01-28 23:50:30

TIMESTAMP: YYYY-MM-DD HH:MI:SS
Example: 2019-01-28 23:50:30

YEAR: YYYY or YY

MySQL query for creating DataBase:

CREATE DATABASE Date_time_example;

Example 1: PHP program to create database and tables

<?php 
  
$servername = "localhost"; 
$username = "root"; 
$password = ""; 
$dbname = "test"; 
  
// 创建连接
$conn = mysqli_connect( $servername, $username, $password, $dbname ); 
  
// 检查连接 
if ( !$conn ) { 
    die("Connection failed: " . mysqli_connect_error()); 
} 
  
// 创建表的SQL查询 
$sql = "CREATE TABLE date_test ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    created_at DATETIME 
)"; 
  
if (mysqli_query($conn, $sql)) { 
    echo "Table date_test created successfully"; 
} else { 
    echo "Error creating table: " . mysqli_error($conn); 
} 
  
// 关闭连接
mysqli_close($conn);

Output:

Table date_test created successfully

Example 2: PHP program to insert dates into a table.

<?php 
  
$servername = "localhost"; 
$username = "root"; 
$password = ""; 
$dbname = "test"; 
  
// 创建连接 
$conn = mysqli_connect( $servername, $username, $password, $dbname ); 
  
// 检查连接
if ( !$conn ) { 
    die("Connection failed: " . mysqli_connect_error()); 
} 
  
// 用于向表中插入数据的SQL查询 
$sql = "INSERT INTO date_test( created_at )  
        VALUES( &#39;2019-01-28 12:39:16&#39; );"; 
  
if (mysqli_query($conn, $sql)) { 
    echo "New record created successfully"; 
} else { 
    echo "Error: " . $sql . "<br>" . mysqli_error($conn); 
} 
  
// 关闭连接
mysqli_close($conn);

Output:

New record created successfully

Example 3: This example is used to display rows created on 2019-01-28. Use the following query to display the results. The created_at column contains not only the date but also the time. So it will show error message .

SELECT * FROM date_test WHERE DATE( created_at ) = &#39;2019-01-28&#39;;
<?php 
  
$servername = "localhost"; 
$username = "root"; 
$password = ""; 
$dbname = "test"; 
  
//创建连接 
$conn = mysqli_connect( $servername, $username, $password, $dbname ); 
  
// 检查连接
if ( !$conn ) { 
    die("Connection failed: " . mysqli_connect_error()); 
} 
  
//SQL查询 
$sql = "SELECT * FROM date_test 
WHERE DATE(created_at) = &#39;2019-01-28&#39;"; 
  
$result = mysqli_query( $conn, $sql );  
  
if ($result) { 
    echo $result; //打印查询结果 
}  
else { 
    echo "Error: " . $sql . "<br>" . mysqli_error($conn); 
} 
  
// 关闭连接
mysqli_close($conn);

Output:

id  created_at
1   2019-01-28 12:39:16

To get the year, quarter, month, week, day, hour, minute and second from a DATETIME value, use the function shown in the following statement:
HOUR (@dt), MINUTE (@dt), SECOND (@dt) ), DAY (@dt), WEEK (@dt), MONTH (@dt), QUARTER (@dt), YEAR (@dt);

Recommended reference study:

PHP Tutorialhttp://www.php.cn/course/list/29.html

mysql tutorialhttp://www.php.cn/course/list/51.html


##

The above is the detailed content of PHP date() format when inserting datetime in MySQL. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn