Can't get the chart.js graph on my web app to populate when pulling data from my database and I know it's related to the format of the date returned

Question

For background, I am working on my first web app using heroku + flask/python. It's a personal project to track soil humidity in various potted plants around my house using an esp32 and sensors that push sensor readings via a POST request to my api and into POSTgres.

All of that is working just fine. However on one of the pages of my web app I have a chart.js line graph to track said readings that takes values(being the readings from the sensor) and labels(being the time/date of when they were stored).

I know the graph works, as I can get it populating by using fake data for the labels in a format such as "01-01-2024" but not the return of my query for actual data.

Here's how it looks with fake data:

My database is actually storing date/time in this standard format: 2024-05-25 14:33:17.314 -- I can see this using dbeaver. But when I make my database call I receive a list of these: datetime.datetime(2024, 5, 25, 19, 7, 15, 97998).

Is this likely something with my query:

RETRIEVE_LOG_DATA = "SELECT * FROM data WHERE user_id = (%s) ORDER BY date DESC;"

Or am I not doing what I need to on the python side to interpret/reformat this.

# Query database for all plant1 history
with connection:
    with connection.cursor() as cursor:
        cursor.execute(helpers.RETRIEVE_LOG_DATA, (session["user_id"],))
        data = cursor.fetchall()
        
last_reading = data[len(data)-1][0]
labels = [row[7] for row in data]   # <----------------------
values_p1 = [row[4] for row in data]

Tried data in one format and it worked great. So it's definitely that. -- Not sure how to process the return I receive from my DB and get it where it needs to be.

Answer 1

In general, dates and datetimes should be stored using their dedicated data types. Such values aren't in any display format; they simply represent a date or datetime value. The tools you use to inspect your data will display them using whatever internal logic they have.

The good news is that you're already doing this. Getting Python datetime.datetime values as the response to your query indicates that the data is stored as a datetime in the database.

You'll need to tell Python how you want the values formatted, probably by passing format codes to datetime.strftime() in your existing list comprehension:

labels = [row[7].strftime("%m-%d-%Y") for row in data]
#               ^^^^^^^^^^^^^^^^^^^^^