Jump to content
Larry Ullman's Book Forums

Using Sql Dates In Js Object Date()?


Recommended Posts

I'm building something cool with JQuery and PHP. I display info and a countdown for the next upcoming football match using some plugin developed in JQuery.

 

You can see it here: http://dev.juvenorge.com/

 

My timestamps are saved as yyyy-mm-dd hh:mm in the database. I struggle a little bit with transforming that format into a date string the Date object in JavaScript will accept.

 

Any recommendations for me?

 

Btw: I meant to bring this up to:

 

The plugin uses a format called DHMS I don't think I can change. I basically builds the whole markup up the timer, which I just hates. I would've liked the markup to be placed inside the Html markup by PHP, then retrieved and altered by the plugin. Any ideas where to start?

Link to comment
Share on other sites

That countdown timer is really cool!

 

I would think you could tackle your first issue by retrieving the individual parts of the date from the database, then using them to construct the Date object in JS. Use the MySQL date functions YEAR(), MONTH(), DAYOFMONTH(), HOUR() and MINUTE() on your date field.

 

Sorry, I have no idea on the second issue.

 

Go Portland Timbers FC!

  • Upvote 2
Link to comment
Share on other sites

Similar to what Paul said, I would use the DATE_FORMAT function in MySQL to format the date so that it can be easily broken up and thrown into the Date constructor in JS. For example:

 

DATE_FORMAT(date-field-in-DB, '%Y,%m,%d,%H,%i')

 

The above will get you a date in yyyy,mm,dd,hh,mm format. The one thing to note is that the JS Date object interprets months from 0-11, whereas the above function gives you months in the range 1-12.

 

Next, you can use either PHP or JS to split the string on the commas to create the new Date object in JS. For example:

 

PHP

explode(',', $row['date']);

 

JS

var dateArr = ajax.responseText.split(',');

 

Either way, assuming you end up with the following dateArr variable in JS, you can then easily create the proper Date object, as shown below:

 

dateArr[0] = yyyy
dateArr[1] = mm
dateArr[2] = dd
dateArr[3] = hh
dateArr[4] = mm

 

var d = new Date(dateArr[0], parseInt(dateArr[1]) - 1, dateArr[2], dateArr[3], dateArr[4]);

 

Note that 1 is subtracted from the month to make it work properly for the Date constructor in JS. Please also note that the Date constructor can take dates in integer or string format, but since you need to subtract 1 from the month, you first need to convert the string value in the dateArr variable to an integer.

 

And as you know, once you have a Date object in JS, you can manipulate it however you want.

Sorry for spelling everything out so exactly, Antonio. I'm sure you've already figured out all of that on your own.

I was just bored at work and wanted to post something.

  • Upvote 2
Link to comment
Share on other sites

Sorry, I have no idea on the second issue.

 

Ever seen a referee write down a match as started 20:45 and 7 seconds? Guess that won't be a problem. :P

 

Jon: Very good post. I get that feeling too. You just want to go on explaining. Loved your use of date_format()... I did pretty much the same in PHP, but I started to hating the solution pretty fast. That worked seamlessly.

 

In the end, I prefer Paul's solution as I can scratch the splitting. I just used each of the function to create new columns for them:

 

SELECT DATE_FORMAT(games.kickoff_time, '%d:%m:%Y %H:%i') as kickoff,
			YEAR(games.kickoff_time) as year,
			MONTH(games.kickoff_time) as month,
			DAYOFMONTH(games.kickoff_time) as day,
			HOUR(games.kickoff_time) as hour,
			MINUTE(games.kickoff_time) as min

	   	 FROM ....

 

Thanks, guys. Maybe I'll create a new thread for the html generation part.

Link to comment
Share on other sites

Yeah, that's a good solution, too. In fact, a better solution. That way, you can place all the date formatting on the DB side and get exactly what you want.

 

As for the HTML markup creation, well, I can't view the library right now, so can't comment on that. I imagine it's pretty much just a matter of calling a method with a date and giving it a div to spit all the HTML out to though, right?

Link to comment
Share on other sites

 Share

×
×
  • Create New...