OT - MySQL Question(s)

[jemagee]

Ok - so I have a MySQL question after I spent an hour yesterday dealing with 'start times' for games.

I thought it would be easy to compare real times and it turns out it isn't. 

In my 'games' table I have a game date field and a game time field - what's the best way to take these two fields, combine them, and create the datetimestamp from them?  Is there a simple way using a sql query?

[westkoast]

You want to combine both of them into another field?

If you are trying to display a time stamp on a web page it's pretty basic, at least in php, you place the results of the query into a variable and echo it out.

[jemagee]

I want to combine them into another field.

The way I have it set up now i have to get the time convert it to a time stamp and then see if the current time (+20 minutes) stamp is more or less than the time stamp on the start time of the first game - thus determining if picks can be made or not.  I sort of worked it backwards cause I didn't think it through.  I'd just like the start time to be a datetimestamp so i can compare more easily - plus i'm concerned that by only comparing times (converted into time stamp) someone can get around my function in the 11:40 to midnight time window.

I'm not even sure it's a problem until i test it but if i change the table set up so the start time is already the date/time stamp it becomes an easier functionality (i think)

[Derek Bodner]

What's the reasoning for having it separated to begin with?  From a data integrity standpoint, that's not really the way to go.

[WayOutWest]

Are you using a programming laguage or strictly SQL?

If it's just SQL just convert the date and time to seconds or milliseconds and compare the two.

[jemagee]

Like I said in the post you replied to - I didn't think it through (properly) when I was planning it.  It's the first site I've ever built and have limited experience working with these things from scratch, I'm used to working with databases already built, or filemaker pro which is an entirely different kind of animal until its most recent versions.

[jemagee]

Are you using a programming laguage or strictly SQL?

If it's just SQL just convert the date and time to seconds or milliseconds and compare the two.

I'm using PHP with MySQL

The start date and time are currently separate fields...i can combine them using the PHP i just thought having them combined and 'time stamped' already in the database would make it run more efficientliy as I'd just have to get the field and compare it to the server timestamp without having to manipulate either.

[WayOutWest]

Are you using a programming laguage or strictly SQL?

If it's just SQL just convert the date and time to seconds or milliseconds and compare the two.

I'm using PHP with MySQL

The start date and time are currently separate fields...i can combine them using the PHP i just thought having them combined and 'time stamped' already in the database would make it run more efficientliy as I'd just have to get the field and compare it to the server timestamp without having to manipulate either.

Convert both fields to seconds/milliseconds
Add the two
Reformat the result back into a date/time format.

[jemagee]

Convert both fields to seconds/milliseconds
Add the two
Reformat the result back into a date/time format.

I'll have to look into that...thanks for your help.  i just worried that by using 'only the time' as my comparing factor that the midnight issue might screw things up...btw - if you're curios

Here's where it's at now - the submit won't work - but i'm hoping to get something functional sooner than later

http://student49.tangostudents.com/Interface%20Design/picks.php

[jemagee]

BTW - are people still terrified of cookies on web sites?

[Lurker]

BTW - are people still terrified of cookies on web sites?

Only the ones with nuts in them.

 

[jemagee]

WOW

Thanks for your suggestions, i used a modification that your suggestion spurred in my php code - got the date as one variable - time as another - merged them and then strtotime

It works and my worries on the close to midnight thing are taken care of...course now i realized i have to do this check twice - when they try to make picks and when they try to submit - but at least now i have something i have confidence in.

[WayOutWest]

WOW

Thanks for your suggestions, i used a modification that your suggestion spurred in my php code - got the date as one variable - time as another - merged them and then strtotime

It works and my worries on the close to midnight thing are taken care of...course now i realized i have to do this check twice - when they try to make picks and when they try to submit - but at least now i have something i have confidence in.

No problem.  Your crumbling down the stretch of our week 2 matchup is thanks enough. 

[jemagee]

No problem.  Your crumbling down the stretch of our week 2 matchup is thanks enough.

My team is not good I did warn you all i wasn't very good at this - seriously - i should just concede turnovers every week - every season - i tend to pick the turnover prone players - and i expect not playing mobley and kaman last night (instead of chander and durant) will cost me even though i'm now up 10-1 - weekends always kill me

[Derek Bodner]

It works and my worries on the close to midnight thing are taken care of...course now i realized i have to do this check twice - when they try to make picks and when they try to submit - but at least now i have something i have confidence in.

Create a php function, pass the date and time, return the stamp.

Or, better yet, mysql function/stored procedure.