SQL auslesen und JSON erstellen

[Stefan_NK]

Hallo Leute,

ich habe es nun endlich geschafft, mittels Blockly eine SQL zu befüllen und wieder auszulesen.
Die Testtabelle sieht wie folgt aus:

Datum= Datentyp date
Zeit=Datentyp time
km... =Datentyp int.

Zu meinem Problem:
Ich lese die Tabelle mittels Blockly aus.

<block xmlns="https://developers.google.com/blockly/xml" type="convert_object2json" id="YE=Q8?Z?3wIHm4;Nf+p_" x="297" y="117">
  <field name="PRETTIFY">TRUE</field>
  <value name="VALUE">
    <block type="variables_get" id="P!]-f.8Q#:Osu%xF9gO[">
      <field name="VAR" id="CgfJo8VRSg%C3jf0z5Fh">result_objekt</field>
    </block>
  </value>
</block>

und erhalte im JSON einen Timestamp anstatt dem Datum. Das JSON wird in einen Datpunkt vom Typ JSON geschrieben.

[
  {
    "ID": 1,
    "Datum": "2023-01-14T23:00:00.000Z",
    "Zeit": "12:53:00",
    "kmStart": 300,
    "kmEnde": 500
  },
  {
    "ID": 2,
    "Datum": "2023-01-14T23:00:00.000Z",
    "Zeit": "14:53:00",
    "kmStart": 500,
    "kmEnde": 550
  },
  {
    "ID": 3,
    "Datum": "2023-01-14T23:00:00.000Z",
    "Zeit": "14:53:00",
    "kmStart": 500,
    "kmEnde": 550
  },
  {
    "ID": 4,
    "Datum": "2023-01-15T23:00:00.000Z",
    "Zeit": "15:16:00",
    "kmStart": 500,
    "kmEnde": 550
  },
  {
    "ID": 5,
    "Datum": "2023-01-15T23:00:00.000Z",
    "Zeit": "15:16:00",
    "kmStart": 500,
    "kmEnde": 550
  }
]

In der VIS gelingt es mir auch nicht mit vis.inventwo - JSON das Datum bzw. die Uhrzeit zu formatieren.
Gibt es eine Möglichkeit die Daten im JSON zu formatieren?

LG Stefan

[Stefan_NK]

@stefan_nk
Habe jetzt noch das SQL angepasst. Ist zwar aufwändig aber es funktioniert. Keine Ahnung ob es richtig bzw. die beste Lösung ist

SELECT DATE_FORMAT(Datum, "%d %m %Y") Datum, TIME_FORMAT(Zeit, "%h:%i") Zeit, kmStart, kmEnde FROM iobroker.stefan_test; 

[OliverIO]

@stefan_nk
Das ist die bessere Lösung.
Datum formatieren in JavaScript ist nicht so optimal

[Stefan_NK]

Hallo, ich bin jetzt schon um einiges weiter, habe aber dennoch eine Frage zum SQL:

In der Datenbank ist das Datum im Format yyyy-mm-dd als Datentyp DATE gespeichert.

Das SQL liefert mir so das korrekte Ergebnis und richtig sortiert.

select
ProtTag,
TIME_FORMAT(ZeitStart, "%H:%i") ZeitStart, 
TIME_FORMAT(ZeitEnde, "%H:%i") ZeitEnde
FROM iobroker.mustang ORDER BY ProtTag DESC, ZeitStart DESC;

Spoiler

Wenn ich jetzt aber das Datum im SQL formatiere stimmt die Sortierreihenfolge nicht (mir ist schon klar warum)

select
DATE_FORMAT(ProtTag, "%d.%m.%Y") ProtTag,
TIME_FORMAT(ZeitStart, "%H:%i") ZeitStart, 
TIME_FORMAT(ZeitEnde, "%H:%i") ZeitEnde
FROM iobroker.mustang ORDER BY ProtTag DESC, ZeitStart DESC;

Spoiler

Kann mir jemand von Euch bitte weiterhelfen, wie ich das machen kann, dass ohne Formatierung sortiert wird und die Darstellung aber formatiert wird?

[Stefan_NK]

@stefan_nk sagte in SQL auslesen und JSON erstellen:

select
ok, ich habs mit "herumspielen" hinbekommen
Falls es wer braucht oder eine "elegantere" Lösung kennt:

select
DATE_FORMAT(ProtTag, "%d.%m.%Y") ProtTag1,
TIME_FORMAT(ZeitStart, "%H:%i") ZeitStart, 
TIME_FORMAT(ZeitEnde, "%H:%i") ZeitEnde
FROM iobroker.mustang ORDER BY ProtTag DESC, ZeitStart DESC;

[OliverIO]

@stefan_nk

hm, du findest, das bei d m y das dann richtig sortiert ist?
wenn dann müsste es andersrum lauten y m d

nach einer sortierung müsste es dann so aussehen
wenn du formatierst, dann ist das für die db ein normaler string, der genauso sortiert wird.

01.01.2023
01.02.2023
02.01.2023
...

ich weiß jetzt nicht was für eine DB du genau verwendest, aber
wenn in Feld Datum das Datum in einem für die DB verständlichen Format steht, ich glaube du hast geschrieben Datum hat den Typ Date, dann mach hinten einfach order by datum

Die verwendeten Felder müssen nicht alle in der SELECT Klausel stehen. Wichtig ist das alle Felder über die FROM-Auswahl erreichbar sind bzw. unterschieden werden kann.

Was mir noch aufgefallen ist:

select
DATE_FORMAT(ProtTag, "%d.%m.%Y") ProtTag1,
TIME_FORMAT(ZeitStart, "%H:%i") ZeitStart,

bspw ProtTag1 ist ja der Feldname der für diese Spalte verwendet werden soll. Im Standard SQL wird das mit AS benannt
also

SELECT
DATE_FORMAT(ProtTag, "%d.%m.%Y") AS ProtTag1,
TIME_FORMAT(ZeitStart, "%H:%i") AS ZeitStart,

zur besseren Lesbarkeit werden alle DB-Befehle und Funktionen Groß geschrieben
https://www.w3schools.com/mysql/mysql_alias.asp