BASE / In Abfrage Zeitdifferenz berechnen

de-users
#1

Hallo,

im Backend (mySQL / PostgreSQL) gibt es eine Tabelle (Feldtypen in (Klammern)
ID, Datum (DATE), Beginn (TIME), Ende (TIME).

Alle Datensätze beziehen sich nur auf jeweils einen Tag (Mitternacht wird nie überschritten)!

In einer BASE-Abfrage muss ich die Zeitdifferenz ZeitEnde - ZeitBeginn berechnen; meine erfolglosen Versuche:

  * "Ende" - "Beginn": Im Abfrageergebnis wird <OBJECT> ausgegeben,
  * ( SELECT "Ende" - "Beginn" FROM "SchemaName"."TabellenName" ): Im
    Abfrageergebnis wird <OBJECT> ausgegeben,
  * DATEDIFF( 'mi', "Beginn", "Ende" ): ERROR: function
    datediff(unknown, time without time zone) does not exist

Wie geht's richtig?

Danke, Michael

#2

Lieber Michael,

DATEDIFF wäre zunächst schon mal falsch, weil du ja gerade keine Datumsdifferenz hast, sondern eine Zeitdifferenz. Außerdem verwendest du wohl, wie aus den Parametern anzunehmen ist, die DATEDIFF-Funktion von Basic (es gibt eine gleichnamige z.B. bei MariaDB), aber du dich ja im SQL befindest, musst du eine Funktion nehmen, die das Datenbanksystem bereitstellt.
Bei MariaDB und dann sicher auch bei MySQL wäre das TIMEDIFF
SELECT tim1, tim2, TIMEDIFF(tim1, tim2) FROM michael WHERE id = 1;
Das Ergebnis:
tim1                tim2               TIMEDIFF(tim1, tim2)
18:15:18        13:17:17        04:58:01

Herzliche Grüße

Gerhard

#3

Lieber Gerhard,

vielen Dank.

Außerdem verwendest du wohl, wie aus den Parametern anzunehmen ist, die DATEDIFF-Funktion von Basic (es gibt eine gleichnamige z.B. bei MariaDB), aber du dich ja im SQL befindest, musst du eine Funktion nehmen, die das Datenbanksystem bereitstellt.
Bei MariaDB und dann sicher auch bei MySQL wäre das TIMEDIFF
SELECT tim1, tim2, TIMEDIFF(tim1, tim2) FROM michael WHERE id = 1;
Das Ergebnis:
tim1                tim2               TIMEDIFF(tim1, tim2)
18:15:18        13:17:17        04:58:01

Hier leider nicht:
SELECT
"ID-dz",
"Datum", "Beginn", "Ende",
( SELECT "Ende" - "Beginn" FROM "SandBox"."DatZeit" ) "dauer",
"Ende" - "Beginn" "aa",
TIMEDIFF( "Ende", "Beginn" ) "ww" FROM "SandBox"."DatZeit" "DatZeit";
liefert
ERROR: function timediff(time without time zone) does not exist

Ohne die TIMEDIFF-Spalte lautet das Ergebnis:
1   13.09.2018   11:00   12:15   <OBJECT>   <OBJECT>

Herzliche Grüße
Michael

#4

Hallo Michael,

TIMEDIFF( "Ende", "Beginn" ) "ww" FROM "SandBox"."DatZeit" "DatZeit";
liefert
ERROR: function timediff(time without time zone) does not exist

Lass mich raten: JDBC-Verbindung mit einem neueren Connector von MySQL?
Wenn ich da time zone lese, dann werde ich hellhörig.

Also: Die Funktion TIMEDIFF(Zeit1,Zeit2) müsste in MySQL/MariaDB schon
die richtige sein. Wichtig ist, dass beide Zeitangaben den gleichen
Datentyp haben, also beides Zeiten oder beides Timestamp-Felder mit
zusätzlicher Datumsangabe sind.

Mach' doch einmal ein Beispiel, bei dem das auch nachvollziehbar ist.
Handelt es sich um die Zeiten 11:00 Uhr und 12:15 Uhr? Soll also 01:15
oder 75 raus kommen, korrekt?

Gruß

Robert

#5

Lieber Michael,

nachdem du ja PostgreSQL verwendest, habe ich in der dortigen Doku nachgesehen, da gibt es die Funktion TIMEDIFF nicht; soweit ich das verstehe, wird das Minuszeichen verwendet, die Beispiele sind leider durchgängig nur mit Konstanten ausgeführt. Daraufhin habe ich die Subtraktion von TIME-Feldern mal in MariaDB ausprobiert, das geht auch, liefert aber ein zumindest gewöhnungsbedürftiges Ergebnis, s.u.
Ich vermute, dass dein Problem mit dem in deiner Antwort unten genannten Beispiel darin besteht, dass du zusätzlich zu der Subtraktion auch noch TIMEDIFF drin hast, denn die Fehlermeldung bezieht sich ja darauf (du hättest erst einmal bei PostgreSQL schauen sollen, ob die Funktion dort auch existiert bzw. genauso heißt); der Teil davor scheint ja wohl nicht beanstandet zu werden, die Prüfung geht ja normalerweise von links nach rechts.
Lass mal die Zeile mit Timediff weg.
Ich stelle noch ein weiteres Phänomen fest (zumindest in Verbindung mit MariaDB):
Ich hatte den ersten Versuch direkt mit der Datenbank gemacht, da kam dann schön eine Zeitangabe (04:58:01) heraus. Nach genauem Lesen deiner ersten Mail habe ich dann gefunden, dass du das in einer Abfrage verwenden willst, und das dort ausprobiert.

  * Bei der Verwendung von TIMEDIFF erhalte ich bei meinem Beispiel
    0,21, das ist die genannte Zeit als Bruchzeit eines Tages
    (gerundet). Da müsste man dann wohl irgendeine Umwandlung verwenden,
    aber da habe ich wenig Erfahrung, und ohnehin ist das nicht PostgreSQL.
  * Bei der Verwendung der Subtraktion ergibt sich 49801. Da habe ich
    ein bisschen gebraucht, bis ich kapiert habe, dass da die
    Zeitangaben einfach als Zahlen ohne die Doppelpunkte interpretiert
    werden und die Differenz dieser Zahlen gebildet wird: 181518 -
    131717 = 49801.

Das hilft dir nur bedingt, weil es eine andere DB-Engine ist. Aber vielleicht ist das Verhalten bei der Subtraktion bei PostgreSQL ebenso, weil das möglicherweise eine Base-Eigenheit ist, dann hast du schon einen Hinweis.
Versuche auf jeden Fall mal, was direkt mit der Datenbank funktioniert, du hast ja ein Tool für den Zugriff auf PostgreSQL, dessen Namen ich gerade nicht auswendig weiß (aber er steht irgendwo!),  und probiere dann erst in Base. Es ist ja auch die Frage, ob du eine Abfrage in Base brauchst, eine View im DB-System ist normalerweise schneller (die sieht dann für Base wie eine Table aus, aber für die Datenbank verhält sie sich wie eine Abfrage), eine Abfrage brauchst du ja wohl nur, wenn du sie per Programm modifizieren willst, das ist bei einer View aufwe(ä)ndiger.

Herzliche Grüße

Gerhard

#6

Hallo zusammen,

ich antworte zusammenfassend auf beide Nachrichten (vielen Dank dafür!), da es sich teils überschneidet:
Backend: PostgreSQL mit JDBC-Konnector.
Beide Zeitfelder sind Typ TIME, versuchsweise auch Typ TIMEtz (Fehler time zone s.u.).
Ich brauche die Dauer = Ende - Beginn (ohne Datum, da immer nur am gleichen Tag, kein Überschreiten der Mitternacht) in einem Bericht. Auch darin kann ich die Berechnung nicht ohne Fehlermeldung lösen.
In dem (gruppierten) Bericht brauche ich dann auch noch die Summe der Dauern je Gruppe und die Gesamtsumme über Dauer.

Mach' doch einmal ein Beispiel, bei dem das auch nachvollziehbar ist. Handelt es sich um die Zeiten 11:00 Uhr und 12:15 Uhr? Soll also 01:15 oder 75 raus kommen, korrekt?

Ja, 01:15 wäre ideal! Oder auch 0,05208333 = 01/24 + 15/24/60 bzw. für Gerhards Zeitangabe (04:58:01) 0,206956018519 = 04/24 + 58/24/60 + 01/24/60/60.

nachdem du ja PostgreSQL verwendest, habe ich in der dortigen Doku nachgesehen, da gibt es die Funktion TIMEDIFF nicht;

Stimmt, ich hatte es trotzdem ausprobiert ...

soweit ich das verstehe, wird das Minuszeichen verwendet, die Beispiele sind leider durchgängig nur mit Konstanten ausgeführt. Daraufhin habe ich die Subtraktion von TIME-Feldern mal in MariaDB ausprobiert, das geht auch, liefert aber ein zumindest gewöhnungsbedürftiges Ergebnis, s.u.
Versuche auf jeden Fall mal, was direkt mit der Datenbank funktioniert, du hast ja ein Tool für den Zugriff auf PostgreSQL, dessen Namen ich gerade nicht auswendig weiß (aber er steht irgendwo!),  und probiere dann erst in Base.

Das Minuszeichen liefert mit einer Dbeaver-Abfrage mit den Feldtypen TIME das korrekte Ergebnis 01:15. Diese Abfrage in BASE gibt <OBJECT>.
Das Minuszeichen in einer Dbeaver-Abfrage mit den Feldtypen TIMETZ ergibt den
SQL-Fehler [42883]: ERROR: operator does not exis ...

Ich vermute, dass dein Problem mit dem in deiner Antwort unten genannten Beispiel darin besteht, dass du zusätzlich zu der Subtraktion auch noch TIMEDIFF drin hast, denn die Fehlermeldung bezieht sich ja darauf (du hättest erst einmal bei PostgreSQL schauen sollen, ob die Funktion dort auch existiert bzw. genauso heißt); der Teil davor scheint ja wohl nicht beanstandet zu werden, die Prüfung geht ja normalerweise von links nach rechts.
Lass mal die Zeile mit Timediff weg.

Erledigt! Komisch ist, dass mit den Funktionen DATEDIFF (auch mit DATUM + ENDE - DATUM - BEGINN) oder der Funktion AGE der Fehler
ERROR 42883: function DATEDIFF / AGE (time without time zone) does not exist. You might ...

Ich habe dann noch versucht, die TIME-Strings zu zerlegen in der Art:
SELECT split_part("Beginn", ':', 2) as S2 FROM ...
auch hier:
SQL-Fehler [42883]: ERROR: function split_part(time without time zone, unknown, integer) does not exist
  Hinweis: No function matches the given name and argument types. You might need to add explicit type casts.

Zusammengefasst:

  * Minus-Zeichen
      o OK in DBeaver mit Feldtyp TIME (nicht TIMETZ!),
      o <OBJECT>-Fehler in BASE.
  * DATEDIFF oder AGE oder SPLIT schon in DBeaver ERROR 42883 time zone.

Danke!
Michael

#7

Hallo Michael,

ich habe das einmal hier mit LO 6.3.0.4 und PostgreSQL auf OpenSUSE 15
ausprobiert:
Zeit1 = 11:00:00
Zeit2 = 12:15:00

SELECT "Zeit2" - "Zeit1" AS "Alter_Tag", ("Zeit2" - "Zeit1")*24 AS
"Alter_Stunde", ("Zeit2" - "Zeit1")*24*60 AS "Alter_Minute" FROM
"public"."Zeittest" "Zeittest"

"Alter_Tag" gibt erst einmal eine Dezimalzahl aus, die von den Stellen
her gerundet ist: 0,05. Wird die Spalte als Zeit formatiert, so steht
dort 01:15:00.

"Alter_Stunde" gibt ebenfalls eine Dezimalzahl aus: 1,25. Das darf
natürlich jetzt nicht mehr als Zeit formatiert werden.

"Alter_Minute" gibt merkwürdigerweise eine Zeitformatierung aus:
1800:00:00. Das muss natürlich formatiert werden als Dezimalzahl, lässt
sich aber merkwürdigerweise nicht nachträglich formatieren - unbrauchbar.

Die Abfragen ergeben bei mir also mit Zeitfeldern die korrekten Werte.
Als Vorlage für Berichte würde ich aus Abfragen grundsätzlich Ansichten
machen. Dann kann der Berichtsgenerator das Ergebnis nehmen und fängt
nicht an, den SQL-Code zu überprüfen und zu sagen: kann ich nicht.

Gruß

Robert

#8

Hallo Robert,

vielen Dank.

ich habe das einmal hier mit LO 6.3.0.4 und PostgreSQL auf OpenSUSE 15
ausprobiert:
Zeit1 = 11:00:00
Zeit2 = 12:15:00

SELECT "Zeit2" - "Zeit1" AS "Alter_Tag", ("Zeit2" - "Zeit1")*24 AS
"Alter_Stunde", ("Zeit2" - "Zeit1")*24*60 AS "Alter_Minute" FROM
"public"."Zeittest" "Zeittest"

"Alter_Tag" gibt erst einmal eine Dezimalzahl aus, die von den Stellen
her gerundet ist: 0,05. Wird die Spalte als Zeit formatiert, so steht
dort 01:15:00.

"Alter_Stunde" gibt ebenfalls eine Dezimalzahl aus: 1,25. Das darf
natürlich jetzt nicht mehr als Zeit formatiert werden.

"Alter_Minute" gibt merkwürdigerweise eine Zeitformatierung aus:
1800:00:00. Das muss natürlich formatiert werden als Dezimalzahl, lässt
sich aber merkwürdigerweise nicht nachträglich formatieren - unbrauchbar.

Die Abfragen ergeben bei mir also mit Zeitfeldern die korrekten Werte.

Ist das vorstehende Zitat, insbesondere

SELECT "Zeit2" - "Zeit1" AS "Alter_Tag" FROM ...

auf PostgreSQL oder auf BASE bezogen? in PostgreSQL (DBEAVER) funktioniert es ja bei mir auch und liefert 01:15, aber <OBJECT> Fehler in BASE. Ich würde die Abfrage eigentlich gerne in BASE / im Bericht lassen, denn es müssen noch Parameterfelder (Datum von ... bis) etc. abgefragt werden.

Dein SQL-String in BASE liefert bei mir in allen drei Spalten den <OBJECT>-Fehler.
Mit DBeaver hier: Alter_Tag = 01:15:00, Alter_Stunde = 30:00:00, Alter_Minute = 1800:00:00.

Gruß
Michael

#9

Hallo Michael,

Die Abfragen ergeben bei mir also mit Zeitfeldern die korrekten Werte.

Ist das vorstehende Zitat, insbesondere

SELECT "Zeit2" - "Zeit1" AS "Alter_Tag" FROM ...

auf PostgreSQL oder auf BASE bezogen? in PostgreSQL (DBEAVER)
funktioniert es ja bei mir auch und liefert 01:15, aber <OBJECT> Fehler
in BASE. Ich würde die Abfrage eigentlich gerne in BASE / im Bericht
lassen, denn es müssen noch Parameterfelder (Datum von ... bis) etc.
abgefragt werden.

Dein SQL-String in BASE liefert bei mir in allen drei Spalten den
<OBJECT>-Fehler.
Mit DBeaver hier: Alter_Tag = 01:15:00, Alter_Stunde = 30:00:00,
Alter_Minute = 1800:00:00.

... und 30 Stunden sind dann 1,25 Tage, die als 1,25 z.B. für die
Berechnung des Lohns bei Überstunden genutzt werden können
... und 1800 Stunden dann 75 Tage, die dann als 75 Minuten ohne die
Formatierung genutzt werden können

Moment: Du hast doch Deine Datenbank mit PostgreSQL verbunden. Bei mir
läuft das in Base mit einer PostgreSQL-Datenbank. Dann ist Dein Treiber
(JDBC) nicht in Ordnung. Nimm die direkte Verbindung, die schon seit
Beginn von LO dabei ist. Die macht das korrekt. Deswegen habe ich im
Base-Handbuch auch gar keine anderen Verbindungen getestet.

Gruß

Robert

#10

Hallo Robert,

Moment: Du hast doch Deine Datenbank mit PostgreSQL verbunden.

Ja!

Bei mir läuft das in Base mit einer PostgreSQL-Datenbank. Dann ist Dein Treiber (JDBC) nicht in Ordnung.

mag sein ....

Nimm die direkte Verbindung, die schon seit
Beginn von LO dabei ist. Die macht das korrekt. Deswegen habe ich im
Base-Handbuch auch gar keine anderen Verbindungen getestet.

... aber den nativen hatten wir wegen anderer Fehler (siehe PM) auch schon verworfen. Wenn ich es richtig erinnere, hattest Du sogar eine Bug-Meldung erzeugt (wegen der Anzeige der _Postgres-Tabellen?)

Ratlos
Michael

#11

Lieber Michael,

du hast nichts davon geschrieben, ob du es mit einer View/Ansicht probiert hast, wie Robert und ich beide vorgeschlagen haben. Packe alle Berechnungen einfach da rein.
Ein Beispiel in MariaDB, weil ich das ja nicht in PostgreSQL ausprobieren kann, das musst du entsprechend umschreiben:
SELECT *, TIMEDIFF(tim1, tim2) AS difft, TIME_TO_SEC(TIMEDIFF(tim1, tim2)) AS diffs FROM michael  WHERE id <= 2
Der erste Ausdruck ergibt die Zeitdifferenz in Std:min:sec, der zweite rechnet das noch in Sekunden um.
So passiert alles schon auf DB-Seite, und die Probleme, die Base offenbar macht, werden vermieden.

Wie ich gerade ausprobiert habe, kann der Report-Designer die Zeitangaben im Format mit den Doppelpunkten per Summe schön addieren, also sollte dir der erste der beiden Ausdrücke schon ausreichen.
Hier ein aufbereitetes (weil ich hier keine Abbildung einfügen kann) Ergebnis meines Primitivreports:

1 18:15:18 13:17:17 04:58:01 17881

2 13:07:08 12:05:13 01:01:55 3715

05:59:56 21596

Herzliche Grüße

Gerhard

#12

Lieber Michael,

was ich noch vergessen habe: du kannst natürlich, wenn das nötig ist, in Base eine Abfrage auf diese View definieren.

Herzliche Grüße

Gerhard

#13

Lieber Gerhard,

herzlichen Dank.

du hast nichts davon geschrieben, ob du es mit einer View/Ansicht probiert hast, wie Robert und ich beide vorgeschlagen haben. Packe alle Berechnungen einfach da rein.

Doch, das habe ich probiert (und irgendwas dazu auch geschrieben) und die Abfrage funktioniert richtig. Problem: Ich muss noch vier Felder jeweils zweimal vom Typ DATE und VARCHAR als Parameter-Abfrage einbinden. Für das Datum habe ich das (alles noch in DBeaver) inzwischen geschafft:
WHERE "T Termine"."Datum" >= CAST(CAST(:DatumVON as TEXT) as DATE)    'DATUM als YYYYMMDD eingeben
für die VARCHARs gelingt das nicht:
AND "T PROJKT Daten"."Projekt" = cast(:ProjektBezeichnung as VARCHAR)
oder
AND "T PROJKT Daten"."Projekt" = :ProjektBezeichnung
für Projekt 1070: OK, richtiges Ergebnis
für Projekt ABCD: SQL-Fehler [42703]: ERROR: column "ABCD" does not exist, entsprechend für AB01

Vielleicht ein Problem: In der "T Termine" gibt es für jeden Datensatz eine Projekt-ID. In der BASE-Abfrage hole ich den ProjektName aus der "T PROJKT Daten" die über die Projekt-ID verknüpft ist. Das funktioniert für die Anzeige, alle Werte werden richtig geliefert. Für die Dateneingabe im Formular blockiert die Tabellenverknüpfung die Eingabe, hier muss ich den Projektname mit einem SELECT-Statement in der Abfrage-Spalte holen. Eventuell muss ich das für den Bericht bzw. das Parameterfeld auch so machen?

Ein Beispiel in MariaDB, weil ich das ja nicht in PostgreSQL ausprobieren kann, das musst du entsprechend umschreiben:
SELECT *, TIMEDIFF(tim1, tim2) AS difft, TIME_TO_SEC(TIMEDIFF(tim1, tim2)) AS diffs FROM michael  WHERE id <= 2
Der erste Ausdruck ergibt die Zeitdifferenz in Std:min:sec, der zweite rechnet das noch in Sekunden um.
So passiert alles schon auf DB-Seite, und die Probleme, die Base offenbar macht, werden vermieden.

Vorstehender Absatz ist mir klar, der nachfolgende gar nicht:

Wie ich gerade ausprobiert habe, kann der Report-Designer die Zeitangaben im Format mit den Doppelpunkten per Summe schön addieren, also sollte dir der erste der beiden Ausdrücke schon ausreichen.
Hier ein aufbereitetes (weil ich hier keine Abbildung einfügen kann) Ergebnis meines Primitivreports:

1 18:15:18 13:17:17 04:58:01 17881
2 13:07:08 12:05:13 01:01:55 3715
05:59:56 21596

Vielleicht doch ein Screenshot? Ich habe keine Idee, was Du im Report-Designer machst. Ich hatte schon versucht, dort ein FormatiertesFeld mit der Formel "Ende" - "Beginn" zu belegen, erfolglos. Außerdem muss ich ja noch die (Grupen-)Summen über die Dauern ermitteln.

Danke und herzliche Grüße
Michael