0

TIMESTAMPDIFF

The link above have helped me how to calculate difference in two dates, however, I need to do the same on a particular column at run time via MySQL select query inside PHP.

For example:

Profile table:

  • Name
  • Gender
  • DOB (Date of Birth)

Running following query via MySQL console, gives exactly the needed result:

SELECT TIMESTAMPDIFF(YEAR, DOB, CURDATE()) AS Age FROM profile;

However, I need to complete the following query to get me the same result combined with other conditions on whole set of DOB values:

SELECT * FROM Profile WHERE Gender='$gn';

I checked Sub-query but that, wont work due to more than one return value.

Community
  • 1
  • 1
Kam
  • 13
  • 4
  • Is it `profile` the same table as `Profile`? Then you could just add the calculated column from your first query to the field list of your second query. – akrys Oct 30 '16 at 15:00
  • They are same. Could you write that query for me as you referred, It will be helpful. – Kam Oct 30 '16 at 15:19

1 Answers1

1

As you said, it's the same table, you could use this query:

SELECT *,TIMESTAMPDIFF(YEAR, DOB, CURDATE()) AS Age
FROM Profile 
WHERE Gender='$gn';

Then you'll find an additional field age in your result set.

akrys
  • 563
  • 1
  • 3
  • 9
  • This query works, however when using Age as part of WHERE clause, MySQL reports Age as Unknown column. #1054 - Unknown column 'Age' in 'where clause'. **I'm trying to achieve the result based on this query...SELECT *,TIMESTAMPDIFF(YEAR, DOB, CURDATE()) AS Age FROM Profile where gender="Male" AND (Age BETWEEN 20 AND 30);** – Kam Oct 31 '16 at 14:31
  • This happens, because this field is named after it was select is done. The time MySQL filters the where-statments, this column haven't been created yet. In this case you'll need to add a having clause instead. This runs after the selection is done. Try this: `SELECT *,TIMESTAMPDIFF(YEAR, DOB, CURDATE()) AS Age FROM Profile where gender="Male" having (Age BETWEEN 20 AND 30);` – akrys Oct 31 '16 at 14:40
  • Works as required. – Kam Oct 31 '16 at 17:59