Kush Posted May 7, 2011 Share Posted May 7, 2011 Hi, I'm fairly new to all of this and I'm not quite sure what to do. I'm trying to create a field in my database that's 4 hours and 10 minutes into the future from the current time. What data type do I set my field as in the mysql database? How would I populate that mysql field via php.. would I use something like NOW(), or use the php date() function? Also, how would I be able to tell if my field has "expired" (the 4 hours and 10 minutes has elapsed)? Quote Link to comment https://forums.phpfreaks.com/topic/235776-time-limit/ Share on other sites More sharing options...
jcbones Posted May 7, 2011 Share Posted May 7, 2011 You would set the field as a `timestamp` and populate it with ADDTIME(NOW(),'04:10:00'). INSERT INTO table(timestamp) VALUES (ADDTIME(NOW(),'04:10:00')); And retrieve by: SELECT * FROM table WHERE timestamp > NOW(); Quote Link to comment https://forums.phpfreaks.com/topic/235776-time-limit/#findComment-1211951 Share on other sites More sharing options...
Kush Posted May 7, 2011 Author Share Posted May 7, 2011 Well, I plan on updating the field in certain events and with the "timestamp" field it has an on update which it does the current timestamp again. I'm creating my tables in php and just executing the query from php. How would I populate the timestamp field from a mysql query.. the same as above? For retrieving how would I pull all of the rows where the timestamp has expired? I'm trying to make a count-down timer for something (the timer lasts 4 hours and 10 minutes. I guess I was hoping for more php code instead of mysql? I'm still confused. Quote Link to comment https://forums.phpfreaks.com/topic/235776-time-limit/#findComment-1211954 Share on other sites More sharing options...
Kush Posted May 7, 2011 Author Share Posted May 7, 2011 Anyone? Quote Link to comment https://forums.phpfreaks.com/topic/235776-time-limit/#findComment-1212020 Share on other sites More sharing options...
Kush Posted May 7, 2011 Author Share Posted May 7, 2011 Can people read this? Quote Link to comment https://forums.phpfreaks.com/topic/235776-time-limit/#findComment-1212088 Share on other sites More sharing options...
spiderwell Posted May 7, 2011 Share Posted May 7, 2011 jcbones answered your question perfectly, if you want to get ones that have expired, use less than instead of more than... did you even try out his example? Quote Link to comment https://forums.phpfreaks.com/topic/235776-time-limit/#findComment-1212090 Share on other sites More sharing options...
Kush Posted May 7, 2011 Author Share Posted May 7, 2011 jcbones answered your question perfectly, if you want to get ones that have expired, use less than instead of more than... did you even try out his example? I was a bit confused by his retrieve example because it's saying "timestamp > NOW()". I don't want to select the timestamp that's greater than now. I'm basically wanting to create a timer and I'll be checking my database every minute to check and see if any timers have expired. By using "timestamp > NOW()" isn't that going to select everything then since it would all be set 4 hours and 10 minutes into the future? Quote Link to comment https://forums.phpfreaks.com/topic/235776-time-limit/#findComment-1212093 Share on other sites More sharing options...
spiderwell Posted May 8, 2011 Share Posted May 8, 2011 jcbones answered your question perfectly, if you want to get ones that have expired, use less than instead of more than... did you even try out his example? I was a bit confused by his retrieve example because it's saying "timestamp > NOW()". I don't want to select the timestamp that's greater than now. I'm basically wanting to create a timer and I'll be checking my database every minute to check and see if any timers have expired. By using "timestamp > NOW()" isn't that going to select everything then since it would all be set 4 hours and 10 minutes into the future? the example given selects anything with the column called timestamp set in the future from the time the sql select is executed. you want the opposite, which is why i said use less than <, not more than >. there aren't any 'timers' set up really, just a single insert that at the time is 4hours 10 minutes ahead. as the clock ticks (father time is our timer if anything is) that inserted timestamp becomes less and less in the future until, hey presto, 4hours 10 minutes later it becomes present, then 1 second later it becomes the past, and at that point the 'timer' has expired. dont think to hard about it, just do it Quote Link to comment https://forums.phpfreaks.com/topic/235776-time-limit/#findComment-1212101 Share on other sites More sharing options...
Kush Posted May 8, 2011 Author Share Posted May 8, 2011 I tried making the field as TIMESTAMP, but somehow it adds an "ON UPDATE" and uses the current timestamp for that.. is there a way to disable that? Quote Link to comment https://forums.phpfreaks.com/topic/235776-time-limit/#findComment-1212176 Share on other sites More sharing options...
spiderwell Posted May 8, 2011 Share Posted May 8, 2011 i think you are being confused by a column that is called timestamp and one which its datatype is set as time stamp. which is not surprising since the original response has a column called timestamp. a datatype as a timestamp is somewhat different to a datatype that is date. timestamp data will auto update unless you tell it not to, whereas a date data wont. have a look here for more info on timestamp i would suggest that you use a date as the datatype and not timestamp as the datatype. perhaps call the column expires or something else to avoid confusion? Quote Link to comment https://forums.phpfreaks.com/topic/235776-time-limit/#findComment-1212262 Share on other sites More sharing options...
jcbones Posted May 8, 2011 Share Posted May 8, 2011 You should use a datetime data type, and not just a date. A date data type will not have time associated with it. Quote Link to comment https://forums.phpfreaks.com/topic/235776-time-limit/#findComment-1212494 Share on other sites More sharing options...
spiderwell Posted May 8, 2011 Share Posted May 8, 2011 listen to the man above, lol. I was nearly there. Quote Link to comment https://forums.phpfreaks.com/topic/235776-time-limit/#findComment-1212496 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.