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

Question

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.

Andriy M · Accepted Answer · 2011-09-30T05:09:05.173

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

score 1 · Answer 2 · edited May 23 '17 at 11:47

1

this already explained in below post

How to convert date to timestamp in PHP?

edited May 23 '17 at 11:47

Community

1
1

answered Mar 04 '11 at 09:41

sush

5,897
5
30
39

@LNXA - what sushanth is saying is that you do the conversion in PHP before submitting it to sql. – slugster Mar 04 '11 at 09:59
@slugster,@sushanth: as I understand it what he wants to convert is the date of births that are in the database into the birthday in the current year which he can't do before running the sql. It probably could be done afterwards (select everything and use a loop to do the where in code) of course. – Chris Mar 04 '11 at 10:21

score 0 · Answer 3 · answered Mar 04 '11 at 10:18

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. :)

score 0 · Answer 4 · answered Mar 04 '11 at 11:34

0

If I understand and if it is mysql:

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

answered Mar 04 '11 at 11:34

Clodoaldo Neto

118,695
26
233
260

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

4 Answers4

Linked