DATE and TIME with PDO PHP MYSQL

Question

I'm unable to get the TIME for datetime datatype. I do get date but not able to get time and insert into database using PDO PHP. What needs to be done in the following code to get the time along with date. I tried with H:i:s but it inserts data as 1970-01-01 00:00:00

Date format for input is 08/31/2020 - 05:04 pm

I am looking for date and time(hours and minutes).

$dp = date_create_from_format('m/d/Y',$_POST['dp']);


//CHECKS VALUE
$stmt->bindValue(':dp', $dp->format('Y-m-d'), PDO::PARAM_STR);


//GETTING POST VALUES
$dp=$_POST['dp'];
$dp = date('Y-m-d', strtotime($dp));


//BINDING PARAMETERS
$query->bindParam(':dp', date('Y-m-d', strtotime($dp)));

NOTE: All the above code works fine with getting and inserting date but does not work with date and time both.

What is in `$_POST['dp']`? The format you use in bindParam or bindValue doesn't contain the time, so it's not that surprising it's missing in the database. This article seems to cover it: https://www.plus2net.com/sql_tutorial/date-inserting.php — droopsnoot, Aug 29 '20 at 11:49
dp is date format input. And yes ik H:i:s is missing but i even tried using it but did't work. Instead I got 1970-01-01 00:00:00 — , Aug 29 '20 at 11:54
no give us the format, mysql has a string_to_date, which also covers time, so you can transfer the text directly to mysql and ot then converts it, but we nned to know your format exactly of $dp — nbk, Aug 29 '20 at 12:02
https://stackoverflow.com/questions/1575601/datetime-now-php-mysql-pdo-variant Hope this may help you. — SWastik Thapaliya, Aug 29 '20 at 12:27
Date format for input is 08/31/2020 - 05:04 pm and that in database is YYYY/MM/DD 00:00:00 — , Aug 29 '20 at 14:36
It's because you did `date('Y-m-d',` which, as per the format you've specified there, only outputs the date. You need to add the time components to the format string — ADyson, Aug 29 '20 at 17:29
Well, check the official PHP docs for the date() function and you'll easily find which characters to add to the string so it will output the hours and minutes — ADyson, Aug 29 '20 at 17:31
@droopsnoot Jenny has shown us that in the comment two hours before — ADyson, Aug 29 '20 at 17:32
I already tried ways with hours and minutes but didn't work tbh. Still struggling with this. — , Aug 29 '20 at 17:36
What exactly did you try? If you're struggling then show us what you did — ADyson, Aug 29 '20 at 17:39
$query->bindValue(':dp', $dp->format('Y-m-d'), PDO::PARAM_STR); Tried using h:s:i in date() with trial and error if it's working or not tbh i looked for multiple solutions but didn't find any. — , Aug 29 '20 at 17:43

ADyson · Accepted Answer · 2020-08-30T06:58:41.997

There are two problems:

Your input string is in an unusual format. Therefore you need to use the DateTime::createFromFormat method to parse it (strtotime() can't do the job) using the correct format string.
Your output format is missing the time component - you need to add hours (in 24hr format), minutes and seconds to the string.

Here's a working example:

$dp = DateTime::createFromFormat("m/d/Y - H:i a", $_POST["dp"]);
$dpstr = $dp->format('Y-m-d H:i:s');
$query->bindParam(':dp', $dpstr);

Assuming $_POST["dp"] contains "08/31/2020 - 05:04 pm" then $dpstr will be 2020-08-31 17:04:00.

Demo: http://sandbox.onlinephpfunctions.com/code/b4cc0988c4eee60061502d86f38eccfc97aa9a49

Many many thanks brother ! It works so smoothly ! Got this after hours :) — , Aug 30 '20 at 06:18

DATE and TIME with PDO PHP MYSQL

1 Answers1