falsches Alter/Dauer wird errechnet

E

ezi116

Neuer Benutzer
Beiträge
1
Hallo,

ich bin noch Anfänger mit mysql und php und habe ein Problem und hoffe, dass mir geholfen werden kann (in der Suchfunktion konnte ich leider nix finden):

ich betreibe eine Vereinsseite meines Kegelclubs. Auf ner MySQL-Datenbank sind die ganzen Mitglieder aufgeführt. Bei der Berechnung der Dauer der Vereinszugehörigkeit habe ich bei dem ältesten Mitglied ein Problem. Er trat am 18.04.1964 in den Verein ein, ist also 60 Jahre und knapp 2 Monate dabei.

Die Tabelle mitgliedsdauer besteht aus id, member_id, eintritt, austritt, eintritt1, austritt1,

eintritt und austritt = Datum in amerikanischer Schreibweise

eintritt1 und austritt1 = Datum in deutscher Schreibweise


Hier meine Abfrage:

Code: 
$sqlBefehl = 'SELECT
    id, nickname, nachname, geburtstag, eintritt1, max( austritt1 ) AS austritt1,
SUM(TIMESTAMPDIFF(YEAR, eintritt, IFNULL(austritt, CURDATE()))) AS dauer_jahre,
SUM(TIMESTAMPDIFF(DAY, eintritt, IFNULL(austritt, CURDATE()))) AS dauer 
FROM mitgliedsdauer, member
where id=member_id
GROUP BY member_id
ORDER BY dauer ASC';



Bei meiner Abfrage, welche ich als Jahre und als Tage ausgebe erscheint aber, dass er „nur“ 59 Jahre dabei ist ☹

Die Tage stimmen.

Was mache ich da falsch?
mfg
ezi116
 
Werbung:
ezi116 schrieb:
Was mache ich da falsch?
Zum Vergrößern anklicken....
Du vertraust darauf, daß MySQL inhaltlich/syntaktische Fehler im SQL erkennt und als Fehler auch ausweist. Tut es nicht. Dein SQL ist FALSCH, MySQL liefert statt einer Fehlermeldung lieber ein Zufallsergebniss. Schrott halt.

Merke: in Abfragen mit Aggregationen müssen alle Spalten im Resultset entweder aggregiert oder gruppiert sein. Dies ist nicht der Fall.
 
ezi116 schrieb:
eintritt und austritt = Datum in amerikanischer Schreibweise

eintritt1 und austritt1 = Datum in deutscher Schreibweise
Zum Vergrößern anklicken....
Speicherst Du die Datumswerte als VARCHAR? Das ist ein ganz großer Fehler. Die Spalten sollten als DATE definiert sein. Ein bestimmtes Format wird dann bei der Anzeige der Werte angewendet.

Wenn ich das richtig in Erinnerung habe, dann liefert timestampdiff ausschließlich ganzzahlige Werte. Z.B. liefert TIMESTAMPDIFF(YEAR, '2023-01-01', '2024-12-31') als Ergebnis 1, obwohl es eigentlich (fast) 2 Jahre sind.

Vermutlich musst Du die Dauer in Tagen (oder Sekunden) berechnen und diese dann wieder in Jahre/Monate/Tage umrechnen.
 
Zuletzt bearbeitet:
Du brauchst gar kein Group by, hoffentlich nicht jedenfalls.
Sum() dient anders als bei Excel dazu, Werte verschiedener Datensätze zu summieren.
 
castorp schrieb:
Ich glaube(!) die Aggregation soll den Fall abdecken, bei denen Personen mehrfach ein- und wieder ausgetreten sind.
Zum Vergrößern anklicken....
Ja, kann sein. Das wäre aber nicht mein Verständnis von Dauer einer Mitgliedschaft. Dann eher max als sum... Aber ich kenne den Hintergrund nicht.

Trotzdem, mit der Tabelle Mitgliedsdauer ist eine Aggregatabfrage tatsächlich legitim.
Ich nehme meinen Beitrag zurück.
 
Vor allem wird mal das Datum "in deutscher Schreibweise" und mal das Datum "in amerikanischer Schreibweise" als Berechnungsgrundlage verwendet, das ist ja auch mal Blödsinn. Bitte unbedingt eine Datumsspalte mit passendem Datentyp verwenden. Zur Not kann man für die Ausgabe eine Zeichenkette abspeichern, wie sie einem gefällt, auch wenn das eigentlich Quatsch ist.

Der Join ist nicht explizit (FROM mitgliedsdauer, member where id=member_id ist effektiv ein INNER JOIN und sollte auch so bezeichnet werden) und Tabellen-Aliase oder vollqualifizierte Spaltennamen wären auch nicht verkehrt.

Für die Berechnung der Dauer wäre Tage das einfachste. Wie schon richtig von @castorp geschrieben liefert datediff(year,) oder das MySQL-Equivalent ggf. nur die Differenz im Jahresanteil. Er vergleicht nur den Jahreswert und liefert davon den Unterschied, was halt mal ein Jahr mehr oder ein Jahr weniger sein kann. Eigentlich müsste das aber dennoch 60 sein, auch wenn er erst am 31.12.1964 eingetreten ist. Da müsste man dann nochmal schauen wenn man einen richtigen Datumswert zum rechnen hat.
Code: 
DECLARE    @eintritt VARCHAR(20) = '31.12.1964'
DECLARE    @now DATE = getdate()
DECLARE    @datum DATE = convert(DATE,@eintritt,104)

SELECT    datediff(year,@datum,@now),    --=60 Jahre
        dateadd(year,60,@datum)        --2024-12-31
(MSSQL)
 
Da keine kompletten Antworten kommen, hier mal ein Beispiel:

Code: 
CREATE TABLE mitglieder (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32) NOT NULL,
    eintritt VARCHAR(32) NOT NULL,
    austritt VARCHAR(32)
);
 
INSERT INTO mitglieder (name, eintritt, austritt) VALUES
('Anna Müller', '2023-01-15', '2024-06-11'),
('Bernd Schmidt', '2022-06-09', NULL),
('Claudia Klein', '2021-03-25', '2023-05-30'),
('Dieter Groß', '2020-04-10', '2021-08-15'),
('Eva Lang', '2019-05-05', NULL);

und hier die Abfrage:


Code: 
WITH
yearsMember AS (
  SELECT y.*,
  date_format(eintritt, '%d.%m.%Y') eintritt_ge,
  date_format(austritt, '%d.%m.%Y') austritt_ge,
    TIMESTAMPDIFF(YEAR, eintritt, IFNULL(austritt, CURDATE())) AS jahre
  FROM mitglieder y
  )
SELECT ym.*,
    TIMESTAMPDIFF(DAY, ym.eintritt + INTERVAL ym.jahre YEAR, IFNULL(austritt, CURDATE())) AS Tage
FROM yearsMember ym;


| id | name | eintritt | austritt | eintritt\_ge | austritt\_ge | jahre | Tage |
|---:|:-----|:---------|:---------|:------------|:------------|------:|-----:|
| 1 | Anna Müller | 2023-01-15 | 2024-06-11 | 15.01.2023 | 11.06.2024 | 1 | 148 |
| 2 | Bernd Schmidt | 2022-06-09 | *null* | 09.06.2022 | *null* | 2 | 2 |
| 3 | Claudia Klein | 2021-03-25 | 2023-05-30 | 25.03.2021 | 30.05.2023 | 2 | 66 |
| 4 | Dieter Groß | 2020-04-10 | 2021-08-15 | 10.04.2020 | 15.08.2021 | 1 | 127 |
| 5 | Eva Lang | 2019-05-05 | *null* | 05.05.2019 | *null* | 5 | 37 |

[fiddle](https://dbfiddle.uk/xHQ_LJCp)


Gruß

Bernd
 
Leider scheine ich kein MySQL zu können^^. Was liefert denn TIMESTAMPDIFF(YEAR,date_format('2023-01-15', '%d.%m.%Y'),date_format('2024-01-13', '%d.%m.%Y')) ?
datediff() bei MSSQL liefert 1, weil es den Jahresanteil vergleicht. Aber es ist ja noch nicht ein Jahr um.

Es ist schon erschreckend wie viele alte Lösung zu dem Thema nicht mehr auffindbar oder total vermurkst sind. Da wird dann durch 365,23 Tage gerechnet und so ein Spaß. Eigentlich bleibt bei MSSQL nur eine Prüfung mit CASE, eine wirklich elegante Lösung zur genauen Berechnung habe ich irgendwie noch nicht gesehen.
 
Hi ukulele,

ich bin nicht sicher ob die Frage an mich ging. Kann es sein das du beim TIMESTAMPDIFF nicht date_format sonder str_to date verwenden wolltest um einen String in ein Datum zu wandeln, denn date_format formatiert dir die Ausgabe halt nur wie du es möchtest.

Schau mal bei meiner Antwort. Dort wird tagesgenau gearbeitet.

Gruß Bernd
 
Ja ging an dich. Habe es ohne explizite Konvertierung gemacht und ja, TIMESTAMPDIFF liefert tatsächlich die Differenz in Jahren. Deine Testdaten enthalten leider keinen Fall wie mein Beispiel.

MySQL
SELECT TIMESTAMPDIFF(YEAR,'2023-03-01','2024-02-20'); --=0
SELECT TIMESTAMPDIFF(YEAR,'2023-03-01','2024-02-29'); --=0
SELECT TIMESTAMPDIFF(YEAR,'2023-03-01','2024-03-01'); --=1

MSSQL
SELECT datediff(year,'2023-03-01','2024-02-20'); --=1
SELECT datediff(year,'2023-03-01','2024-02-29'); --=1
SELECT datediff(year,'2023-03-01','2024-03-01'); --=1
 
Werbung:
ukulele schrieb:
Ja ging an dich. Habe es ohne explizite Konvertierung gemacht und ja, TIMESTAMPDIFF liefert tatsächlich die Differenz in Jahren. Deine Testdaten enthalten leider keinen Fall wie mein Beispiel.

MySQL
SELECT TIMESTAMPDIFF(YEAR,'2023-03-01','2024-02-20'); --=0
SELECT TIMESTAMPDIFF(YEAR,'2023-03-01','2024-02-29'); --=0
SELECT TIMESTAMPDIFF(YEAR,'2023-03-01','2024-03-01'); --=1

MSSQL
SELECT datediff(year,'2023-03-01','2024-02-20'); --=1
SELECT datediff(year,'2023-03-01','2024-02-29'); --=1
SELECT datediff(year,'2023-03-01','2024-03-01'); --=1
Zum Vergrößern anklicken....


Danke dir,

wusste ich auch nicht das sich MSSQL so verhält. Kannst aber einfach im Beispiel bei dbfiddle das insert abändern um zu testen.

Gruß Bernd
 
Zurück
Oben