背景
数据库部署在谷歌云平台,而且表里面的字段的时间为UTC时间,现在需求:按月展示每天最大的带宽
解决方案
1. 当传递月份的时候,转换为utc时区时间,然后按天分组,查出每天最大的带宽,最后将结果集转换为北京时区。
结果悲催了,结果集里面3月份会有2月份的数据。
原因是:按天分组的时候,会把小时给截取了,按utc查出来的时间,再转换为北京时区+8的时候,时间是不准确的。
2.按北京时区的时间分组,这样把小时截取的时候,是不影响正常结果的,但问题是如何获取查询按北京时区分组。
翻阅资料发现,mysql的内置函数CONVERT_TZ(create_time,’+00:00’,’+8:00’)) 是支持的,
CONVERT_TZ(create_time,"+00:00",“08:00”) 函数 第一个参数为转化字段,第二个参数为当前时区,第三个参数为目标时区。
这样查询出来是满足条件的,但是其中的create_time的返回结果还是utc时间,但是由于按天分组,所以再加8是没必要的,直接展示就是满足需求的。
其实我一开始,也以为结果集的时间也是北京时区的时间,但当我只是用CONVERT_TZ(create_time,’+00:00’,’+8:00’))只是作为过滤条件去查询的时候,发现结果集的时间还是utc,是和直接查表转换成北京时区比较,才发现的。
代码如下
#按天分组查询 我发现sqlalchemy是不支持的,所以只能用原生sql,出错提示如下方图片,想以后再找找原因
" SELECT date(CONVERT_TZ(time,’+00:00’,’+8:00’)) AS bj_time,"
“max(monitor_bandwidth) AS monitor_bandwidth”
" FROM billing WHERE CONVERT_TZ(time,’+00:00’,’+8:00’) "
" BETWEEN ‘{0}’ AND ‘{1}’ "
" GROUP BY bj_time ORDER BY bj_time DESC LIMIT {2} OFFSET {3};". \
#作为过滤条件查询
data = db.session.query(Billing.time, Billing.monitor_bandwidth, ).
filter(func.CONVERT_TZ(Billing.time, ‘+00:00’, ‘+8:00’).between(start_time, end_time)).
order_by(Billing.time.desc()).all()
如果觉得《数据库存储时间时区UTC 如何转换为北京时区》对你有帮助,请点赞、收藏,并留下你的观点哦!