Date Format in PHP for Insertion in MySQL

1. date() in PHP
2. date_format() in PHP

MySQL is an RDBMS database intended to store relational data. It supports various data types, Date being one of them. As MySQL supports only particular date formats, you need to format the dates before inserting dates into the DB; otherwise, the DB will throw an error.

This article will introduce how to format dates in PHP before inserting them into a MySQL DB.

MySQL supports 5 date formats.

1. DATE: YYYY-MM-DD It only stores the date without time in the range of 1000-01-01 to 9999-12-31. For example, 2021-10-28.
2. DATETIME: YYYY-MM-DD HH:MI:SS. It stores the date with time in the range of 1000-01-01 00:00:00 to 9999-12-31 23:59:59. For example, 2021-10-28 10:30:24
3. TIMESTAMP: YYYY-MM-DD HH:MI:SS. It stores the date with time in the range of 1970-01-01 00:00:01 to 2038-01-09 03:14:17. For example, 2021-10-28 10:30:24
4. TIME: HH:MI:SS. It stores the time without date in the range of -838:59:59 to 838:59:59. For example, 10:30:24
5. YEAR: YYYY or YY. It stores the year either 4 digits or 2 digits in the range of 70(1970)-69(2069) for 2 digits and 1901-2155 | 0000 for 4 digits. For example, 2021.

Before learning the solution, let’s understand the concept of date().

date() in PHP

It is a built-in PHP function that returns the formatted date string.

Syntax of date()

date($format, $timestamp);

Parameters

$format: This is a mandatory parameter that specifies the output date string format. Some of the options are:

1. d - The day of the month in the range of 01 to 31
2. m - A numeric representation of a month in the range of 01 to 12
3. Y - A four-digit representation of a year
4. y - A two-digit representation of a year
5. H - A two-digit representation of an hour in the range of 00 to 23
6. i - A two-digit representation of a minute in the range of 00 to 59
7. s - A two-digit representation of a second in the range of 00 to 59

$timestamp: It is an optional parameter that specifies a Unix timestamp in integer format. If not provided, a default value will be taken as the current local time.

Example code:

<?php
    $formated_DATETIME = date('Y-m-d H:i:s');
    echo $formated_DATETIME. "<br>";
    // 2021-10-27 14:02:16
    $formated_DATE = date('Y-m-d');
    echo $formated_DATE. "<br>";
    // 2021-10-27

    $formated_TIME = date('H:i:s');
    echo $formated_TIME. "<br>";
    //14:03:57

    $formated_YEAR = date('Y');
    echo $formated_YEAR. "<br>";
    // 2021

?>

Output:

2021-10-27 14:02:16
2021-10-27
14:03:57
2021

date_format() in PHP

It is a built-in PHP function that takes the DateTime object as input and returns the formatted date string.

Syntax of date_format()

date_format($dateObject, $format);

Parameters

$dateObject: It is a mandatory parameter that specifies a DateTime object.

$format: This is a mandatory parameter that specifies the output date string format. Some of the options are:

1. d - The day of the month in the range of 01 to 31
2. m - A numeric representation of a month in the range of 01 to 12
3. Y - A four-digit representation of a year
4. y - A two-digit representation of a year
5. H - A two-digit representation of an hour in the range of 00 to 23
6. i - A two-digit representation of a minute in the range of 00 to 59
7. s - A two-digit representation of a second in the range of 00 to 59

Example code:

<?php
    $date = date_create("2021/10/27");

    $formated_DATETIME = date_format($date, 'Y-m-d H:i:s');
    echo $formated_DATETIME. "<br>";

    $formated_DATE = date_format($date, 'Y-m-d');
    echo $formated_DATE. "<br>";

    $formated_TIME = date_format($date, 'H:i:s');
    echo $formated_TIME. "<br>";

    $formated_YEAR = date_format($date, 'Y');
    echo $formated_YEAR. "<br>";
?>

Output:

2021-10-27 00:00:00
2021-10-27
00:00:00
2021