waynobweno Posted September 24, 2008 Share Posted September 24, 2008 Hey, I am a PHP newbie coming from the crappy world of Coldfusion, so I need some assistance... I have a db with a column that has upwards of 20,000 records in it. Each record has a date stamp that looks like this: dd/mm/yyyy... What I am wanting is to do 1 query and get the differnce of each records date in the db compared to todays current date and then average all of those differences together into one avg() number. So, for instance, for record 1 the date may be 08/08/2008 and record 2 the date may be 09/09/2005. I need to get the date difference of each date compared to todays date and then get the average of these two dates added together. For instance: 08/08/2008 - today = 47 04/08/2008 - today = 169 Avg is 108 How do I do this in a single query? I just want to come up with the average number in the end... Please help if you can. Thanks a million! Link to comment https://forums.phpfreaks.com/topic/125696-get-average-days-from-query/ Share on other sites More sharing options...
Barand Posted September 24, 2008 Share Posted September 24, 2008 There are many powerful datetime functions in MySQL but they require correctly formatted date type fields in ISO date format (YYYY-MM-DD or YYYYMMDD). Your format is totally useless as a database date format. However, look at these MySQL functions STR_TO_DATE convert your date format to usable ISO format CURDATE current date DATEDIFF days between two dates AVG calc average Link to comment https://forums.phpfreaks.com/topic/125696-get-average-days-from-query/#findComment-649950 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.