Timestamp runden in Base

Deutsch
#1

LibreOffice Version 25.8.7.3 (AARCH64) auf MacOS 26.5.2, HSQLDB Datenbank

Ich versuche Zeitangaben vom Typ “Timestamp” in Base auf 30 Minuten Intervalle zu runden. Leider wird bei der Methode, die ich bisher gefunden habe, aus dem “Timestamp” ein “Decimal”. Dadurch kann ich gerundete Zeitangaben und nicht zu rundende Angaben nicht mehr in der gleichen Spalte speichern.
Ich habe bisher auch keine Methode gefunden die gerundete Zeit wieder in einen “Timestamp” zu konvertieren.
Eine einfache Methode aus den nicht zu rundenden Angaben ebenfalls "Decimal"s zu machen wäre eine Notlösung.
Für die weitere Verarbeitung und die Fehlersuche wären aber eine Spalte und “Timestamp” Werte sehr hilfreich.

Zum Nachbauen des Problems kann man so eine Tabelle erstellen und vorbelegen:

create table "T1" ( "TS" Timestamp);
insert into "T1"  Values ( '2026-03-01 16:05' );

Meine bisherige Version der Abfrage mit Runden sieht so aus, funktioniert aber nicht:

SELECT CASE WHEN MINUTE( "TS" ) = '30' 
THEN "TS" 
ELSE DATEDIFF( 'dd', '1899-12-30', "TS" ) + 
HOUR( "TS" ) / 24.0000000000 + 
CEILING( MINUTE( "TS" ) / 30.00 ) / 48.0000000000 END 
FROM "T1"

Der then und der else Zweig liefern unterschiedliche Zahlenformate. Setzt man im then-Zweig als Ausgabewert einfach die Zahl 1, wird die Abfrage akzeptiert.
Natürlich müsste man auch auf den Minutenwert Null testen und nicht nur auf 30, aber das Problem ist auch so nachvollziehbar.

#2

Das ganze soll doch eine Abfrage sein und nicht dazu dienen, den gerundeten Wert irgendwo rein zu schreiben - oder? Dann reicht doch eine Abfrage mit dem Wert als Dezimalzahl. Die Formatierung erfolgt dann im Formular (oder ggf. in einer Ansicht).
.
Die CASE WHEN - Bedingung brauchst Du nicht. Wenn Du CEILING nimmst, dann wird aber immer aufgerundet. Ansonsten wäre ROUND die Funktion der Wahl.
.
Es reicht also:

SELECT DATEDIFF( 'dd', '1899-12-30', "TS" ) + 
HOUR( "TS" ) / 24.0000000000 + 
CEILING( MINUTE( "TS" ) / 30.00 ) / 48.0000000000 
AS "TS_gerundet"
FROM "T1"

.
Ich habe das jetzt auch einmal mit der direkten korrekten Formatierung versucht. Erscheint aber nur in Abfragen, wenn direkte SQL-Ausführung angklickt wird:

SELECT "Datetime", 
CAST(
YEAR("Datetime")||'-'||
RIGHT('0'||MONTH("Datetime"), 2)||'-'||
RIGHT('0'||DAY("Datetime"),2)||' '||
RIGHT('0'||HOUR( "Datetime" ) + CASE WHEN CEILING( MINUTE( "Datetime" ) / 30.00 ) = 2 THEN 1 ELSE 0 END, 2)||':'||
CASE WHEN CEILING( MINUTE( "Datetime" ) / 30.00 ) = 1 THEN '30' ELSE '00' END||':'||'00' 
AS TIMESTAMP) AS "DT" 
FROM "tbl_DateTime"

Ich habe hier das Feld allerdings anders benannt - an meine Testdatenbank angepasst.
.
Gruß

Robert

#3

Auch ein guter Ansatz. Danke Robert.
Ich habe allerdings Zeiten aus zwei Quellen. Eine liefert grundsätzlich Zeiten im 30-Minuten Raster, die andere gemessene Zeiten. Wenn ich mit der gemessenen Zeit weiterarbeite, dann muss ich sie aufrunden.
Ich könnte also die gemessenen Zeiten mit folgendem Befehl in eine Dezimalzahl umwandeln und mir den Aufruf von mehreren wahrscheinlich aufwendigeren Funktionen sparen:

SELECT cast("TS" AS Timestamp ) as "TG" 
FROM "T1"

Das liefert komischerweise für die Timestamp-Variable TS keinen Timestamp zurück sondern eine Dezimalzahl. Man kann für TS auch zum Beispiel die Dezimalzahl “46082.67” einsetzen. Das ändert nichts. Ist das möglicherweise ein Fehler?

Grüße Markus.

#4

Der cast macht bei mir leider aus der Zeichenkette keinen Timestamp sondern eine Dezimalzahl.

Grüße Markus.

#5 
SELECT CAST(EXTRACT(YEAR FROM "TS") || '-' || EXTRACT(MONTH FROM "TS") || '-' || EXTRACT(DAY FROM "TS") || ' ' || EXTRACT(HOUR FROM "TS") || ':' AS VARCHAR(14))  
	|| CASE
	      WHEN EXTRACT(MINUTE FROM "TS") < 30 THEN '00'
	      ELSE '30'
	   END
AS "Round30"
FROM "TIMESTAMPS"

Round30
…
CAST(“Round30” AS TIMESTAMP)
:thinking:

#6

Hallo CRDF.

Welche Version von LibreOffice verwendest Du? Bei mir ergibt der cast auf die Zeichenkette in Round30 wieder eine Dezimalzahl und keinen Timestamp.

Grüße Markus.

#8

Und :thinking:
Zum Beispiel

CREATE TABLE ROUND30 (
    TS TIMESTAMP PRIMARY KEY,
    R30 TIMESTAMP GENERATED ALWAYS AS (
        CAST(CAST( EXTRACT( YEAR FROM "TS" ) || '-' || EXTRACT( MONTH FROM "TS" ) || '-'      || EXTRACT( DAY FROM "TS" ) || ' ' || EXTRACT( HOUR FROM "TS" ) || ':' AS VARCHAR ( 14 ) ) || 
        CASE WHEN EXTRACT( MINUTE FROM "TS" ) < 30 THEN '00' ELSE '30' END AS TIMESTAMP)
    )
)

ComputedColumn

#14

Nein, das sieht nur in der Benutzeroberfläche so aus. Für die GUI werden Datums- und Zeitwerte in formatierbare Zahlen umgewandelt, analog zu Calc und Writer. Bei berechneten SQL-Werten erscheint dann oft die unformatierte Dezimalzahl. Bei weiterer SQL-Verarbeitung wird der Wert aber als TIMESTAMP behandelt. Generell sollte man von eingebetteten Datenbanken Abstand nehmen.

P.S. Ich würde die Werte in Calc analysieren. =CEILING(B2;1/48) ist die simple Formel bzw. deutsch =OBERGRENZE(B2;1/48), die den Wert in B2 auf das nächste 48stel eines Tages (30 Minuten) aufrundet.

#15

Danke für die Rückmeldung Villeroy. Wenn ich die Abfrage in Calc einbinde dann wird der TIMESTAMP wirklich erkannt. Aber ist es nicht ein Bug von Base / HSQLDB, wenn dort der TIMESTAMP nicht korrekt dargestellt wird? Die interne Darstellung als Zahl ist ja das eine. Die optisch korrekte Darstellung des Datentyps das andere. Siehe auch das Beispiel unten.

Eine größere Lösung würde ich auch nicht mit HSQLDB realisieren, aber es geht um die Überprüfung von Daten und Ergebnissen einer Applikation. Nach der Zuordnung von Daten zu den richtigen Messintervallen, wofür ich das Aufrunden brauche, sind noch Datensätze aus anderen Tabellen einzubeziehen. Mir fällt das einfacher, wenn ich in Base bleibe.

Für mich sieht die Lösung aktuell so aus:

image
image2312×526 169 KB

In einer ersten Abfrage erstelle ich mir den “DAYSTR”, der dafür sorgt, dass die Zahlenwerte in “DD” (DateDiff) und “CD” (Ceiling Datediff) klein bleiben. Für die Rückumwandlung in einen TIMESTAMP benutze ich den Trick mit der automatischen Umwandlung in Stunden und Minuten. Das dürfte vertretbar sein weil das einfach geht und meine Lösung nur für temporäre Prüfungen und keine längerfristige Lösung gedacht ist. Ich musste aber noch dafür sorgen, dass die Angaben für die Sekunden im String “X” mindestens zwei Stellen vor dem Komma haben. Sonst ist das Format für den CAST AS TIMESTAMP verletzt. Schön, dass die führende Null bei den Sekunden akzeptiert wird. Nicht so schön, dass der Timestamp “Y” nur als Dezimalzahl dargestellt wird.

Wenn die Funktion DateAdd in HSQLDB implementiert wäre, dann wäre Verwendung von DateAdd die zu favorisierende Lösung des Problems des Aufrunden.

#16

Es ist einer von unzähligen Bugs in Base. Dieser ist eher von der harmlosen Sorte. In Calc oder in Writer-Tabellen würde die Dezimalzahl ebenso funktionieren wie der Zeitstempel. Tabellenkalkulationen kennen keine Datumswerte, sondern nur diese Dezimalzahlen, die als Datum (fortlaufende Tagesnummer) und Zeit (Fraktion eines Tages) formatiert werden können. Der importierte Zeitstempel wird also in eine formatierte Dezimalzahl umgewandelt. Entferne die Zellformatierung und die Dezimalzahl kommt zum Vorschein.
DATEDIFF('day', '1899-12-30', "TS") berechnet die richtige Tageszahl für Calc. Man kann damit in Base auch das fehlende DATEADD ersetzen und die resultierenden Zahlen mit formatierten Feldern in Berichten und Formularen wieder als Datum/Zeit anzeigen lassen.
Bei Deinem Problem (Aufrunden von Stunden) kommt es aber zu unangenehmen Rundungsproblemen.

#17

Grundsätzlich gilt: Für Abfragen werden Formatierungen zur Zeit noch nicht gespeichert. Die GUI versucht, die Formatierungen aus den zugrundeliegenden Tabellen zu erschließen. Klappt das nicht, so wird eine zum Datentyp allgemein passende Formatierung gewählt.
.
Die Formatierung als Timestamp wird bei mir in Abfragen korrekt dargestellt, wenn ich die direkte SQL-Ausführung anklicke. In der GUI wird das grundsätzlich zu einer Dezimalzahl.
.
Will ich korrekt formatierte Ergebnisse erhalten, so muss ich aus der Abfrage eine Ansicht erstellen und dort die Formatierung einstellen. Die wird gespeichert.
.
Ansonsten gilt natürlich immer: Das Hauptbedienelement für Base ist das Formular. Und dort klappt auch die Formatierung.

1 Like
#18

Eben. Tabellen und Abfragen können komplett unsichtbar bleiben. Manchmal nervt die fehlende Formatierung aber in Writers/Calcs Datenquellenfenster, wenn man ad-hoc gefilterte Daten in anderen Dokumenten verwenden will. Listboxen für Fremdschlüssel wären in diesem Kontext auch sehr hilfreich.

#7

Danke Robert und CRDF.
Ich habe gelernt, dass das der cast nach Timestamp funktioniert, wenn man von einem String im genau passenden Format ausgeht und man kann es dem Algorithmus überlassen Stunden, Minuten und Tage selber zu berechnen. Mein Lösungsvorschlag wäre also dieses hier:

cast('2025-12-01 00:00:' || ceiling(DATEDIFF( 'minute', '2025-12-01', "TS" )/30.0)*1800 AS Timestamp)

Beim Zusammenführen des Datumsstrings und der Berechnung der Sekunden wird eine Zahl mit mindestens einer Nachkommastelle erzeugt (Beispiel: ‘2025-12-01 00:00:7835400.0’). Wird die Zahl zu groß, dann wird ein Exponent verwendet (Beispiel: 1025-12-01 00:00:3.15643122E10). Mit dem Format kommt die Umwandlung in einen Timestamp nicht zurecht. Daher muss man mit einem Datum arbeiten, dass groß genug ist. Dann erledigt der cast alles Weitere.

#9
  • Damit der String eines Zeitstempels tatsächlich in einen TIMESTAMP in HSQLDB umgewandelt werden kann ist ein Datum erforderlich, das bei Monat und Jahr auf jeden Fall 2 Stellen aufweist. Gleiches gilt auch für Stunde, Minute und Sekunde.
  • Die Anzeige als Timestamp erfolgt automatisch, wenn die Abfrage im direkten SQL-Modus ausgeführt wird. Sonst zeigt die Abfrage eine Dezimalzahl an wie bei der einfachen Berechnung zu Anfang.
  • Ich würde mich nie auf zufällig funktionierende Elemente der HSQLDB verlassen. Besser ist, die Stunden und Minuten tatsächlich zu berechnen, so dass zuerst einmal der Sting für die Umwandlung zum Timestam korrekt wird.
#10

[Firebird]

Timestamp
Timestamp970×209 32.4 KB

Dann [HSQLDB]

[...] TO_CHAR("TS", 'YYYY-MM-DD HH') ||
CASE
[...]
    ELSE ':30:00'
END
[...]
#11

@CRDF :

  • Die Funktion rechnet so falsch. Es sollte aufgerundet werden. Bei 0 Minuten bleibt der Wert, von 1 bis 30 Minuten wird daraus 30, von 31 bis 59 Minuten werden daraus eine zusätzliche Stunde.
  • Als DB war HSQLDB vorgegeben. Es sollte möglichst wieder ein Timestamp heraus kommen, kein Text.
#12

R30
R30973×288 27.8 KB

Bitte beachten
CAST(CAST( […]

Kein DATEADD?

#13

Zumindestens nicht in der embedded verwendeten Version 1.8:
https://www.hsqldb.org/doc/1.8/guide/guide.html#N1251E

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.