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 = "neveropen"; // 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 = "neveropen"; // 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 = "neveropen"; // 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.
