JavaScript countdown to MySQL datetime format

Question

I have a date that comes from a MySQL database in the datetime format, something like 2010-09-24 11:30:12. And I need a way to show a countdown of hours:mins to that date.

I'm not very familiar with dates in JavaScript so any help would be apreciated. Thanks.

Answer 1

You may want to use the UNIX_TIMESTAMP() function in MySQL to return your date in Unix Time Format (the number of seconds since January 1, 1970). Let's say our target date is '2011-01-01 00:00:00' (in reality you would probably have a field from your table, instead of a constant):

SELECT UNIX_TIMESTAMP('2011-01-01 00:00:00') AS timestamp;
+---------------+
| timestamp     |
+---------------+
| 1293836400    |
+---------------+
1 row in set (0.00 sec)

Then we can use the getTime() method of the Date object in JavaScript to calculate the number of seconds between the current time and the target time. Once we have the number of seconds, we can easily calculate the hours, minutes and days:

var target = 1293836400;    // We got this from MySQL
var now = new Date();       // The current tume

var seconds_remaining = target - (now.getTime() / 1000).toFixed(0);
var minutes_remaining = seconds_remaining / 60;
var hours_remaining = minutes_remaining / 60;
var days_remaining = hours_remaining / 24;

alert(seconds_remaining + ' seconds');     // 8422281 seconds
alert(minutes_remaining + ' minutes');     // 140371.35 minutes
alert(hours_remaining + ' hours');         // 2339.5225 hours
alert(days_remaining + ' days');           // 97.48010416666666 days

Answer 2

You can use jquery plugin http://keith-wood.name/countdown.html

Just pass your mysql date and time to js as js variable

syntax is

$(selector).countdown({until: liftoffTime, format: 'HMS'})

js code here

 function mysqlTimeStampToDate(timestamp) {
    //function parses mysql datetime string and returns javascript Date object
    //input has to be in this format: 2010-09-24 11:30:12
    var regex=/^([0-9]{2,4})-([0-1][0-9])-([0-3][0-9]) (?:([0-2][0-9]):([0-5][0-9]):([0-5][0-9]))?$/;
    var parts=timestamp.replace(regex,"$1 $2 $3 $4 $5 $6").split(' ');
    return new Date(parts[0],parts[1]-1,parts[2],parts[3],parts[4],parts[5]);
  }
$('#selector').countdown({until: mysqlTimeStampToDate(<? echo "2010-09-24 11:30:12" ?>), format: 'HMS'});

Answer 3

There is a Date.parse() method that might work.