如何按财年查询以epoch时间格式存储的日期数据

1. Epoch格式可直接实现查询，操作方法如下 #

你只需要先把统计区间的起止时间转换为对应时区的epoch时间戳，直接对time字段做数值范围筛选即可，查询效率极高。
示例SQL（以UTC时区统计为例）：

-- 1598889600 对应2020-09-01 00:00:00 UTC，1630281599对应2021-08-30 23:59:59 UTC
SELECT * FROM 库存表 
WHERE time >= 1598889600 AND time <= 1630281599;

注意：转换起止时间的时间戳时，需和你存储time字段时使用的时区保持一致，避免出现时区偏移导致的统计误差

如果需要按财年分组统计物料数量，也可以直接对时间戳做计算后分组，或者调用数据库内置的时间戳转日期函数实现：

-- MySQL环境统计该财年各月份新增物料数
SELECT MONTH(FROM_UNIXTIME(time)) as 月份, COUNT(*) as 物料数
FROM 库存表
WHERE time BETWEEN 1598889600 AND 1630281599
GROUP BY MONTH(FROM_UNIXTIME(time));

2. 是否需要更换日期存储格式 #

两种存储方案各有优劣，可根据你的实际使用场景选择：

• 保留epoch时间戳的优势：无需修改现有表结构，数值比较性能远高于字符串/日期类型比较，跨数据库兼容性好，只要存储时统一时区就不会出现时区混乱问题，适合仅需做范围筛选的简单统计场景。
• 更换为DATE/DATETIME类型的优势：查询和统计时代码可读性更强，无需提前转换时间戳，做复杂日期计算（如按季度、周度分组，判断闰年、工作日等）时内置函数支持更完善，适合需要频繁做多维度日期统计的场景。

如果不想改动原有存储逻辑，又需要经常做日期维度统计，也可以新增一个冗余的create_date字段，写入数据时同步保存对应的日期值，兼顾两种方案的优势。

内容的提问来源于stack exchange，提问作者Pedro