php - Incorrect Date/Time Formatting when inserting to mysql database
401
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 = "http://example.com/uploads/" . 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']);
$ip = $_SERVER['REMOTE_ADDR'];
$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');";
mysql_query($sql);
Obviously, I'm missing something here.
Answer
Solution:
The problem probably lies on the MySQL side: the column of
tofiles_post_dateis of typeDATETIMEorTIMESTAMPrather than a String. In such a case, MySQL expects the data to be in YYYY-MM-DD HH:MM:SS format (documentation: https://dev.mysql.com/doc/refman/4.1/en/datetime.html). 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) is
0000-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:05is suspiciously close to the UNIX epoch with some time zone related offset.Answer
Solution:
You’re passing a date of the form
05/13/2015 May 13, 2015, 4:30 pmvia 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 let
05/13/2015 May 13, 2015, 4:30 pmget 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 at
1970-01-01. Add a local timezone into the mix, and that you get1969-12-31 19:00:05seems 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 of
time(), without formatting it.But you should really use an appropriate MySQL data type instead of just an integer. Have a look at http://dev.mysql.com/doc/refman/5.5/en/datetime.html –
DATETIMEorTIMESTAMPwould 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 function
NOW()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.)