0

How to intercept datetime value and make custom format?

Here is how i fetch records and put them in json format:

cursor.execute(sql)

row_headers = [x[0] for x in cursor.description]

json_data = []
for row in cursor.fetchall():
    json_data.append(dict(zip(row_headers, row)))

return jsonify(json_data)

When I send to console i have something like:

('2022000390100002', '00002', 'CTD2X405AXXX', '2022/39/1', 48.0, datetime.date(2022, 12, 20), 4, None)

The json output looks like:

{
  "BoxNumber": "00002",
  "BoxQty": 48.0,
  "DateofSPC": "Tue, 20 Dec 2022 00:00:00 GMT",
  "GPDI_Codice": null,
  "Lot": "2022000390100002",
  "OrderNumber": "2022/39/1",
  "Product": "CTD2X405AXXX",
  "TestResult": 4
}

What I want to do is to change DateofSPC output to dd.mm.yyyyy

Josef
  • 2,648
  • 5
  • 37
  • 73
  • Does this answer your question? [Keep a datetime.date in 'yyyy-mm-dd' format when using Flask's jsonify](https://stackoverflow.com/questions/43663552/keep-a-datetime-date-in-yyyy-mm-dd-format-when-using-flasks-jsonify) – Julia Meshcheryakova Dec 20 '22 at 20:42
  • @JuliaMeshcheryakova not really. I have few columns to output and the problem is how to format this specific one which is datetime – Josef Dec 20 '22 at 21:06
  • The accepted answer https://stackoverflow.com/a/43663918/2347649 shows exactly how to handle datetime columns (verifying type) – Julia Meshcheryakova Dec 20 '22 at 21:09

1 Answers1

2

Type of the column has not worked, so you may use regex:

from datetime import datetime
import re
p = re.compile(r'\w{3},\s\d{1,2}\s\w{3}\s\d{4}\s00:00:00\s\w{3}')

for row in cursor.fetchall():
    m = p.match(row)
    if m != None:
        row = datetime.strptime(row, "%a, %d %b %Y %H:%M:%S %Z")
        row = row.strftime('%d.%m.%Y')

    json_data.append(dict(zip(row_headers, row)))
Mário César Fracalossi Bais
  • 340
  • 2
  • 5