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.

John Green · Accepted Answer · 2011-05-18 12:05:11Z

13

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;

answered May 18, 2011 at 12:05

John Green

13.5k3 gold badges31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Christian Over a year ago

+1 This is the right answer, though I'm skeptic of valueOf(). People should rely on "magic" to get things done. He could as well use getTime(), which is more documented and well known than plain typecasting.

John Green Over a year ago

Thanks. Not magic though, this is an ECMA 1.0 standard.... functionally equivalent to getTime.

Christian Over a year ago

"magic" doesn't mean "not standard", it means doing something else than advertised.

John Green Over a year ago

I hear you, really. I've been using valueOf for the past decade, and most of the 3rd party code I've seen uses this. I actually had to look up getTime to see that it was valid. : )

Christian · Accepted Answer · 2011-05-18 11:44:28Z

3

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.

answered May 18, 2011 at 11:44

Christian

28.3k18 gold badges117 silver badges162 bronze badges

4 Comments

Emyr Over a year ago

Also consider whether you need to convert the client's timezone to the server's timezone or alternatively use an ISO/utc timestamp.

Christian Over a year ago

@Emyr - Agreed, but the "correct" time came from the client, so I suppose this shouldn't be an issue.

Emyr Over a year ago

@Christian Only if all your clients are in the same place.

Christian Over a year ago

Hmm, I see your point. If that were the case, he'll have more problems since the check is being done on the server, where he can't know about the client's TZ.

RJD22 · Accepted Answer · 2011-05-18 11:41:38Z

2

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();
}

answered May 18, 2011 at 11:41

RJD22

10.4k3 gold badges30 silver badges35 bronze badges

4 Comments

John Green Over a year ago

Well, I didn't vote it down, but I think it is a poor solution. Since it is client-side and a string operation, it adds locale & timezone variance issues. Best to keep it as a proper numeric representation all the way through the chain and if you want to store the time variances, do with with a proper Timezone Designator.

RJD22 Over a year ago

@John Well you could send the timezone with it. Remember the OP never asked about getting the timezones right only on how to get the date in the correct format so he can store it. He could use Date.getTimezoneOffset() to get the correct timezone.

John Green Over a year ago

That's fine... I didn't suggest a TZD in my solution either, so I apologize if it seemed I was nitpicking on that. And I'm going to have to backpedal on 'poor solution', because that is an overstatement. What I was attempting to say is that your time solution is based on the client's time and I think a proper UTC would be a better start, with a TZD if wanted/needed.

RJD22 Over a year ago

@John I agree that the use of UTC is a way better solution.

Ryan Tuosto · Accepted Answer · 2017-05-13 21:11:46Z

You could use either of these to add a method for getting SQL formatted timestamps from a JS Date object.

First in 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've created a lightweight script that extends the Date object with these and other commonly needed here: https://github.com/rtuosto/js-date-format

enoyhs · Accepted Answer · 2011-05-18 11:57:59Z

0

You need to add date with correct format, which is: YYYY-MM-DD HH:MM:SS. 10.3.1. The DATETIME, DATE, and TIMESTAMP Types

So to convert that 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 shorten all this and stuff like that, but you get the idea.

edited May 18, 2011 at 11:57

answered May 18, 2011 at 11:52

enoyhs

2,0592 gold badges16 silver badges17 bronze badges

Comments

Nono · Accepted Answer · 2012-07-02 11:19:26Z

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>

diagonalbatman · Accepted Answer · 2011-05-18 12:07:19Z

-1

I found a snippet a while back, which may 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();
}

edited May 18, 2011 at 12:07

answered May 18, 2011 at 11:41

diagonalbatman

18.1k3 gold badges33 silver badges31 bronze badges

3 Comments

Christian Over a year ago

Isn't that pretty much the opposite of what he wants to do?

RJD22 Over a year ago

No it's exactly what he want... The script adds leading zero's to the day and month so the format is correct. If you voted us both down, and you posted your own answer which does exactly the same only in PHP, you should remove your votedowns.

diagonalbatman Over a year ago

Explain? This function has done for me many times what the user wanted to do. Of course as mentioned i did say it depends on where you want the conversion to happen. So if you want it to be on the Client Side, then the JS is the solution - the PHP which you posted (exactly the same output i might add) would be handy if you want to do it on the Server side.

Collectives™ on Stack Overflow

Storing javascript Date() in mySQL

7 Answers 7

4 Comments

4 Comments

4 Comments

Comments

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

4 Comments

4 Comments

4 Comments

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related