What is PHP date() format when inserting into datetime in MySQL?

In this article, we will learn PHP date() format when inserting into datetime in MySQL,This free PHP tutorial for complete beginners will help you learn PHP from scratch.

PHP date() format when inserting into datetime in MySQL️‍🔥

This problem describes the date format for inserting the date into MySQL database. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The date can be stored in this format only. However, it can be used with any time format functions to change it and display it.

When writing a query in MySQL using PHP it’s applicability will be checked on the basis of MySQL itself. So use default date and time format as provided by MySQL i.e. ‘YYYY-MM-DD’

Examples:

DATE: YYYY-MM-DD
Example: 2005-12-26

DATETIME: YYYY-MM-DD HH:MI:SS
Example: 2005-12-26 23:50:30

TIMESTAMP: YYYY-MM-DD HH:MI:SS
Example: 2005-12-26 23:50:30

YEAR: YYYY or YY

MySQL query to create DataBase:

CREATE DATABASE Date_time_example;

Example 1: PHP program to Create database and table

php

<?php 
  
$servername = "localhost"; 
$username = "root"; 
$password = ""; 
$dbname = "Beginner"; 
  
// Create connection 
$conn = mysqli_connect( $servername, $username, $password, $dbname ); 
  
// Check connection 
if ( !$conn ) { 
    die("Connection failed: " . mysqli_connect_error()); 
} 
  
// SQL query to create table 
$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 connection 
mysqli_close($conn); 
?> 

Output:

Table date_test created successfully

Example 2: PHP program to insert date into the table.

php

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

Output:

New record created successfully

Example 3: This example is used to display which row created on 2018-12-05. Use the following query to display result.
The created_at column contains not only date but also time. So it will display error message.

SELECT * FROM date_test WHERE created_at = '2018-12-05';

Output:

(!Important) Wrong Query It returns no rows

Correct Query: To correct it, use the DATE function as follows:

SELECT * FROM date_test WHERE DATE( created_at ) = '2018-12-05';

php

<?php 
  
$servername = "localhost"; 
$username = "root"; 
$password = ""; 
$dbname = "Beginner"; 
  
// Create 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) = '2018-12-05'"; 
  
$result = mysqli_query( $conn, $sql );  
  
if ($result) { 
    echo $result; //printing Query result 
}  
else { 
    echo "Error: " . $sql . "<br>" . mysqli_error($conn); 
} 
  
// Close connection 
mysqli_close($conn); 
?> 

Output:

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

PHP is a server-side scripting language designed specifically for web development. You can learn PHP from the ground up by following this PHP Tutorial and PHP Examples.