Storing javascript Date() in mySQL

Question

I currently have a javascript variable which records the current date and time like so:

var time_of_call;
time_of_call = new Date();

and I need to store it in a MySQL database. When I try to upload it, the column just appears blank but I'm not sure what I'm doing wrong. I know it's not a problem with the mysql query because I have tried entering different values and it works OK.

I have the column set to DATETIME and I am uploading the value unformatted. Could someone please explain what I need to do differently?

Thanks for any help

P.s. I can't use NOW() because I am using that to capture the time that the record is actually captured, and this time_of_call records the time a call actually comes in.

Answer 1

In JavaScript, the underlying value of a Date object is in milliseconds, while Unix servers (and MySQL internally) uses whole seconds.

To get the underlying value for a javascript date object:

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

From here, you'll send it to the server... it is up to you whether or not to divide it by 1000, but it has to be done somewhere. From this value, you could just call something like PHP's date('Y-m-d H:i:s', $pDateSeconds); on it.

Or, you could 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. Also, you don't "upload" an object.

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

After uploading a timestamp, you have to convert to the DB's native format, eg: date('d-m-Y',(int)$_REQUEST['time_of_call']);

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

If you used either TIMESTAMP or INT (which is best IMHO), you don't need any conversion.

Important: A javascript timestamp is in milliseconds, whereas a PHP timestamp is in seconds. You will have to do time=time_of_call.getTime()/1000; to fix this.

Answer 3

Afaik Date doesn't add leading zero's to the day and month you should format the date like this:

yyyy-mm-dd hh:mm:ss

To get the expected result you could use a function:

Javascript:

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