按年份分组可与“unixepoch”配合使用但不适用于“本地时间”

发布于 2025-01-13 14:50:58 字数 903 浏览 0 评论 0原文

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

看起来，当使用当地时间时，每年的分组是将所有内容在线分组。这种行为有解释吗？解决办法吗？

原文

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

It looks that the yearly grouping with , when using localtime, is grouping everything in on line.
Is there an explanation for this behavior?
A work around ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

污味仙女 2025-01-20 14:50:58

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;

The column timestamp contains integer values which are unix epoch values, so you have to use the modifier 'unixepoch' and 'localtime':

SELECT strftime('%Y', datetime(timestamp, 'unixepoch', 'localtime')) year,
       MIN(timestamp) min_timestamp, 
       MAX(timestamp) max_timestamp
FROM fiveminutesdata 
GROUP BY year;

Or, simpler:

SELECT strftime('%Y', timestamp, 'unixepoch', 'localtime') year,
       MIN(timestamp) min_timestamp, 
       MAX(timestamp) max_timestamp
FROM fiveminutesdata 
GROUP BY year;

回复收藏 0 原文

~没有更多了~