mysql - 如何根据本地服务器时间获取每个州的基于时间的数据？

Question

我有以下表格和相关列:

state
    id int
    name text
    utc_offset int (minutes)

sale
    id int
    state_id int
    created datetime
    amount decimal(8,2)

created 列中的值为本地服务器时间，状态为 id = 1。

如何获取每个州每天的总销售额？

有没有更好的方法来处理时区数据？

Answer 1

我不太了解 MySQL 语法，但这是在正确的轨道上吗？

SELECT sale.name AS name, 
    adddate(sale.created. interval (state.utc_offset - (select utc_offset from state where id = 1)) minute) AS date, 
    SUM(amount) AS total_amount
FROM sale
JOIN state ON sale.state_id = state.id
GROUP BY name,date;

注意:像这样存储 utc_offset 充其量是草率的，因为 utc_offset 在世界许多地方每年更改两次。存储时区名称并使用实际时区操作库为您计算日期通常是更好的做法。

编辑

我对时区处理的建议是按如下方式修改您的状态表:

state
    id int
    name text
    tz varchar

然后按如下方式更新您的查询:

SELECT sale.name AS name,
    CAST(CONVERT_TZ(sale.created,'utc',state.tz) AS DATE) AS date,
    SUM(amount) AS total_amount
FROM sale
JOIN state ON sale.state_id = state.id
GROUP BY name,date;