CREATE TABLE FiveMinutesData (
TimeStamp datetime NOT NULL,
V_Ph1 float, V_Ph2 float, V_Ph3 float,
P_Ph1 float, P_Ph2 float, P_Ph3 float,
P_Ph1_Day float, P_Ph2_Day float, P_Ph3_Day float,
Flags int,
PRIMARY KEY (TimeStamp)
);
sqlite> select min(timestamp), max(timestamp) FROM fiveminutesdata group by strftime('%Y',datetime(TimeStamp,'localtime'));
1290948000|1647001800
sqlite> select min(timestamp), max(timestamp) FROM fiveminutesdata group by strftime('%Y',datetime(TimeStamp,'unixepoch'));
1290948000|1293812700
1293873900|1325347800
1325410500|1356970500
1357032600|1388507700
1388565900|1420070100
1420070700|1451606100
1451606400|1483228500
1483228800|1514764500
1514764800|1546300500
1546300800|1577836500
1577836800|1609458900
1609459200|1640994600
1640997300|1647001800
看起來，使用本地時間的年度分組是在線分組所有內容。這種行為有解釋嗎？解決方法？
uj5u.com熱心網友回復：
該列timestamp包含整數值，它們是 unix 紀元值，因此您必須使用修飾符'unixepoch'和'localtime'：
SELECT strftime('%Y', datetime(timestamp, 'unixepoch', 'localtime')) year,
MIN(timestamp) min_timestamp,
MAX(timestamp) max_timestamp
FROM fiveminutesdata
GROUP BY year;
或者，更簡單：
SELECT strftime('%Y', timestamp, 'unixepoch', 'localtime') year,
MIN(timestamp) min_timestamp,
MAX(timestamp) max_timestamp
FROM fiveminutesdata
GROUP BY year;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/442091.html
標籤：sqlite 约会时间 通过...分组 最大限度 分钟