grouping by year works with 'unixepoch' but not with 'localtime'-CodePudding

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 ?

CodePudding user response：

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;