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:
- 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.
- 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**
When you store a date/time in Django, it is always converted to utc+0 time.
This appears to be intentional and may result in conversion from Django's time zone to the utc time set in the settings.
from django.utils import timezone
timezone.now() # utc +0 time
timezone.localtime(timezone.now()) # utc +{timzeon in settings} time