Insert datetime method in PHP date of format MySQL

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

Examples:

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 for creating databases and tables

<?php 
  
$servername = "localhost"; 
$username = "root"; 
$password = ""; 
$dbname = "test"; 
  
//  Create a connection 
$conn = mysqli_connect( $servername, $username, $password, $dbname ); 
  
//  Check connection  
if ( !$conn ) { 
  die("Connection failed: " . mysqli_connect_error()); 
} 
  
//  Object for creating a table SQL Query  
$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); 
} 
  
//  Close the connection 
mysqli_close($conn);

Output:

Table date_test created successfully

Example 2: An PHP program that inserts a date into a table.

<?php 
  
$servername = "localhost"; 
$username = "root"; 
$password = ""; 
$dbname = "test"; 
  
//  Create a connection  
$conn = mysqli_connect( $servername, $username, $password, $dbname ); 
  
//  Check connection 
if ( !$conn ) { 
  die("Connection failed: " . mysqli_connect_error()); 
} 
  
//  Used to insert data into the table SQL Query  
$sql = "INSERT INTO date_test( created_at ) 
    VALUES( '2019-01-28 12:39:16' );"; 
  
if (mysqli_query($conn, $sql)) { 
  echo "New record created successfully"; 
} else { 
  echo "Error: " . $sql . "<br>" . mysqli_error($conn); 
} 
  
//  Close the connection 
mysqli_close($conn);

Output:

New record created successfully

Example 3: This example is used to display the 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 display an error message.

SELECT * FROM date_test WHERE DATE( created_at ) = '2019-01-28';

<?php 
  
$servername = "localhost"; 
$username = "root"; 
$password = ""; 
$dbname = "test"; 
  
// Create a connection  
$conn = mysqli_connect( $servername, $username, $password, $dbname ); 
  
//  Check connection 
if ( !$conn ) { 
  die("Connection failed: " . mysqli_connect_error()); 
} 
  
//SQL Query  
$sql = "SELECT * FROM date_test 
WHERE DATE(created_at) = '2019-01-28'"; 
  
$result = mysqli_query( $conn, $sql ); 
  
if ($result) { 
  echo $result; // Print query results  
} 
else { 
  echo "Error: " . $sql . "<br>" . mysqli_error($conn); 
} 
  
//  Close the connection 
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 the DATETIME value, use the functions shown in the following statement:
HOUR (@ dt), MINUTE (@ dt), SECOND (@ dt), DAY (@ dt), WEEK (@ dt), MONTH (@ dt), QUARTER (@ dt), YEAR (@ dt);