Inserting Date&Time FORM in database

[Terminaxx]

Hello Guys,

 

I am trying to put the date and time from a form into a database. But i'm not sure how to manage this.

 

The form looks like this:

<span>Enddate:</span>
<input class="textinput" name="date" placeholder="enddate (dd.mm.yyyy hh:mm)">

I tried this:

$rawdate = input($_POST["date"]);

$date = date('d.m.Y H:i', strtotime($rawdate));

mysqli_query($con, "INSERT INTO games (enddate) VALUES ('$date')"); 

But it doesnt work. It only shows the Date correctly like this: 10-06-2017 00:00:00

 

The time doesnt work. I know maybe its because i only used date(), but i dont know what to use for date AND time.

 

Thanks for any help

Edited June 17, 2017 by Terminaxx

[AntonZelenin]

Your date must be this format 'yyyy-mm-dd H:i:s'. Like '2017-17-06 15:56'.
Or you can do so

$date = strtotime($rawdate);

mysqli_query($con, "INSERT INTO games (enddate) VALUES (FROM_UNIXTIME($date))"); 

[ginerjm]

If your db is defined as a 'datetime' field you don't need the masking of the input.  Just convert it to a datefield like you did. and insert it as a number (no quotes nec.)

 

PS - are you inserting this as a record all by itself with no keys?

[Terminaxx]

If your db is defined as a 'datetime' field you don't need the masking of the input.  Just convert it to a datefield like you did. and insert it as a number (no quotes nec.)

 

PS - are you inserting this as a record all by itself with no keys?

its defined as "timestamp". The problem is, that i want the time in the DB also. But it always shows me "00:00:00" for the time.

 

This was just a demo how i would insert it.

[ginerjm]

How is your actual input formattted?  Read the Manual for strtotime and see how it will be recognized if you want to learn.

[Jacques1]

Neither PHP nor MySQL can read your mind. You can't just throw an arbitrary timestamp format at them and assume they'll magically figure out what you mean.

 

Fumbling with Unix timestamps is also a bad idea, because you're effectively converting a relative timestamp (relative to the default PHP timezone) to an absolute Unix timestamp and then back to a relative timestamp (relative to the MySQL default timezone). There's no guarantee whatsoever that you'll get back the original timestamp. In fact, you probably haven't even thought about time zones and DST yet.

 

The correct approach is to explicitly specify the format that should be parsed and RTFM for the right MySQL format.

<?php

// your custom input format to be parsed by DateTime::createFromFormat()
const INPUT_TIMESTAMP_FORMAT = 'd.m.Y H:i';

// the MySQL format to be created by DateTime::format()
const MYSQL_TIMESTAMP_FORMAT = 'Y-m-d H:i';



// test timestamp
$_POST['date'] = '13.4.2017 21:16';

// parse the input timestamp according to its specific format
$gameTimestamp = DateTime::createFromFormat(INPUT_TIMESTAMP_FORMAT, $_POST['date']);

// now you can convert the timestamp to the MySQL format and insert it
$mysqlGameTimestamp = $gameTimestamp->format(MYSQL_TIMESTAMP_FORMAT);

var_dump($mysqlGameTimestamp);

[Terminaxx]

Thank you very much. Especially for the explanation!