Determining an AVERAGE date

[NiallFH]

Could someone please advise the best way to determine the average of a DATE field in a SELECT query?

 

 

[Barand]

Did some experimenting and, amazingly, this seems to work

SELECT * FROM testtime;
+----+------------+
| id | start      |
+----+------------+
|  1 | 2014-05-22 |
|  2 | 2014-07-24 |
+----+------------+

SELECT CAST(ROUND(AVG(start),0) as DATE) as average FROM testtime;
+------------+
| average    |
+------------+
| 2014-06-23 |
+------------+

[NiallFH]

Thanks Barand,

 

Trying this just now but it only seems to return a year? No month or date showing.  

$get_average_dob = mysql_query("
SELECT 
	CAST(ROUND(AVG(P.PlayerDOB),0) as DATE) as dob
FROM 
	tplss_players P, tplss_appearances A, tplss_matches M
WHERE 
	P.PlayerID = A.AppearancePlayerID AND
	A.AppearanceMatchID = M.MatchID AND
	M.MatchDateTime = '$matchdate' AND
	P.PlayerDOB IS NOT NULL
",$connection) or die(mysql_error());

[Barand]

Well, the definition of test data is "That data for which the program works". Perhaps I was just lucky.

 

Can you attach a dump of your player table

[NiallFH]

Here it is:

-- phpMyAdmin SQL Dump
-- version 2.11.11.3
-- http://www.phpmyadmin.net
--
-- Host: ###
-- Generation Time: May 22, 2014 at 03:04 PM
-- Server version: 5.0.96
-- PHP Version: 5.1.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `###`
--

-- --------------------------------------------------------

--
-- Table structure for table `tplss_players`
--

CREATE TABLE `tplss_players` (
  `PlayerID` int(10) unsigned NOT NULL auto_increment,
  `PlayerFirstName` varchar(32) character set utf8 NOT NULL,
  `PlayerLastName` varchar(64) character set utf8 NOT NULL,
  `PlayerFriendlyName` varchar(200) collate latin1_general_ci NOT NULL,
  `PlayerNickName` varchar(128) collate latin1_general_ci NOT NULL default '',
  `PlayerSeasonID` int(11) unsigned NOT NULL default '0',
  `PlayerPositionID` tinyint(1) unsigned NOT NULL default '0',
  `PlayerNumber` tinyint(3) unsigned NOT NULL default '0',
  `PlayerDescription` text collate latin1_general_ci NOT NULL,
  `PlayerProfile` text collate latin1_general_ci NOT NULL,
  `PlayerPublish` tinyint(1) unsigned NOT NULL default '1',
  `PlayerShowStats` tinyint(1) NOT NULL default '1',
  `PlayerAllData` tinyint(1) NOT NULL default '1',
  `PlayerTask` varchar(128) collate latin1_general_ci NOT NULL default '',
  `PlayerFullName` varchar(128) collate latin1_general_ci NOT NULL,
  `PlayerHeight` varchar(128) collate latin1_general_ci NOT NULL default '',
  `PlayerDead` tinyint(1) NOT NULL default '0',
  `PlayerDOB` date default NULL,
  `PlayerDied` date default NULL,
  `PlayerBirthplace` varchar(220) collate latin1_general_ci NOT NULL,
  `PlayerDeathplace` varchar(220) collate latin1_general_ci NOT NULL,
  `PlayerPos` varchar(128) collate latin1_general_ci NOT NULL default '',
  `PlayerYouthID` tinyint(3) NOT NULL default '9',
  `PlayerSigned` date default NULL,
  `PlayerFee` int(13) NOT NULL default '0',
  `PlayerNationID` tinyint(3) NOT NULL default '0',
  `PlayerCapped` tinyint(1) default NULL,
  `PlayerCaps` tinyint(3) default NULL,
  `PlayerCapsGoals` tinyint(11) default NULL,
  `PlayerCapsNotes` text collate latin1_general_ci,
  `PlayerIntDebut` date default NULL,
  `PlayerIntOpponent` tinyint(11) default NULL,
  `PlayerHomegrown` tinyint(1) NOT NULL default '0',
  `PlayerExpiry` date default NULL,
  PRIMARY KEY  (`PlayerID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1493 ;

[NiallFH]

On further reflection, the year that appeared was a different print test.  The code I posted actually returns nothing at all.  No error though either?

[NiallFH]

I had tried this:

$get_average_dob = mysql_query("
SELECT 
	FROM_UNIXTIME(avg(unix_timestamp(P.PlayerDOB))) as dob
FROM 
	tplss_players P, tplss_appearances A, tplss_matches M
WHERE 
	P.PlayerID = A.AppearancePlayerID AND
	A.AppearanceMatchID = M.MatchID AND
	M.MatchDateTime = '$matchdate'
",$connection) or die(mysql_error());

However, the result it presents seems wildly inaccurate.  To the point where a bunch of players who were born between 1958 and 1963 returns an average DOB of some time in 1969??

[Barand]

Yes, it was luck. I add a load more test dates and it failed.

 

try

SELECT DATE(FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(playerDOB)))) as dob

[NiallFH]

Am trying this now.

 

Okay, something strange is happening.  Have tested this against a variety of matches and combinations.

 

Every single test returns "1969-12-31" as the average.

[NiallFH]

Ha! Okay, it seems to work fine when the players involved are born AFTER 1969?

 

But any time there are players involved who were born before that date, the result seems to default to 1969-12-31.

[Barand]

Unix timestamps number from 1970-01-01. Lets try days instead of seconds

SELECT FROM_DAYS(AVG(TO_DAYS(playerDOB))) as dob

[NiallFH]

Yes! That seems to have done it. Need to double check the accuracy, but I think it has.  Thank you once again Barand!