How to convert birth date to current year's birthday from a field timestamp?

id birth
-----------------------------
1 1079280000 (2004-03-15) 
2 987264000  (2001-04-15)
3 1042560000 (2003-01-15)

How to convert to birth timestamp to 1300118400 (2011-03-15) for sql;

<?php
  $now = strtotime('2011-03-04')开发者_如何学运维;// today's timestamp
  $sql = "SELECT * FROM member 
  WHERE birth(1079280000  convert to 1300118400) > $now";
?>

Results
id   birth 
---------------
1 1079280000 (2004-03-15) 
2 987264000  (2001-04-15)    

I need the birth convert to current year birthday's timestamp.

---

A no-subselect solution:

SELECT
  id,
  UNIX_TIMESTAMP(
    FROM_UNIXTIME(birth) +
    INTERVAL (YEAR(CURDATE()) - YEAR(FROM_UNIXTIME(birth))) YEAR
  ) AS birthday
FROM member
WHERE NOW() < FROM_UNIXTIME(birth) +
              INTERVAL (YEAR(CURDATE()) - YEAR(FROM_UNIXTIME(birth))) YEAR

Not sure how MySQL likes (ab)using derived tables, but the above query would possibly be more readable, if rewritten like this:

/* Stage 3. Filtering out past birthdays and
   converting datetime dates to unix timestamps. */
SELECT
  id,
  UNIX_TIMESTAMP(birthday_date) AS birthday
FROM (
  /* Stage 2. Getting this year birthdays */
  SELECT
    id,
    birth_date + INTERVAL (YEAR(CURDATE()) - YEAR(birth_date)) AS YEAR birthday_date
  FROM (
    /* Stage 1. Converting unix timestamps to datetime values. */
    SELECT
      id,
      FROM_UNIXTIME(birth) AS birth_date
    FROM member
  ) m
) m
WHERE NOW() < birthday_date

---

this already explained in below post

How to convert date to timestamp in PHP?

---

SQL has a DATEPART function (http://msdn.microsoft.com/en-us/library/ms174420.aspx) which you could use to get the day and month of the date of birth and use them in your comparisons having got today's day and month in php. There might be a better way of doing it but that way should work at least. :)

This does of course assume that your dates are stored as dates in the database rather than integers. :)

---

If I understand and if it is mysql:

SELECT day(FROM_UNIXTIME(birth)) as birthday
FROM member 
WHERE birth > $now