SQL-Abfrage mit UTC-Verschiebung (Datum)
Aly IGERY
Wir haben ein Bibliothek Namens "IGERYsTT" (Time Tracker). Vorgestern hat KI-"Copilot" ein SQL-Query für uns erstellt.
=======Query funkzioniert folgendermassen ...
Z.B.: Bei 'h' AS periode, : zeigt er alle Einträge von Heute 05.05.2026, 00:00:00 bis Heute 05.05.2026, 23:59:59
► Bei 'l' AS periode, : (ohne Zahl) zeigt er alle Einträge von Gestern Tagesende 23:59:59 in gesamte Vergangenheit.
► Bei 'n' AS periode, : (ohne Zahl) zeigt er gesamte alle Einträge von Morgen Tagesbeginn 00:00:00 in der Zukunft
Aber mit 'lX' und 'nX', zeigt er X belibige Tage ab gester und morgen.
Z.B.:
► Bei 'l3' AS periode, : zeigt er alle Einträge von Gestern Tagesende 23:59:59 beginnend in letzten 3 Tage in der Vergangenheit; 'l30', 'l365', 'l3650' … usw
► Bei 'n3' AS periode, : zeigt er alle Einträge von Morgen Tagesbeginn 00:00:00 in nächten 3 Tage in der Zukunft; 'n30', 'n365', 'n3650' … usw
Funktioniert auch sehr gut bis auf ein Ausrutscher: Wenn ich sehr frühen norgens einen Eintrag erstelle z.B. um 01:14, wird nicht Heute, sondern in Gestern Gruppe angezeigt. Erst nach 8 oder 6 Uhr wird wieder in Heute Gruppe angezeigt.
Gestern habe ich den Pänomenen geschildert, er meinte das Problem wäre normal und läge an UTC-Verschiebung. Hat mir korrigierte Version erstellt. Das Problem besteht immer noch. Wieder gemeldet das Bestehendes Problem, diesmal meint er es lege an Datumsformat, ich muss Datumsformat ändern. Erste und zweite version funkzionieren gut nur mit "UTC-Verschiebung". Kann mir bitte jemand weiter helfen?
WITH params AS (
SELECT
'h' AS periode,
'' AS sg, '' AS sk, '' AS si1, '' AS si2,
'' AS sb, '' AS sq, '' AS so, '' AS sm
),
range AS (
SELECT
substr(periode,1,1) AS mode,
CASE WHEN length(periode)>1 THEN CAST(substr(periode,2) AS INTEGER) END AS days
FROM params
),
zeit AS (
SELECT
mode, days,
CASE
WHEN mode='h' THEN strftime('%s',date('now','localtime')||' 00:00:00')
WHEN mode='l' AND days IS NULL THEN -99999999999
WHEN mode='l' AND days IS NOT NULL THEN strftime('%s',date('now','localtime','-'||days||' day')||' 00:00:00')
WHEN mode='n' THEN strftime('%s',date('now','localtime','+1 day')||' 00:00:00')
END AS start_ts,
CASE
WHEN mode='h' THEN strftime('%s',date('now','localtime')||' 23:59:59')
WHEN mode='l' THEN strftime('%s',date('now','localtime','-1 day')||' 23:59:59')
WHEN mode='n' AND days IS NULL THEN 99999999999
WHEN mode='n' AND days IS NOT NULL THEN strftime('%s',date('now','localtime','+'||days||' day')||' 23:59:59')
END AS end_ts
FROM range
)
SELECT
id,
Datum,
KI, Info, Betr, Q, Ort, Bem
FROM
IGERYsTT, params, zeit
WHERE
(
strftime(
'%s',
-- Jahr
substr(Datum, instr(Datum,' ')+1, 4) || '-' ||
-- Monatsname → Monatsnummer
CASE
WHEN substr(Datum, 10, 3)='Jan' THEN '01'
WHEN substr(Datum, 10, 3)='Feb' THEN '02'
WHEN substr(Datum, 10, 3)='Mär' THEN '03'
WHEN substr(Datum, 10, 3)='Apr' THEN '04'
WHEN substr(Datum, 10, 3)='Mai' THEN '05'
WHEN substr(Datum, 10, 3)='Jun' THEN '06'
WHEN substr(Datum, 10, 3)='Jul' THEN '07'
WHEN substr(Datum, 10, 3)='Aug' THEN '08'
WHEN substr(Datum, 10, 3)='Sep' THEN '09'
WHEN substr(Datum, 10, 3)='Okt' THEN '10'
WHEN substr(Datum, 10, 3)='Nov' THEN '11'
WHEN substr(Datum, 10, 3)='Dez' THEN '12'
END
|| '-' ||
-- Tag (immer an Position 6–7)
substr(Datum, 6, 2)
|| ' ' ||
-- Uhrzeit (immer nach Jahr + Leerzeichen)
substr(Datum, instr(Datum,' ')+6)
)
) BETWEEN zeit.start_ts AND zeit.end_ts
AND (params.sg='' OR (KI LIKE '%'||params.sg||'%' OR Info LIKE '%'||params.sg||'%' OR Betr LIKE '%'||params.sg||'%' OR Q LIKE '%'||params.sg||'%' OR Ort LIKE '%'||params.sg||'%' OR Bem LIKE '%'||params.sg||'%'))
AND (params.sk='' OR KI LIKE '%'||params.sk||'%')
AND (params.si1='' OR Info LIKE '%'||params.si1||'%')
AND (params.si2='' OR Info LIKE '%'||params.si2||'%')
AND (params.sb='' OR Betr LIKE '%'||params.sb||'%')
AND (params.sq='' OR Q LIKE '%'||params.sq||'%')
AND (params.so='' OR Ort LIKE '%'||params.so||'%')
AND (params.sm='' OR Bem LIKE '%'||params.sm||'%')
ORDER BY Datum DESC;
=====================================================================================================
Vielen Danks voraus…
Mmm
==========
Aly IGERY
Mmm
Bill Crews
--
You received this message because you are subscribed to the Google Groups "mementodatabase" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mementodataba...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/mementodatabase/fa932dd6-aef5-454f-a35b-04e559f9dff8n%40googlegroups.com.
Aly IGERY
'h' = Today (from 00:00 to 23:59)
'm' = Tomorrow (from 00:00 to 23:59)
'n7' = Next 7 days
'n30' = Next 30 days
'g' = Yesterday (from 00:00 to 23:59)
'l7' = Last 7 days
'l30' = Last 30 days
etc. …
E.g.: With 'h' AS period, it displays all entries from today (May 5, 2026, 00:00:00) up to today (May 5, 2026, 23:59:59).
► With 'l' AS period (without a number), it displays all entries from the end of yesterday (23:59:59) covering the entire past.
► With 'n' AS period (without a number), it displays all entries from the start of tomorrow (00:00:00) covering the entire future.
However, with 'lX' and 'nX', it displays any X number of days starting from yesterday or tomorrow.
E.g.:
► With 'l3' AS period, it displays all entries starting from the end of yesterday (23:59:59) and covering the last 3 days in the past; 'l30', 'l365', 'l3650', etc.
► With 'n3' AS period, it displays all entries starting from the beginning of tomorrow (00:00:00) and covering the next 3 days in the future. 'n30', 'n365', 'n3650' … etc.
It works very well, with one exception: If I create an entry very early in the morning—for instance, at 00:14—it is displayed in the "Yesterday" group rather than "Today." It only reappears in the "Today" group after 6:00 or 8:00 AM.
The day before yesterday, I described this phenomenon to him; he claimed the issue was normal and due to a UTC offset. He then created a corrected version for me. However, the problem persists. I reported the ongoing issue again; this time, he suggested it was due to the date format and that I needed to change it. Both the first and second versions work fine—the only issue is the "UTC offset" glitch.
Mmm
digico Iwate (Digico)
Hallo zusammen,
ich bin mir nicht ganz sicher, aber ich habe versucht, das Problem ein bisschen genauer zu verstehen.
Falls ich irgendwo falsch liege, bitte korrigiert mich gern…📌 Warum frühe Einträge (z. B. 01:14) manchmal als „Gestern“ erscheinen
Nach meinem Verständnis liegt es daran, dass die ursprüngliche Abfrage das Feld Datum als Text verarbeitet hat.
Beispiel:
Sa., 9. Mai 2026 01:14:00Die Abfrage zerlegt diesen Text:
- Wochentag
- Tag
- Monatsname → manuelle Umwandlung in eine Zahl
- Jahr
- Uhrzeit
und setzt daraus ein neues Datum zusammen.
SQLite interpretiert dieses rekonstruierte Datum dann als lokale Zeit.Memento speichert Datum intern aber als UNIX‑Zeit in UTC (Millisekunden).
Dadurch entsteht – so wie ich es verstanden habe – ein Zeitversatz zwischen:
- dem rekonstruierten lokalen Datum
- und dem tatsächlichen UTC‑Zeitstempel
Und genau dieser Unterschied führt wohl dazu, dass sehr frühe Einträge manchmal in die Gruppe „Gestern“ rutschen.
🟢 Lösung: Datum nicht mehr als Text parsen
Ich habe versucht, die Abfrage so umzubauen, dass:
- Datum direkt als UNIX‑Zeit (Millisekunden) verwendet wird
- keine Text‑Zerlegung mehr nötig ist
- alle Zeitgrenzen mit UTC+2 fixiert berechnet werden
(damit keine Abhängigkeit vom Gerät, der Zeitzone oder Sommerzeit entsteht)
Seitdem tritt das Problem bei mir nicht mehr auf.
Ich hoffe, dass es auch bei euch funktioniert – und falls nicht, sagt bitte Bescheid.🟦 Überarbeitete Abfrage (mit deutschen Kommentaren)
-- IGERYsTT (Time Tracker) — Optimierte Abfrage ohne UTC-Fehler
-- Version: 2026.05.11
-- Diese Version vermeidet vollständig das Problem,
-- dass frühe Einträge (z.B. 01:14) als "Gestern" erscheinen.
-- Grund: Datum wird NICHT mehr als Text geparst, sondern als UNIX‑Zeit (Millisekunden).
WITH params AS (
SELECT
'h' AS periode, -- 'h', 'l3', 'n30' usw.
'' AS sg, '' AS sk, '' AS si1, '' AS si2,
'' AS sb, '' AS sq, '' AS so, '' AS sm
),
-- Periode zerlegen: Modus + optionale Tageszahl
range AS (
SELECT
substr(periode, 1, 1) AS mode,
NULLIF(substr(periode, 2), '') AS days
FROM params
),
-- Zeitgrenzen berechnen (UTC+2 fixiert, unabhängig vom Gerät)
zeit AS (
SELECT
mode,
CAST(days AS INTEGER) AS days,
CASE
WHEN mode='h'
THEN strftime('%s', date('now', '+2 hours'), 'start of day')
WHEN mode='l' AND days IS NULL
THEN -99999999999
WHEN mode='l' AND days IS NOT NULL
THEN strftime('%s', date('now', '+2 hours', '-'||days||' day'), 'start of day')
WHEN mode='n'
THEN strftime('%s', date('now', '+2 hours', '+1 day'), 'start of day')
END AS start_ts,
CASE
WHEN mode='h'
THEN strftime('%s', date('now', '+2 hours'), 'start of day', '+1 day', '-1 second')
WHEN mode='l'
THEN strftime('%s', date('now', '+2 hours', '-1 day'), 'start of day', '+1 day', '-1 second')
WHEN mode='n' AND days IS NULL
THEN 99999999999
WHEN mode='n' AND days IS NOT NULL
THEN strftime('%s', date('now', '+2 hours', '+'||days||' day'), 'start of day', '+1 day', '-1 second')
END AS end_ts
FROM range
),
-- Datum (Millisekunden) → Anzeigezeit (UTC+2)
IGERYsTT_view AS (
SELECT
id,
Datum,
datetime(Datum / 1000, 'unixepoch', '+2 hours') AS Anzeigezeit,
KI, Info, Betr, Q, Ort, Bem
FROM IGERYsTT
)
-- Hauptabfrage
SELECT
v.id,
v.Anzeigezeit,
v.KI, v.Info, v.Betr, v.Q, v.Ort, v.Bem
FROM
IGERYsTT_view AS v
CROSS JOIN params
CROSS JOIN zeit
WHERE
-- Vergleich über UNIX‑Zeit (Millisekunden → Sekunden)
(v.Datum / 1000) BETWEEN zeit.start_ts AND zeit.end_ts
-- Volltextsuche über mehrere Felder
AND (
params.sg = '' OR
CASE
WHEN v.KI LIKE '%'||params.sg||'%' THEN 1
WHEN v.Info LIKE '%'||params.sg||'%' THEN 1
WHEN v.Betr LIKE '%'||params.sg||'%' THEN 1
WHEN v.Q LIKE '%'||params.sg||'%' THEN 1
WHEN v.Ort LIKE '%'||params.sg||'%' THEN 1
WHEN v.Bem LIKE '%'||params.sg||'%' THEN 1
ELSE 0
END = 1
)
-- Einzelfeldfilter
AND (params.sk = '' OR v.KI LIKE '%'||params.sk||'%')
AND (params.si1 = '' OR v.Info LIKE '%'||params.si1||'%')
AND (params.si2 = '' OR v.Info LIKE '%'||params.si2||'%')
AND (params.sb = '' OR v.Betr LIKE '%'||params.sb||'%')
AND (params.sq = '' OR v.Q LIKE '%'||params.sq||'%')
AND (params.so = '' OR v.Ort LIKE '%'||params.so||'%')
AND (params.sm = '' OR v.Bem LIKE '%'||params.sm||'%')
ORDER BY
v.Datum DESC;Wenn jemand Verbesserungsvorschläge hat oder ich etwas übersehen habe,
würde ich mich sehr über Hinweise freuen.Viele Grüße
Digico
Aly IGERY
at line 4, column 27.
Was expecting:
")"
Vielen Danks und Allesgute
digico Iwate (Digico)
Hallo,
vielen Dank für den Hinweis! Ich glaube, ich habe den Fehler gefunden.
Der SQL‑Explorer von Memento reagiert manchmal empfindlich auf Kommentare, vor allem wenn im Kommentar Kommas oder Klammern stehen.
Dadurch interpretiert er Teile des Kommentars als SQL‑Syntax und zeigt dann Fehler wie „unerwartetes Token“ an.
Die Abfrage selbst war korrekt – nur die Kommentare waren zu lang.
Ich habe deshalb eine Version mit kurzen, sicheren Kommentaren erstellt. Diese sollte ohne Fehlermeldung funktionieren:
-- IGERYsTT Optimierte Abfrage
-- Keine Textverarbeitung
-- UNIX-Zeit (ms)
-- UTC+2
WITH params AS (
SELECT
'h' AS periode,
'' AS sg, '' AS sk, '' AS si1, '' AS si2,
'' AS sb, '' AS sq, '' AS so, '' AS sm
),
range AS (
SELECT
substr(periode, 1, 1) AS mode,
NULLIF(substr(periode, 2), '') AS days
FROM params
),
IGERYsTT_view AS (
SELECT
id,
Datum,
datetime(Datum / 1000, 'unixepoch', '+2 hours') AS Anzeigezeit,
KI, Info, Betr, Q, Ort, Bem
FROM IGERYsTT
)
SELECT
v.id,
v.Anzeigezeit,
v.KI, v.Info, v.Betr, v.Q, v.Ort, v.Bem
FROM
IGERYsTT_view AS v
CROSS JOIN params
CROSS JOIN zeit
WHERE
(v.Datum / 1000) BETWEEN zeit.start_ts AND zeit.end_ts
AND (
params.sg = '' OR
CASE
WHEN v.KI LIKE '%'||params.sg||'%' THEN 1
WHEN v.Info LIKE '%'||params.sg||'%' THEN 1
WHEN v.Betr LIKE '%'||params.sg||'%' THEN 1
WHEN v.Q LIKE '%'||params.sg||'%' THEN 1
WHEN v.Ort LIKE '%'||params.sg||'%' THEN 1
WHEN v.Bem LIKE '%'||params.sg||'%' THEN 1
ELSE 0
END = 1
)
AND (params.sk = '' OR v.KI LIKE '%'||params.sk||'%')
AND (params.si1 = '' OR v.Info LIKE '%'||params.si1||'%')
AND (params.si2 = '' OR v.Info LIKE '%'||params.si2||'%')
AND (params.sb = '' OR v.Betr LIKE '%'||params.sb||'%')
AND (params.sq = '' OR v.Q LIKE '%'||params.sq||'%')
AND (params.so = '' OR v.Ort LIKE '%'||params.so||'%')
AND (params.sm = '' OR v.Bem LIKE '%'||params.sm||'%')
ORDER BY
v.Datum DESC;
Falls es trotzdem noch Probleme gibt, sagt bitte Bescheid. Ich helfe gern weiter.
Viele Grüße Digico
digico Iwate (Digico)
Hallo nochmal,
falls diese Version auch noch Probleme macht, gehen wir einfach komplett ohne Kommentare weiter. Ich hatte schon irgendwie das Gefühl, dass die Kommentare der Auslöser sein könnten, aber ich selbst konnte es leider nicht sauber beheben – da musste ich ein bisschen schmunzeln 😅
Hier ist die komplett kommentarlos Version, die auf jeden Fall funktionieren sollte:
Wenn trotzdem noch etwas nicht klappt, sagt bitte einfach Bescheid. Ich helfe gern weiter.
Viele Grüße Digico
Aly IGERY
digico Iwate (Digico)
Hallo zusammen,
ich glaube inzwischen, dass das Problem weniger an der Query selbst liegt, sondern eher daran, dass momentan ein lokalisiertes Anzeige-Datum direkt in SQL wieder zurück in einen Zeitwert umgewandelt wird.
Aktuell wird ja ein String wie:
Sa., 9. Mai 2026 11:20:04
innerhalb der SQL-Abfrage erneut zerlegt und mit strftime('%s', ...) in Unix-Time konvertiert.
Dabei können offenbar UTC-/localtime-Verschiebungen entstehen, weshalb Einträge in den frühen Morgenstunden manchmal noch in „Gestern“ landen.
Die eigentliche Logik mit:
h
l30
n365
finde ich dagegen sehr gut gelöst.
Das ist eine flexible dynamische Zeitraumsuche.
Ich habe selbst schon öfter mit Datums-/Zeitproblemen in Memento Database kämpfen müssen, deshalb kam mir ein anderer Gedanke:
Vielleicht wäre es stabiler, zusätzlich zum Anzeige-Datum noch einen separaten Timestamp-Wert in Memento Database zu speichern, z.B. per Moment:
moment(field("Datum")).unix()oder:
moment(field("Datum")).valueOf()Moment scheint mir dafür eigentlich ideal zu sein, weil man dort Anzeigeformat und interne Zeitbasis sauber trennen kann.
Zum Beispiel könnte Moment gleichzeitig:
Anzeigeformate erzeugen
Monatsnamen / Locale behandeln
Jahreswerte liefern
Tages-/Monatsgrenzen berechnen
Unix-Timestamps erzeugen
während SQL später nur noch numerisch vergleichen müsste:
WHERE DatumTS BETWEEN start_ts AND end_tsDann müsste SQL nicht jedes Mal den lokalisierten Text erneut analysieren.
Dadurch würden wahrscheinlich:
UTC-Probleme
lokale Zeitverschiebungen
Monatsnamen-/Locale-Probleme
Nacht-/Frühmorgen-Fehler
deutlich reduziert werden, und die Query selbst würde viel einfacher und schneller werden.
Die dynamische Periodenlogik (h, lX, nX) könnte dabei unverändert bestehen bleiben.
Die Library habe ich mir übrigens auch angesehen 😅
Aber ehrlich gesagt hat sie inzwischen so viele Parameter und Ebenen, dass ich nicht mehr vollständig nachvollziehen konnte, welche Teile gerade:
Anzeigeformat
lokale Zeit
UTC
interne Zeitberechnung
Zeitraumlogik
übernehmen.
Deshalb kam ich wahrscheinlich auf die deutlich simplere Idee mit einem festen technischen Timestamp.
Vielleicht denke ich da zu pragmatisch 🙂
Aber aus meiner bisherigen Memento-Erfahrung wirkt:
Anzeige bleibt Anzeige
Zeitvergleich bleibt numerisch
meistens deutlich stabiler und leichter wartbar.
Dann viel Erfolg 🙂
Aly IGERY
Das ist ein Entwurf von KI "Copilot". Selbs die anderen "ChatGPT" und "Google Gemini" kommen auch nicht klar. Jedes mal wenn Sie komplette Query überarbeiten kommt immer gleiche Effekt. Na ja, wir werden uns erstmal damit abfinden und abwarten. Vielleicht ändert es sich mit der Zeit.
Mmm
Aly IGERY
The correct code is below (thanks to AnthropicClaude).
If anyone ever runs into issues with UTC in an SQL query...
==================================
zeit AS (
SELECT
mode,
days,
-- START_TS (robust)
CASE
WHEN mode2 = 'hl' THEN
strftime('%s', date('now','localtime') || ' 00:00:00', 'utc')
WHEN mode2 = 'hn' THEN
strftime('%s', datetime('now','localtime'), 'utc')
WHEN mode = 'h' THEN
strftime('%s', date('now','localtime') || ' 00:00:00', 'utc')
WHEN mode = 'l' AND days IS NULL THEN
-99999999999
WHEN mode = 'l' AND days IS NOT NULL THEN
strftime('%s', date('now','localtime','-'||days||' day') || ' 00:00:00', 'utc')
WHEN mode = 'n' THEN
strftime('%s', date('now','localtime','+1 day') || ' 00:00:00', 'utc')
END AS start_ts,
-- END_TS (robust)
CASE
WHEN mode2 = 'hl' THEN
strftime('%s', datetime('now','localtime'), 'utc')
WHEN mode2 = 'hn' THEN
strftime('%s', date('now','localtime') || ' 23:59:59', 'utc')
WHEN mode = 'h' THEN
strftime('%s', date('now','localtime') || ' 23:59:59', 'utc')
WHEN mode = 'l' THEN
strftime('%s', date('now','localtime','-1 day') || ' 23:59:59', 'utc')
WHEN mode = 'n' AND days IS NULL THEN
99999999999
WHEN mode = 'n' AND days IS NOT NULL THEN
strftime('%s', date('now','localtime','+'||days||' day') || ' 23:59:59', 'utc')
END AS end_ts
FROM range
)
Best regards