Django MySQL组按日期与时区 [英] Django MySQL group by day with timezone
问题描述
class Sale(models.Model):
total = models .DecimalField(max_digits = 8,decimal_places = 2,default = 0)
sale_date = models.DateTimeField(auto_now_add = True)
现在,每个销售sale_date都将其值保存为UTC,所以如果我尝试按日分组和总计所有的销售额:
report = Sale.objects.extra({'day':date(sale_date)})。values('day')。annotate(day_total = Sum('total'))
我希望每天在不同的时区(UTC-6)。 >
有没有办法在一个特定的区域获得正确的金额?我正在使用MySQL。
啊,这是一个很好的挑战。我能够从PostGres测试,我可以确认它正在工作。 MySQL代码应该非常接近。但是,有一个注释在 CONVERT_TZ文档:
要使用命名时区,例如
MET
或欧洲/莫斯科
,时区表必须正确设置。请参阅第10.6节MySQL服务器时区支持,用于指示。
MySQL (使用 CONVERT_TZ(dt,from_tz,to_tz))
from_tz ='UTC'
to_tz ='澳大利亚/ ACT'
report = Sale.objects.extra(
{
'day':date(CONVERT_TZ(sale_date,'{from_tz}','{to_tz}'))format(
from_tz = from_tz,
to_tz = to_tz
)
}
).values(
'day'
).annotate(
day_total = Sum('total')
)
PostGres:(使用 AT TIME ZONE )
time_zone ='澳大利亚/ ACT'
report = Sale.objects.extra(
{'day':date(sale_date)AT时区{0}格式(time_zone)}
).values(
'day'
).annotate(
day_total = Sum('total')
)
Lets say I have a sale model:
class Sale(models.Model):
total = models.DecimalField(max_digits=8, decimal_places=2, default=0)
sale_date = models.DateTimeField(auto_now_add=True)
Now, with each sale sale_date get its value saved in UTC, so if I try to group and sum all the sales by day:
report = Sale.objects.extra({'day':"date(sale_date)"}).values('day').annotate(day_total=Sum('total'))
I get all wrong becouse I expect each day in a different timezone (UTC-6).
Is there a way to get the correct sums in a specific tiemezone? Im working with MySQL.
Ah, this was a good challenge. I was able to test from PostGres, and I can confirm it is working. The MySQL code should be pretty close. However, there is a note on the CONVERT_TZ documentation:
To use named time zones such as
MET
orEurope/Moscow
, the time zone tables must be properly set up. See Section 10.6, "MySQL Server Time Zone Support", for instructions.
MySQL (using CONVERT_TZ(dt, from_tz, to_tz))
from_tz = 'UTC'
to_tz = 'Australia/ACT'
report = Sale.objects.extra(
{
'day': "date(CONVERT_TZ(sale_date, '{from_tz}', '{to_tz}'))".format(
from_tz=from_tz,
to_tz=to_tz
)
}
).values(
'day'
).annotate(
day_total=Sum('total')
)
PostGres: (using AT TIME ZONE)
time_zone = 'Australia/ACT'
report = Sale.objects.extra(
{'day': "date(sale_date) AT TIME ZONE '{0}'".format(time_zone)}
).values(
'day'
).annotate(
day_total=Sum('total')
)
这篇关于Django MySQL组按日期与时区的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!