SQLite中NZST特定时间戳转换与指定数据查询需求

1. 核心查询逻辑 #

要获取NZST午夜12:00的各唯一name对应数据，需先将数据库中的秒级时间戳转换为NZST，筛选出时间恰好为00:00:00的记录，再将时间戳转换为要求的UTC格式。

SQLite查询语句如下：

SELECT
    strftime('%Y-%m-%dT%H:%M:%SZ', datetime(time, 'unixepoch', 'utc')) AS utc_time,
    name,
    value
FROM your_table
WHERE
    -- 将时间戳转为NZST后，筛选午夜12点整的记录
    strftime('%H:%M:%S', datetime(time, 'unixepoch', '+12 hours')) = '00:00:00'
    -- 限定为当天的午夜，避免匹配历史数据
    AND date(datetime(time, 'unixepoch', '+12 hours')) = date(datetime('now', '+12 hours'))
GROUP BY name;

注：新西兰夏令时（NZDT）期间需将+12 hours改为+13 hours，可根据时段手动调整或在脚本中做动态适配。

2. 定时触发实现 #

SQLite本身不支持内置定时触发器，需借助外部工具实现NZST 00:05的定时执行：

方式一：Linux/macOS系统cron任务 #

执行crontab -e编辑定时任务规则：

# 非夏令时（NZST=UTC+12）：NZST 00:05 对应UTC 12:05
5 12 * * * /usr/bin/python3 /path/to/your/query_script.py
# 夏令时（NZDT=UTC+13）：NZST 00:05 对应UTC 11:05
5 11 * * * /usr/bin/python3 /path/to/your/query_script.py

方式二：Python定时脚本 #

用schedule库实现定时执行，脚本示例：

import sqlite3
import schedule
import time

def run_query():
    conn = sqlite3.connect('your_database.db')
    cursor = conn.cursor()
    
    query = '''
    SELECT
        strftime('%Y-%m-%dT%H:%M:%SZ', datetime(time, 'unixepoch', 'utc')) AS utc_time,
        name,
        value
    FROM your_table
    WHERE
        strftime('%H:%M:%S', datetime(time, 'unixepoch', '+12 hours')) = '00:00:00'
        AND date(datetime(time, 'unixepoch', '+12 hours')) = date(datetime('now', '+12 hours'))
    GROUP BY name;
    '''
    cursor.execute(query)
    results = cursor.fetchall()
    
    # 按指定格式输出结果
    print(f"utc_time             name           value")
    print("------------------------------------------")
    for row in results:
        print(f"{row[0]}         {row[1]}              {row[2]:.2f}")
    
    conn.close()

# 若运行环境为UTC时区，NZST 00:05对应UTC 12:05（非夏令时）
schedule.every().day.at("12:05").do(run_query)

# 持续运行监听任务
while True:
    schedule.run_pending()
    time.sleep(60)

3. 测试验证 #

使用你提供的测试数据生成代码创建测试库后，运行上述查询脚本，会得到类似如下的输出：

utc_time             name           value
------------------------------------------
2024-09-30T12:00:00Z         1              0.45
2024-09-30T12:00:00Z         2              0.72
2024-09-30T12:00:00Z         3              0.18

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