PHP date() format when inserting datetime in MySQL-PHP Tutorial-php.cn

PHP date() format when inserting datetime in MySQL

藏色散人

Release： 2023-04-04 21:56:02

Original

5152 people have browsed it

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

Copy after login

MySQL query for creating DataBase:

CREATE DATABASE Date_time_example;

Copy after login

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);

Copy after login

Output:

Table date_test created successfully

Copy after login

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);

Copy after login

Output:

New record created successfully

Copy after login

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;;

Copy after login

<?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);

Copy after login

Output:

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

Copy after login

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 Tutorial》//m.sbmmt.com/course/list/29.html

《mysql tutorial》//m.sbmmt.com/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!