taz321 Posted March 25, 2008 Share Posted March 25, 2008 Hi I was wondering if anyone had an answer to my question. I have 4 fields in my table called - dateactioned timeactioned datecompleted timecompleted The format of the date is - e.g Thursday, 12, March 2008 AND the time is - 22:10 I require a calculation which works out the difference between the two sets of dates and time. Thank you in advance for any help Link to comment https://forums.phpfreaks.com/topic/97820-calculating-date-and-time/ Share on other sites More sharing options...
BlueSkyIS Posted March 25, 2008 Share Posted March 25, 2008 i suggest that you use 1 field for each date/time instead of 2. assuming you're using MySQL, i would use a single DATETIME field for actioned and one for completed. this will make your calculations simpler as you only have to consider 2 fields instead of 4. Link to comment https://forums.phpfreaks.com/topic/97820-calculating-date-and-time/#findComment-500475 Share on other sites More sharing options...
discomatt Posted March 25, 2008 Share Posted March 25, 2008 Also, mysql has a broad range of date functions available. Check them out here http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html Link to comment https://forums.phpfreaks.com/topic/97820-calculating-date-and-time/#findComment-500481 Share on other sites More sharing options...
taz321 Posted March 25, 2008 Author Share Posted March 25, 2008 Okay thanks, im going to go through my system now and place the date and time in one field, once iv done that i will come back lol Cheers Link to comment https://forums.phpfreaks.com/topic/97820-calculating-date-and-time/#findComment-500483 Share on other sites More sharing options...
obsidian Posted March 25, 2008 Share Posted March 25, 2008 Okay thanks, im going to go through my system now and place the date and time in one field, once iv done that i will come back lol Cheers Make note of the FORMAT of the DATETIME field. It accepts YYYY-MM-DD HH:MM:SS as the value, so you will need to convert and combine you existing data in order to get it to fit. This is definitely worth the effort. Link to comment https://forums.phpfreaks.com/topic/97820-calculating-date-and-time/#findComment-500484 Share on other sites More sharing options...
taz321 Posted March 25, 2008 Author Share Posted March 25, 2008 I have changed my system so that the DATE-TIME is in this format - $today = date("F j, Y, g:i a"); // March 10, 2001, 5:16 pm This is an okay format to use for what i need ? Thanks Link to comment https://forums.phpfreaks.com/topic/97820-calculating-date-and-time/#findComment-500578 Share on other sites More sharing options...
taz321 Posted March 25, 2008 Author Share Posted March 25, 2008 As i believe i have the correct Date/Time format, would anyone have an idea on how i can find the difference in time with the following fields - Date/time submitted AND Date/time completed Many Thanks in advance. Link to comment https://forums.phpfreaks.com/topic/97820-calculating-date-and-time/#findComment-500599 Share on other sites More sharing options...
discomatt Posted March 25, 2008 Share Posted March 25, 2008 No, mysql expects data inserted into a datetime field to be in the following format: YYYY-MM-DD HH:MM:SS Where HH is 24-hour format. To find the difference between 2 datetime fields, you want to use TIMEDIFF and DATEDIFF (assuming you only want days, hours, minutes, seconds, also assuming >= MySQL 4.11) SELECT CONCAT( DATEDIFF(`completed`, `submitted`), " days(s) ", HOUR( @a := TIMEDIFF(`completed`, `submitted`) ), " hour(s) ", MINUTE(@a), " minute(s) ", SECOND(@a), " second(s) " ) as `difference` FROM `table` If you want to go into months and years, it gets a little more complex. Link to comment https://forums.phpfreaks.com/topic/97820-calculating-date-and-time/#findComment-500630 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.