php - Incorrect Date/Time Formatting when inserting to mysql database


I have a php form that once submitted is supposed to insert at date and time for which the form was submitted. The format I'm looking for is:May 13, 2015, 4:30 pm But instead I'm getting:1969-12-31 19:00:05. Obviously, with the date retrieved from the record there is a formatting error (Dec. 31, 1969).

On the php submission form the hidden field code is:

<input type="hidden" name="tofiles_post_date" value="<?php echo date('m/d/Y F j, Y, g:i a', time()); ?>">

On the php confirmation page where the date is inserted into the mysql database is:

$title = mysql_real_escape_string($_POST['tofiles_title']);
$body = mysql_real_escape_string($_POST['tofiles_body']);
$link = "" . mysql_real_escape_string($_POST['tofiles_link']);
$relation = mysql_real_escape_string($_POST['tofiles_relation']);
$type = mysql_real_escape_string($_POST['tofiles_type']);
$date = mysql_real_escape_string($_POST['tofiles_post_date']);
$post_user = $_SESSION['user_id'];

$sql = "INSERT INTO site_tofiles (tofiles_title, tofiles_body, tofiles_link, tofiles_relation, tofiles_type,  tofiles_post_date, tofiles_post_ip, tofiles_post_user) VALUES ";
$sql .= "('$title', '$body', '$link', '$relation', '$type', '$date', '$ip', '$user_id');";

Obviously, I'm missing something here.




The problem probably lies on the MySQL side: the column oftofiles_post_date is of typeDATETIME orTIMESTAMP rather than a String. In such a case, MySQL expects the data to be in YYYY-MM-DD HH:MM:SS format (documentation: Therefore to insert the date and time into the column you must use that format too. If you want it to be formatted the way you have written, then you should format it on output, after you retrieve the date from the database.

If you look at your database you probably find the data in that column (when you insert the date as you have written) is0000-00-00 00:00:00, which is what is inserted when MySQL can't understand your date. Or at least this is my suspicion because1969-12-31 19:00:05 is suspiciously close to the UNIX epoch with some time zone related offset.




You’re passing a date of the form05/13/2015 May 13, 2015, 4:30 pm via your hidden form field, and I don’t see you converting that to anything else anywhere – yet in comments further down you said, your “date” column was of typeINT … now how is that supposed to make sense?

Now, if you let05/13/2015 May 13, 2015, 4:30 pm get automatically cast to an integer (which will happen, since that is the data type of your column), the result will just be 5, because after05, there’s characters that can not be interpreted as numeric any more.

Since you seem to be then interpreting that value as a unix timestamp again, that is just five seconds from the begin of the unix epoch, which starts at1970-01-01. Add a local timezone into the mix, and that you get1969-12-31 19:00:05 seems perfectly logical – 5 seconds after midnight of1970-01-01, interpreted in a timezone that is five hours behind.

If you want to insert a unix timestamp as an integer value into your database, then you should also pass an integer via your hidden form field – simply pass the result oftime(), without formatting it.

But you should really use an appropriate MySQL data type instead of just an integer. Have a look at orTIMESTAMP would be suitable. (Not that the latter is not to be confused with a unix timestamp.)

And if you just want to insert the current time value at the time the INSERT query is executed, then you can simply use the MYSQL functionNOW() directly in your query (with an appropriate column type) – no need to pass a date value around from the server to the client and back (which also makes it vulnerable to manipulation; a user could send you a file now and pretend they uploaded it two weeks or a year before now.)

People are also looking for solutions to the problem: Transformation between XML & Form -PHP


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.