Write a datetime to the database using model.DateTimeField() in Python Django, the UTC timezone is not correctly displayed in the database

settings.py

TIME_ZONE = "Asia/Taipei"

USE_I18N = True

USE_TZ = True

models.py

from django.db import models

class ApiLog(models.Model):
    endpoint = models.CharField(max_length=255)
    method = models.CharField(max_length=10)
    request_body = models.TextField()
    response_body = models.TextField()
    timestamp = models.DateTimeField()

    class Meta:
        db_table = 'api_log'
        managed = False  # 若資料表為手動建立，將 managed 設為 False



    def __str__(self):
        return f"{self.method} {self.endpoint} at {self.timestamp}"

views.py

def simple_api(request):
    endpoint = request.path
    method = request.method
    request_body = request.body.decode('utf-8') if request.body else ""

    if method == 'GET':
        response_data = {'message': 'This is a GET request'}
    elif method == 'POST':
        try:
            data = json.loads(request.body)
            response_data = {'message': 'Data received', 'data': data}
        except json.JSONDecodeError:
            response_data = {'error': 'Invalid JSON format'}
    else:
        response_data = {'error': 'Method not allowed'}

    response_body = json.dumps(response_data)

    # def UTC+8 
    tz = pytz.timezone('Asia/Taipei')
    # get utc+8 time
    current_utc_plus_8_time = timezone.now().astimezone(tz)

    print("Current UTC+8 Time:", current_utc_plus_8_time)

    # record ApiLog
    ApiLog.objects.create(
        endpoint=endpoint,
        method=method,
        request_body=request_body,
        response_body=response_body,
        timestamp=current_utc_plus_8_time
    )

    return JsonResponse(response_data)

Based on this design, I expect the recorded timestamp in the database to be in the format “yyyy-MM-DD HH:mm:ss.ssssss+08:00.”

However, when querying directly with a SELECT statement in the database, the result is converted to UTC+0. I’ve tried several methods to resolve this issue.

For example:

1. In Java, using mybatis

mapper

@Mapper
public interface ApiLogMapper {

  @Insert("INSERT INTO api_log (endpoint, method, request_body, response_body, timestamp) " +
      "VALUES (#{endpoint}, #{method}, #{requestBody}, #{responseBody}, #{timestamp})")
  @Options(useGeneratedKeys = true, keyProperty = "id")
  void insertApiLog(ApiLog apiLog);
}

controller

  @PostMapping("/message")
  public ResponseEntity<String> handleMessage(@RequestBody String message) {
    // def UTC+8  OffsetDateTime
    OffsetDateTime dateTimeUTC8 = OffsetDateTime.now(ZoneOffset.ofHours(8));
    
    String response = "message";  
    // record ApiLog 
    ApiLog log = new ApiLog();
    log.setEndpoint("/api/chatbox/message");
    log.setMethod("POST");
    log.setRequestBody(message);
    log.setResponseBody(response);
    log.setTimestamp(dateTimeUTC8);
    System.out.println(log.getTimestamp());
    apiLogMapper.insertApiLog(log);

    return ResponseEntity.ok(response);
  }

this method correctly shows UTC+8 results in the database.

2. In Python, using a SQL script for insertion also displays results in UTC+8.

def log_api_call(endpoint: str, method: str, request_body: str,
    response_body: str):
  connection = get_connection()
  cursor = connection.cursor()
  # def utc+8 timezone
  timezone_utc8 = pytz.timezone('Asia/Taipei')
  utc8_time = datetime.now(timezone_utc8)

  print(utc8_time)


  query = """
    INSERT INTO api_log (endpoint, method, request_body, response_body, timestamp)
    VALUES (?, ?, ?, ?, ? AT TIME ZONE 'Taipei Standard Time' )
    """
  cursor.execute(query, (
  endpoint, method, request_body, response_body, utc8_time))
  connection.commit()
  cursor.close()
  connection.close()


@app.post("/example-endpoint")
async def example_endpoint(request: Request):
  request_body = await request.json()

  response_body = {"message": "This is a response"}

  response_body_str = json.dumps(response_body)

  # record APILog
  log_api_call(
      endpoint="/example-endpoint",
      method="POST",
      request_body=json.dumps(request_body),
      response_body=response_body_str
  )

  return response_body

more information: using mcr.microsoft.com/mssql/server:2022-lates TZ=Asia/Taipei

SELECT SYSDATETIMEOFFSET() AS CurrentDateTimeWithOffset;

CurrentDateTimeWithOffset    |
-----------------------------+
2024-11-14 10:25:44.588 +0800|

SELECT CURRENT_TIMEZONE() AS TimeZone;

TimeZone          |
------------------+
(UTC+08:00) Taipei|

table

create table api_log
(
    id            bigint identity
        primary key,
    endpoint      nvarchar(255),
    method        nvarchar(10),
    request_body  nvarchar(max),
    response_body nvarchar(max),
    timestamp     datetimeoffset
)
go

Is there a way to achieve this in the Django framework?

As described in the question, I expect the time displayed in the database to match the UTC time zone in which it was written.

if AP UTC+8 Time is 2024-11-14 09:53:31.916319**+08:00**

,then Select DB timestamp must is 2024-11-14 09:53:31.916319**+08:00**

Вернуться на верх