✔ Geburtstag(e) von SQL Tabelle mit DATE Format

Matthias Reitinger · 28. Dezember 2006

Hallo,

die Methode von Gumbo funktioniert allerdings über Jahresgrenzen hinweg nicht zuverlässig:

Code:

mysql> DESCRIBE `birthday`;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) |      | PRI | NULL    | auto_increment |
| bdate | date    | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT CURRENT_DATE, `bdate`,
    -> (TO_DAYS(CONCAT(YEAR(CURRENT_DATE), "-", DATE_FORMAT(`bdate`, "%m-%d"))) - TO_DAYS(CURRENT_DATE) + 366) % 366 AS `Tage_bis_zum_Jahrestag`
    -> FROM `birthday`;
+--------------+------------+------------------------+
| CURRENT_DATE | bdate      | Tage_bis_zum_Jahrestag |
+--------------+------------+------------------------+
| 2006-12-28   | 2000-12-31 |                      3 |
| 2006-12-28   | 2001-01-01 |                      5 |
+--------------+------------+------------------------+
2 rows in set (0.00 sec)

Thomas' Vorschlag ist auch noch nicht perfekt:

Code:

mysql> SELECT CURRENT_DATE,bdate,
    -> DATEDIFF(MAKEDATE(YEAR(CURRENT_DATE())+1, DAYOFYEAR(bdate)),CURRENT_DATE()) % DAYOFYEAR(LAST_DAY(CURRENT_DATE())) AS `diff`
    -> FROM birthday;
+--------------+------------+------+
| CURRENT_DATE | bdate      | diff |
+--------------+------------+------+
| 2006-12-28   | 2000-12-31 |    4 |
| 2006-12-28   | 2001-01-01 |    4 |
+--------------+------------+------+
2 rows in set (0.00 sec)

Grüße,
Matthias

maga147 · 28. Dezember 2006

@Mik3e: Muss mich da mal schlau lesen

@Matthias Reitinger:

Den "Bug" hab ich schon ausgemerzt (hoff ich

) ... Ich hab die 366 Tage auf die üblichen 365 Tage geändert und dann geht's

Gumbo · 28. Dezember 2006

Das hängt mit den Schaltjahren zusammen.

Code:

(TO_DAYS(CONCAT(YEAR(CURRENT_DATE), "-", DATE_FORMAT(`bdate`, "%m-%d"))) - TO_DAYS(CURRENT_DATE) + DATE_FORMAT(CONCAT(YEAR(CURRENT_DATE), "-12-31"), "%j") ) % DATE_FORMAT(CONCAT(YEAR(CURRENT_DATE), "-12-31"), "%j")

maga147 · 28. Dezember 2006

ähm, und was wird dadurch jetzt ersetzt

?

Matthias Reitinger · 28. Dezember 2006

Hallo,

maga147 hat gesagt.:
@Matthias Reitinger:

Den "Bug" hab ich schon ausgemerzt (hoff ich ) ... Ich hab die 366 Tage auf die üblichen 365 Tage geändert und dann geht's

Leider reicht das nicht:

Code:

mysql> SELECT '2004-01-01', `bdate`,
    -> (TO_DAYS(CONCAT(YEAR('2004-01-01'), "-", DATE_FORMAT(`bdate`, "%m-%d"))) - TO_DAYS('2004-01-01') + 365) % 365 AS `Tage_bis_zum_Jahrestag`
    -> FROM `birthday`;
+------------+------------+------------------------+
| 2004-01-01 | bdate      | Tage_bis_zum_Jahrestag |
+------------+------------+------------------------+
| 2004-01-01 | 2000-12-31 |                      0 |
| 2004-01-01 | 2001-01-01 |                      0 |
+------------+------------+------------------------+
2 rows in set (0.00 sec)

Gumbo hat gesagt.:
Das hängt mit den Schaltjahren zusammen.

Code:

(TO_DAYS(CONCAT(YEAR(CURRENT_DATE), "-", DATE_FORMAT(`date`, "%m-%d"))) - TO_DAYS(CURRENT_DATE) + DATE_FORMAT(CONCAT(YEAR(CURRENT_DATE), "-12-31")), "%j")) % DATE_FORMAT(CONCAT(YEAR(CURRENT_DATE), "-12-31")), "%j")

Da steckt auch noch irgendwo der Wurm drin:

Code:

mysql> SELECT CURRENT_DATE, `bdate`,
    -> (TO_DAYS(CONCAT(YEAR(CURRENT_DATE), "-", DATE_FORMAT(`bdate`, "%m-%d"))) -
    ->  TO_DAYS(CURRENT_DATE) + DATE_FORMAT(CONCAT(YEAR(CURRENT_DATE), "-", DATE_FORMAT(`bdate`, "%m-%d")), "%j")) %
    ->  DATE_FORMAT(CONCAT(YEAR(CURRENT_DATE), "-", DATE_FORMAT(`bdate`, "%m-%d")), "%j") AS `Tage_bis_zum_Jahrestag`
    -> FROM `birthday`;
+--------------+------------+------------------------+
| CURRENT_DATE | bdate      | Tage_bis_zum_Jahrestag |
+--------------+------------+------------------------+
| 2006-12-28   | 2000-12-31 |                      3 |
| 2006-12-28   | 2001-01-01 |                      0 |
| 2006-12-28   | 2000-02-29 |                     -2 |
+--------------+------------+------------------------+
3 rows in set (0.01 sec)

Hier mal mein Vorschlag:

SQL:

SELECT
	`id`,
	`bdate`,
	`bdate` + INTERVAL (YEAR(CURRENT_DATE) - YEAR(`bdate`) + IF(DATE_FORMAT(CURRENT_DATE, "%m%d") > DATE_FORMAT(`bdate`, "%m%d"), 1, 0)) YEAR AS `next_bday`	
FROM
	`birthday`
HAVING
	DATEDIFF(`next_bday`, CURRENT_DATE) <= 7;

Das Alter lässt sich dann ganz einfach aus dem Jahresanteilen von Geburtsdatum und nächstem Geburtstag berechnen.

Grüße,
Matthias

Thomas Darimont · 28. Dezember 2006

Hi,

so gings nun auch:

SQL:

drop table birthday;
create table birthday (id int, bdate date);

insert into birthday values (1 ,'1998-12-28');
insert into birthday values (2 ,'1993-08-29');
insert into birthday values (3 ,'1983-12-31');
insert into birthday values (4 ,'1986-08-30');
insert into birthday values (5 ,'1962-01-05');
insert into birthday values (6 ,'1983-02-23');
insert into birthday values (7 ,'1999-12-24');
insert into birthday values (8 ,'1995-12-24');
insert into birthday values (9 ,'2000-12-29');
insert into birthday values (10,'2001-01-01');
insert into birthday values (11,'2006-01-23');
insert into birthday values (12,'2000-12-31');

select * from birthday;

SELECT
id,
bdate,
YEAR(CURRENT_DATE()) -YEAR(bdate) age, 
datediff(
MAKEDATE(YEAR(CURRENT_DATE())+1,IF(DAYOFYEAR(bdate) > DAYOFYEAR(LAST_DAY(MAKEDATE(YEAR(CURRENT_DATE()), 365))),DAYOFYEAR(LAST_DAY(MAKEDATE(YEAR(CURRENT_DATE()), 365))),DAYOFYEAR(bdate)) ),
CURRENT_DATE()
) % DAYOFYEAR(LAST_DAY(MAKEDATE(YEAR(CURRENT_DATE()), 365))) AS 'days to birthday' 
FROM birthday;

SQL:

mysql> drop table birthday;
Query OK, 0 rows affected (0.05 sec)

mysql> create table birthday (id int, bdate date);
Query OK, 0 rows affected (0.09 sec)

mysql>
mysql> insert into birthday values (1 ,'1998-12-28');
Query OK, 1 row affected (0.03 sec)

mysql> insert into birthday values (2 ,'1993-08-29');
Query OK, 1 row affected (0.03 sec)

mysql> insert into birthday values (3 ,'1983-12-31');
Query OK, 1 row affected (0.03 sec)

mysql> insert into birthday values (4 ,'1986-08-30');
Query OK, 1 row affected (0.05 sec)

mysql> insert into birthday values (5 ,'1962-01-05');
Query OK, 1 row affected (0.05 sec)

mysql> insert into birthday values (6 ,'1983-02-23');
Query OK, 1 row affected (0.03 sec)

mysql> insert into birthday values (7 ,'1999-12-24');
Query OK, 1 row affected (0.03 sec)

mysql> insert into birthday values (8 ,'1995-12-24');
Query OK, 1 row affected (0.03 sec)

mysql> insert into birthday values (9 ,'2000-12-29');
Query OK, 1 row affected (0.03 sec)

mysql> insert into birthday values (10,'2001-01-01');
Query OK, 1 row affected (0.03 sec)

mysql> insert into birthday values (11,'2006-01-23');
Query OK, 1 row affected (0.03 sec)

mysql> insert into birthday values (12,'2000-12-31');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> select * from birthday;
+------+------------+
| id   | bdate      |
+------+------------+
|    1 | 1998-12-28 |
|    2 | 1993-08-29 |
|    3 | 1983-12-31 |
|    4 | 1986-08-30 |
|    5 | 1962-01-05 |
|    6 | 1983-02-23 |
|    7 | 1999-12-24 |
|    8 | 1995-12-24 |
|    9 | 2000-12-29 |
|   10 | 2001-01-01 |
|   11 | 2006-01-23 |
|   12 | 2000-12-31 |
+------+------------+
12 rows in set (0.00 sec)

mysql>
mysql> SELECT
    -> id,
    -> bdate,
    -> YEAR(CURRENT_DATE()) -YEAR(bdate) age,
    -> datediff(
    -> MAKEDATE(YEAR(CURRENT_DATE())+1,IF(DAYOFYEAR(bdate) > DAYOFYEAR(LAST_DAY(MAKEDATE(YEAR(CURRENT_DATE()), 365))),DAYOFYEAR(LAST_DAY(MAKEDATE(YEAR(CURRENT_DATE()), 365))),DAYOFYEAR(bdate)) ),
    -> CURRENT_DATE()
    -> ) % DAYOFYEAR(LAST_DAY(MAKEDATE(YEAR(CURRENT_DATE()), 365))) AS 'days to birthday'
    -> FROM birthday;
+------+------------+------+------------------+
| id   | bdate      | age  | days to birthday |
+------+------------+------+------------------+
|    1 | 1998-12-28 |    8 |                0 |
|    2 | 1993-08-29 |   13 |              244 |
|    3 | 1983-12-31 |   23 |                3 |
|    4 | 1986-08-30 |   20 |              245 |
|    5 | 1962-01-05 |   44 |                8 |
|    6 | 1983-02-23 |   23 |               57 |
|    7 | 1999-12-24 |    7 |              361 |
|    8 | 1995-12-24 |   11 |              361 |
|    9 | 2000-12-29 |    6 |                2 |
|   10 | 2001-01-01 |    5 |                4 |
|   11 | 2006-01-23 |    0 |               26 |
|   12 | 2000-12-31 |    6 |                3 |
+------+------------+------+------------------+
12 rows in set (0.00 sec)

mysql> SELECT
    -> id,
    -> bdate,
    -> YEAR(CURRENT_DATE()) -YEAR(bdate) age,
    -> datediff(
    -> MAKEDATE(YEAR(CURRENT_DATE())+1,IF(DAYOFYEAR(bdate) > DAYOFYEAR(LAST_DAY(MAKEDATE(YEAR(CURRENT_DATE()), 365))),DAYOFYEAR(LAST_DAY(MAKEDATE(YEAR(CURRENT_DATE()), 365))),DAYOFYEAR(bdate)) ),
    -> CURRENT_DATE()
    -> ) % DAYOFYEAR(LAST_DAY(MAKEDATE(YEAR(CURRENT_DATE()), 365))) days_to_birthday
    -> FROM birthday
    -> HAVING days_to_birthday < 7;
+------+------------+------+------------------+
| id   | bdate      | age  | days_to_birthday |
+------+------------+------+------------------+
|    1 | 1998-12-28 |    8 |                0 |
|    3 | 1983-12-31 |   23 |                3 |
|    9 | 2000-12-29 |    6 |                2 |
|   10 | 2001-01-01 |    5 |                4 |
|   12 | 2000-12-31 |    6 |                3 |
+------+------------+------+------------------+
5 rows in set (0.00 sec)

Gruß Tom

Gumbo · 28. Dezember 2006

Die Frage ist, ob es richtig ist, den 29. Februar eines Schaltjahrs auf den 28. Februar zu verlegen.

Übrigens hast du zwar meinen Algorithmus richtig zitiert jedoch wohl falsch kopiert, Matthias.

Matthias Reitinger · 28. Dezember 2006

Hallo,

Gumbo hat gesagt.:
Die Frage ist, ob es richtig ist, den 29. Februar eines Schaltjahrs auf den 28. Februar zu verlegen.

Da dies der Tag ist, an dem ein am 29. Februar Geborener normalerweise seinen Geburtstag in einem Nicht-Schaltjahr feiert, halte ich das durchaus für berechtigt.

Grüße,
Matthias

maga147 · 29. Dezember 2006

so, jetzt meld ich mich auch mal wieder, nach dem ich hier schon die Großen angelockt habe

Ich hab jetzt mal folgende SQL von Thomas Darimont verwendet:

Code:

#
mysql> SELECT
#
    -> id,
#
    -> bdate,
#
    -> YEAR(CURRENT_DATE()) -YEAR(bdate) age,
#
    -> datediff(
#
    -> MAKEDATE(YEAR(CURRENT_DATE())+1,IF(DAYOFYEAR(bdate) > DAYOFYEAR(LAST_DAY(MAKEDATE(YEAR(CURRENT_DATE()), 365))),DAYOFYEAR(LAST_DAY(MAKEDATE(YEAR(CURRENT_DATE()), 365))),DAYOFYEAR(bdate)) ),
#
    -> CURRENT_DATE()
#
    -> ) % DAYOFYEAR(LAST_DAY(MAKEDATE(YEAR(CURRENT_DATE()), 365))) days_to_birthday
#
    -> FROM birthday
#
    -> HAVING days_to_birthday < 7;

Aber da stellt sich jetzt mir bloß die Frage... Wie wird es denn dann behandelt, wenn jemand am 29. Februar geboren ist? Ansonsten funktioniert das ganze soweit klasse

Danke!

grafzahl84 · 2. März 2007

Hi Leute,

ich habe eure SQL-Abfragen einmal in einem meiner Projekte angewendet, musste jedoch feststellen das die Variante von Thomas einen kleinen Fehler zu haben scheint.

Ist jemand bei mir in einem ggeraden Jahr geboren, also zb 1984, dann wird das Feld in days_to_birthday plötzlich um den Wert 1 erhöht, sehr merkwürdig das ganze.

Das wäre ja kein Problem das ganze nachher mit PHP zu berichtigen, doch leider benötige ich die SORT BY Funktion von MySQL, und das würde dann natürlich nicht mehr passen. Habt ihr da evtl. nen Lösungsvorschlag?

✔ Geburtstag(e) von SQL Tabelle mit DATE Format

Matthias Reitinger

ɐɯıǝɹ

maga147

Erfahrenes Mitglied

Gumbo

Erfahrenes Mitglied

maga147

Erfahrenes Mitglied

Matthias Reitinger

ɐɯıǝɹ

Thomas Darimont

Erfahrenes Mitglied

Gumbo

Erfahrenes Mitglied

Matthias Reitinger

ɐɯıǝɹ

maga147

Erfahrenes Mitglied

grafzahl84

Grünschnabel

Neue Beiträge