How to convert time string to sql timestamp in PHP

Question

I want to insert a time string that i got via HTTP POST to a mysql table with timestamp field. How do I do it?

My PHP code is

$time_string="Apr 23, 2019 4:30:15 PM";
$query = $database->prepare('INSERT INTO trips(trip_start) VALUES(?)') {
$query->bind_param("s", $time_string]);
$query->execute();

Here trip_start is a timestamp datatype in MYSQL

What's the problem with the given code, besides multiple syntax errors? — Nico Haase, Apr 23 '19 at 14:53
Possible duplicate of [PHP mysql insert date format](https://stackoverflow.com/questions/12120433/php-mysql-insert-date-format) — Dave, Apr 23 '19 at 15:04

score 0 · Answer 1 · edited Apr 23 '19 at 15:31

0

I would recommend to use Carbon library to work with date formats as it makes this and other operations really easy. In this particular case i would do something like this:

$carbonDate = new Carbon\Carbon('Apr 23, 2019 4:30:15 PM');
$timeStamp = $carbonDate->toDateTimeString();

Now timeStamp is a valid timestamp for mysql.

edited Apr 23 '19 at 15:31

Adrian W

4,563
11
38
52

answered Apr 23 '19 at 15:09

accexs

98
8

score 0 · Answer 2 · answered Apr 23 '19 at 15:41

Use PHP's DateTime to convert from one format to another. You don't need any dependencies, such as Carbon - which is bloat and overkill for your use case.

What you need is to tell PHP what the input date format is and what you want for the output.

Your code states "Apr 23, 2019 4:30:15 PM" as the date. Code that would convert between your input and what MySQL expects is the following:

$date = 'Apr 23, 2019 4:30:15 PM';
$input_format = 'M d, Y H:i:s A'; // Apr 23, 2019 4:30:15 PM
$output_format = 'Y-m-d H:i:s' // 2019-04-23 16:30:15
$timezone = new \DateTimeZone("UTC"); // Make sure to correctly choose your time zone

$dt = \DateTime::createFromFormat($input_format, $date, $timezone);

echo $dt->format($output_format); // echoes "2019-04-23 16:30:15"

No need for external dependencies, quick, simple and readable.

How to convert time string to sql timestamp in PHP

2 Answers2