sql - 如何将出生日期从字段时间戳转换为当年的生日？

Question

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

如何转换为 birth时间戳到 1300118400 (2011-03-15) 用于 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)    

我需要 birth转换为当年生日的 timestamp .

Answer 1

无子选择解决方案:

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

不确定 MySQL 如何喜欢 (ab) 使用派生表，但如果像这样重写，上面的查询可能会更具可读性:

/* 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