Storing javascript Date () in mySQL

Question

I currently have a javascript variable that records the current date and time:

var time_of_call; time_of_call = new Date(); 

and I need to save it in a MySQL database. When I try to load it, the column just looks empty, but I'm not sure what I'm doing wrong. I know this is not a mysql query problem because I tried to enter different values ​​and it works fine.

I have the DATETIME column set, and I'm loading a value that is not formatted. Can someone explain what I need to do differently?

Thanks for any help

Ps I cannot use NOW() because I use this to capture the time during which the record was actually captured, and this_of_call time records the time at which the call actually arrives.

Answer 1

In JavaScript, the base value of a Date object is in milliseconds, and Unix servers (and MySQL inside) use whole seconds.

To get the base value for a javascript date object:

 var pDate = new Date(); var pDateSeconds = pDate.valueOf()/1000; 

From here you send it to the server ... you decide if you need to divide it by 1000, but you need to do it somewhere. From this value, you can simply name something like a PHP date ("Ymd H: i: s", $ pDateSeconds); On him.

Or you can just use the built-in function in MySQL:

 $sql = 'UPDATE table_name SET field_name=FROM_UNIXTIME('.$pDateSeconds.') WHERE field_name='.$row_id; 

Answer 2

You must convert your format. In addition, you are not loading the object.

At least you need to do: time_of_call.getTime(); which returns a timestamp.

After loading the timestamp, you need to convert it to your own DB format, for example: date('dm-Y',(int)$_REQUEST['time_of_call']);

The format of the date depends on whether you used DATE, DATETIME, TIME, TIMESTAMP or INT.

If you used TIMESTAMP or INT (IMHO is best), you do not need to convert.

Important: The javascript timestamp is in milliseconds, while the PHP timestamp is in seconds. You will need to do time=time_of_call.getTime()/1000; to fix it.

Answer 3

Afaik Date does not add a leading zero in the day and month, you must format the date as follows:

 yyyy-mm-dd hh:mm:ss 

To get the expected result, you can use the function:

JavaScript:

 function formatDate(date1) { return date1.getFullYear() + '-' + (date1.getMonth() < 9 ? '0' : '') + (date1.getMonth()+1) + '-' + (date1.getDate() < 10 ? '0' : '') + date1.getDate(); } 

Answer 4

You need to add a date with the correct format: YYYY-MM-DD HH:MM:SS . 10.3.1. DATETIME, DATE, and TIMESTAMP types

So, to convert this, you need to write something like this:

 var t = new Date(); var YYYY = t.getFullYear(); var MM = ((t.getMonth() + 1 < 10) ? '0' : '') + (t.getMonth() + 1); var DD = ((t.getDate() < 10) ? '0' : '') + t.getDate(); var HH = ((t.getHours() < 10) ? '0' : '') + t.getHours(); var mm = ((t.getMinutes() < 10) ? '0' : '') + t.getMinutes(); var ss = ((t.getSeconds() < 10) ? '0' : '') + t.getSeconds(); var time_of_call = YYYY+'-'+MM+'-'+DD+' '+HH+':'+mm+':'+ss; 

Of course, you can cut it all and all that, but you get the point.

Answer 5

 Try This: Quick Java Script Date function :-) /*************************************************** * Function for Fetch Date, Day, Time, Month etc.. * input @param = month, date, time, mysql_date etc.. **************************************************/ function getDateNow(output) { var dateObj = new Date(); var dateString = dateObj.toString(); dateArray = dateString.split(" "); if (output == 'day') { output = dateArray[0]; } else if (output == 'month') { output = dateArray[1]; } else if (output == 'date') { output = dateArray[2]; } else if (output == 'year') { output = dateArray[3]; } else if (output == 'time') { output = dateArray[4]; } else if (output == 'am_pm') { output = dateArray[5]; } else if (output == 'mysql_date') { var dt = new Date(); output = dt.toYMD(); }else { output = dateArray[6]; } return output; } /***************************************************** * Function for Fetch date like MySQL date fromat * type @Prototype ****************************************************/ (function() { Date.prototype.toYMD = Date_toYMD; function Date_toYMD() { var year, month, day; year = String(this.getFullYear()); month = String(this.getMonth() + 1); if (month.length == 1) { month = "0″ + month; } day = String(this.getDate()); if (day.length == 1) { day = "0″ + day; } return year + "-" + month + "-" + day; } })(); /*********************************** * How to Use Function with JavaScript **********************************/ var sqlDate = getDateNow('mysql_date')); /*********************************** * How to Use Function with HTML **********************************/ <a href="javascript:void(0);" onClick="this.innerHTML = getDateNow('mysql_date');" title="click to know Date as SQL Date">JavaScript Date to MySQL Date</a> 

Answer 6

You can use any of these to add a method to retrieve time stamps in SQL format from a JS Date object.

First, at the user's local time:

 Date.prototype.getTimestamp = function() { var year = this.getFullYear(), month = this.getMonth(), day = this.getDate(), hours = this.getHours(), minutes = this.getMinutes(), seconds = this.getSeconds(); month = month < 10 ? "0" + month : month; day = day < 10 ? "0" + day : day; hours = hours < 10 ? "0" + hours : hours; minutes = minutes < 10 ? "0" + minutes : minutes; seconds = seconds < 10 ? "0" + seconds : seconds; return year + "-" + month + "-" + day + " " + hours + ":" + minutes + ":" + seconds; } var d = new Date(); console.log(d.getTimestamp()); 

Second in UTC:

 Date.prototype.getUTCTimestamp = function() { var year = this.getUTCFullYear(), month = this.getUTCMonth(), day = this.getUTCDate(), hours = this.getUTCHours(), minutes = this.getUTCMinutes(), seconds = this.getUTCSeconds(); month = month < 10 ? "0" + month : month; day = day < 10 ? "0" + day : day; hours = hours < 10 ? "0" + hours : hours; minutes = minutes < 10 ? "0" + minutes : minutes; seconds = seconds < 10 ? "0" + seconds : seconds; return year + "-" + month + "-" + day + " " + hours + ":" + minutes + ":" + seconds; } var d = new Date(); console.log(d.getUTCTimestamp()); 

I created a lightweight script that extends a Date object with these and others commonly needed here: https://github.com/rtuosto/js-date-format p>

Answer 7

I found a snippet of time ago that might help depending on where you want to convert:

  function formatDate(date1) { return date1.getFullYear() + '-' + (date1.getMonth() < 9 ? '0' : '') + (date1.getMonth()+1) + '-' + (date1.getDate() < 10 ? '0' : '') + date1.getDate(); }