Why does json.loads function change my timestamp into milliseconds?

The behavior you’re observing where the date column ‘Mon, 15 Apr 2024 11:20:20 GMT’ is converted into a large integer like 1713177478000 is due to how the pandas.DataFrame.to_json() method serializes datetime objects. By default, pandas converts datetime objects to milliseconds since the Unix epoch (January 1, 1970). This is a standard practice for handling date and time data in JSON because JSON itself does not have a built-in date type.

However, if you want to maintain the datetime in a more human-readable string format or a specific format within your JSON output, you can adjust the date_format parameter of the to_json() method. Here’s how you can modify your Flask route to handle this:

Use the date_format=’iso’ parameter in the to_json() method to convert the datetime objects to ISO 8601 string format, which is a widely accepted format for transmitting datetime information in web applications.

Ensure you return a response using Flask’s jsonify() to properly set the response headers and manage JSON serialization correctly.

from flask import Flask, request, jsonify
import pandas as pd
import json

app = Flask(__name__)

@app.route("/tipi", methods=["GET"])
def sqltipi():
    try:
        department = request.args.get("department")
        ogc_fid = request.args.get("ogc_fid")
        score = request.args.get("score")
        longitude = request.args.get("longitude")
        latitude = request.args.get("latitude")
        rayon = request.args.get("rayon")

        # Simulated function calls to a database or similar.
        # These would actually call your DBP.load_* functions.
        def simulate_db_query():
            # Returning a sample DataFrame with datetime column for illustration.
            return pd.DataFrame({
                "date": [pd.Timestamp("2024-04-15 11:20:20")],
                "score": [1]
            })

        if longitude is not None and latitude is not None and rayon is not None:
            df = simulate_db_query()
        elif ogc_fid is not None:
            df = simulate_db_query()
        else:
            df = simulate_db_query()

        if score is not None:
            df = df[df["score"] == int(score)]

        # Change here: using date_format='iso'
        js = df.to_json(orient="records", date_format='iso')
        data = json.loads(js)
        return jsonify(data)  # Using jsonify to return JSON response
    except Exception as e:
        error_message = "An error occurred: " + str(e)
        return jsonify({"error": error_message}), 500

if __name__ == "__main__":
    app.run(debug=True)