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

filter_none

edit
close

play_arrow

link
brightness_4
code

<?php
  
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "geeks";
  
// 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);
?>

chevron_right


Output:

Table date_test created successfully

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

filter_none

edit
close

play_arrow

link
brightness_4
code

<?php
  
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "geeks";
  
// 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 coneection
mysqli_close($conn);
?>

chevron_right


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

edit
close

play_arrow

link
brightness_4
code

<?php
  
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "geeks";
  
// 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);
?>

chevron_right


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



My Personal Notes arrow_drop_up

COMPETITIVE PROGRAMMER

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.



Improved By : AnnapurnaAgrawal



Article Tags :
Practice Tags :


2


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.