CODEWITHSUNDEEP
phpmysql
Alex
Alex

Reputation: 6645

PHP date() format when inserting into datetime in MySQL

What is the correct format to pass to the date() function in PHP if I want to insert the result into a MySQL datetime type column?

I've been trying date('Y-M-D G:i:s') but that just inserts "0000-00-00 00:00:00" everytime.

Upvotes: 412

Views: 903290

Answers (15)

Hassan
Hassan

Reputation: 11

I need Y-m-d this format of date in excel export. This is my code.

$contents .= "<td>". date('Y-m-d', strtotime($row3['DATED'])) . "</td>";

And these are headers

header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
header("Content-Disposition: attachment; filename=Log_Export_Alico_CSV_" . date('Y-m-d') . ".xls");
header("Pragma: no-cache");
header("Expires: 0");

// Output CSV content
echo "\xEF\xBB\xBF"; // UTF-8 BOM
echo $contents;
exit();

When I export the date is still showing m-d-Y.

When I use

$contents = strip_tags($contents);

with this the date format is exported as my desire but the whole format of export data is disturbed.

Please advice me to resolve this problem.

Upvotes: 0

carlo-849978
carlo-849978

Reputation: 21

IMO in addition to the date() function options provided in the previous answers, you should carefully consider its use the server time zone setting which may be different from the database one. If the UTC time zone is needed then the gmdate() function (it has the same options of date()) will be more convenient for the specific case.

Upvotes: 0

onin
onin

Reputation: 5760

Using DateTime class in PHP7+:

function getMysqlDatetimeFromDate(int $day, int $month, int $year): string
{
 $dt = new DateTime();
 $dt->setDate($year, $month, $day);
 $dt->setTime(0, 0, 0, 0); // set time to midnight

 return $dt->format('Y-m-d H:i:s');
}

Upvotes: 5

Bruno
Bruno

Reputation: 621

A small addendum to accepted answer: If database datetime is stored as UTC (what I always do), you should use gmdate('Y-m-d H:i:s') instead of date("Y-m-d H:i:s").

Or, if you prefer to let MySQL handle everything, as some answers suggest, I would insert MySQL's UTC_TIMESTAMP, with the same result.

Note: I understood the question referring to current time.

Upvotes: 5

Ali Umair
Ali Umair

Reputation: 700

$date_old = '23-5-2016 23:15:23'; 
//Date for database
$date_for_database = date ('Y-m-d H:i:s'", strtotime($date_old));

//Format should be like 'Y-m-d H:i:s'`enter code here`

Upvotes: 16

Tim Lytle
Tim Lytle

Reputation: 17624

From the comments of php's date() manual page:

<?php $mysqltime = date ('Y-m-d H:i:s', $phptime); ?>

You had the 'Y' correct - that's a full year, but 'M' is a three character month, while 'm' is a two digit month. Same issue with 'D' instead of 'd'. 'G' is a 1 or 2 digit hour, where 'H' always has a leading 0 when needed.

Upvotes: 128

Mark Elliot
Mark Elliot

Reputation: 77044

The problem is that you're using 'M' and 'D', which are a textual representations, MySQL is expecting a numeric representation of the format 2010-02-06 19:30:13

Try: date('Y-m-d H:i:s') which uses the numeric equivalents.

edit: switched G to H, though it may not have impact, you probably want to use 24-hour format with leading 0s.

Upvotes: 838

Maurici Abad
Maurici Abad

Reputation: 1718

This is a more accurate way to do it. It places decimals behind the seconds giving more precision.

$now = date('Y-m-d\TH:i:s.uP', time());

Notice the .uP.

More info: https://stackoverflow.com/a/6153162/8662476

Upvotes: 3

SandroMarques
SandroMarques

Reputation: 6534

I use this function (PHP 7)

function getDateForDatabase(string $date): string {
    $timestamp = strtotime($date);
    $date_formated = date('Y-m-d H:i:s', $timestamp);
    return $date_formated;
}

Older versions of PHP (PHP < 7)

function getDateForDatabase($date) {
    $timestamp = strtotime($date);
    $date_formated = date('Y-m-d H:i:s', $timestamp);
    return $date_formated;
}

Upvotes: 12

Brian Jones
Brian Jones

Reputation: 69

This has been driving me mad looking for a simple answer. Finally I made this function that seems to catch all input and give a good SQL string that is correct or at least valid and checkable. If it's 1999-12-31 it's probably wrong but won't throw a bad error in MySQL.

function MakeSQLDate($date) {
    if (is_null($date)) {
        //use 1999-12-31 as a valid date or as an alert
        return date('Y-m-d', strtotime('1999-12-31'));
    }

    if (($t = strtotime($date)) === false) {
        //use 1999-12-31 as a valid date or as an alert
        return date('Y-m-d', strtotime('1999-12-31'));
    } else {
        return date('Y-m-d H:i:s', strtotime($date));
    }
}

Upvotes: 1

Adam
Adam

Reputation: 29019

There is no need no use the date() method from PHP if you don't use a timestamp. If dateposted is a datetime column, you can insert the current date like this:

$db->query("INSERT INTO table (dateposted) VALUES (now())");

Upvotes: 8

Ahmed Saber
Ahmed Saber

Reputation: 509

Format time stamp to MySQL DATETIME column :

strftime('%Y-%m-%d %H:%M:%S',$timestamp);

Upvotes: 9

dev4092
dev4092

Reputation: 2891

Format MySQL datetime with PHP

$date = "'".date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $_POST['date'])))."'";

Upvotes: 8

Steffan
Steffan

Reputation: 556

I use the following PHP code to create a variable that I insert into a MySQL DATETIME column.

$datetime = date_create()->format('Y-m-d H:i:s');

This will hold the server's current Date and Time.

Upvotes: 33

JAL
JAL

Reputation: 21563

Here's an alternative solution: if you have the date in PHP as a timestamp, bypass handling it with PHP and let the DB take care of transforming it by using the FROM_UNIXTIME function.

mysql> insert into a_table values(FROM_UNIXTIME(1231634282));
Query OK, 1 row affected (0.00 sec)

mysql> select * from a_table;

+---------------------+
| a_date              |
+---------------------+
| 2009-01-10 18:38:02 |
+---------------------+

Upvotes: 44

Related Questions