Incorrect Date/Time Formatting when inserting to mysql database

Question

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.

Obviously, you are missing something here . But at first which data type of your `tofiles_post_date` column — Imran, May 18 '15 at 20:14

score 1 · Answer 1 · answered May 18 '15 at 20:33

1

The problem probably lies on the MySQL side: the column of tofiles_post_date is of type DATETIME or TIMESTAMP rather 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 because 1969-12-31 19:00:05 is suspiciously close to the UNIX epoch with some time zone related offset.

answered May 18 '15 at 20:33

Kai Yao

371
1
6

Thanks, Kai. Actually, I figured out the MySQL side. Field structure: Type (timestamp) and Default (CURRENT_TIMESTAMP). Now it retrieves the correct date but in the wrong format. Current output = 2015-05-18 16:40:42. Desired output = May 18, 2015, 4:40 pm. Currently the code on the xml page is: Post Date . Do you how to get it to the desired format? – Beagle Guy May 18 '15 at 21:13
@BeagleGuy To get the desired format: e.g. if the time from MySQL is stored in `$time` variable, use ``. Basically, instead of using `time()` like in your original statement above, we replace it with `strtotime($time)` which takes the MySQL time and converts it into a timestamp value that the date function can use. http://stackoverflow.com/questions/136782/convert-from-mysql-datetime-to-another-format-with-php – Kai Yao May 18 '15 at 21:47
If I change the statement to "$date = date('F j, Y, g:i a', strtotime($time));" and change elements to var(11), then the date incorrectly displays as "0". – Beagle Guy May 18 '15 at 22:01

score 0 · Answer 2 · answered May 18 '15 at 20:59

You’re passing a date of the form 05/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 type INT … now how is that supposed to make sense?

Now, if you let 05/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 after 05, 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 get 1969-12-31 19:00:05 seems perfectly logical – 5 seconds after midnight of 1970-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 – DATETIME or TIMESTAMP 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 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.)

OK, you said in comments furthermore, that this was a legacy system. Then you could still use MySQL function [`UNIX_TIMESTAMP()`](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp) inside your query if the goal is to insert the current unix timestamp value into the INT column. (Otherwise, as said, pass result of `time()` directly via your form.) — CBroe, May 18 '15 at 21:09

Incorrect Date/Time Formatting when inserting to mysql database

2 Answers2