Convert php datetime format to mysql

PHP
1

I have a datetime variable which is being passed in the following format.

02/10/2019 15:48:38

I need to convert to mysql for inclusion in db, but in my script it keeps changing to:

01/01/1970 00:00:00

I have tried the following code but no joy:

$intakedate = $_POST['brdatetimepicker'];
$intakedate = str_replace(' ', '', $intakedate);
$intakedate = DateTime::createFromFormat('d/m/Y H:i:s', $intakedate)->format('Y-m-d H:i:s');

I would be grateful if someone could help with this. Many thanks.

2

What is the MySql table date column type?

Maybe the type is timestamp which is numeric?

3

It is datetime type. Thanks

4

Try echoing $intakedate and see if it is identical to the datetime type expects:

https://dev.mysql.com/doc/refman/8.0/en/datetime.html

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is 'YYYY-MM-DD hh:mm:ss [. fraction ]', the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999' , and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999' . The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see Section 11.3.5, “Fractional Seconds in Time Values”.

Edit:

Invalid DATE , DATETIME , or TIMESTAMP values are converted to the “zero” value of the appropriate type ( '0000-00-00' or '0000-00-00 00:00:00' ), if the SQL mode permits this conversion. The precise behavior depends on which if any of strict SQL mode and the NO_ZERO_DATE SQL mode are enabled; see Section 5.1.11, “Server SQL Modes”.

5

Hi John. Thanks for reply. Got the following code to work. Not sure if it is correct format but it work.

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

Many thanks

1 Like
6

This, for clarity of those that follow, is only required of D/M/Y (“European”) datestrings.
strtotime assumes X/Y/Z format is US timeformat (M/D/Y).

7

In what form is the date in this array element?

8

Here you go…

$datetime = "02/10/2019 15:48:38";
echo $timestamp = date('Y-m-d H:i:s',strtotime($datetime));
// Output: 2019-02-10 15:48:38
9

(which is fine until that date is from yesterday, the 2nd of October, and not February 10th…)

10

Really pretty moot. If your expected date is Oct 10 then just switch the m and the d to to get the proper date format for mysql (YYYY-MM-DD).

$datetime = "02/10/2019 15:48:38";
echo $timestamp = date('Y-d-m H:i:s',strtotime($datetime));

Or using the Datetime Function

$date ='02/10/2019 15:48:38';
$dateTime = new DateTime($date);
echo $formatted_date=date_format ( $dateTime, 'Y-d-m H:i:s' );

Or using date_create_from_format

$date = date_create_from_format('d/m/Y H:i:s', '02/10/2019 15:48:38');
echo date_format($date, 'Y-m-d H:i:s');

Or Object Oriented

$date = DateTime::createFromFormat('d/m/Y H:i:s', '02/10/2019 15:48:38');
echo $date->format('Y-m-d H:i:s');

Take your pick.

11

for future reference

https://www.php.net/manual/en/function.strtotime.php

… Unix timestamp (the number of seconds since January 1 1970 00:00:00 UTC) …

in other words “zero” seconds is 01-01-1970 00:00:00 so if you see that date be highly suspicious of the value you’re parsing

12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.