CODEWITHSUNDEEP
sqlpostgresql
Zoran Trifunovski
Zoran Trifunovski

Reputation: 321

sql select query from a single table, results separated by intervals

I have a table where data is being written every 10 seconds. There is a field in the table that holds the timestamp when the data was written. I need to construct a query, where I can specify a timeframe (from date - to date), and an interval, and I want those results to be returned in that interval value. For instance, If I have these records:

and if I want the results grouped in interval of 1 minute, i would want my query to return results:

meaning, to group the sum of the value by intervals. Could this be done in a single SELECT query, or I need to execute as much queries as there are intervals, to get the desired data? Any form of query optimization would be appreciated.

Upvotes: 0

Views: 209

Answers (2)

jpw
jpw

Reputation: 44871

Given a tableyour_table with columnsts timestamp/datetime, val int one option if you want to group by minute would be to deduct the seconds part of the date and group by that.

The same concept should be possible to use for other intervals.

Using MS SQL it would be:

select 
    dateadd(second, -DATEPART(second,ts),ts) as ts, 
    SUM(val) as v_sum 
from your_table
group by dateadd(second, -DATEPART(second,ts),ts)

I think the Postgresql could be this:

SELECT 
  date_trunc('minute', ts),
  sum(val) v_sum 
FROM
  your_table
GROUP BY date_trunc('minute', ts)
ORDER BY 1

I tried the MSSQL version and got the desired result, but as SQL Fiddle is down at the moment I couldn't try the PG version. and also the PG version, which seems to work.

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17137

Since you just wanted an idea, here's one:

SELECT 
    count(*) AS quantity, 
    extract(year from mytime) ||'-'|| extract(month from mytime) ||'-'|| extract(day from mytime) ||' '|| extract(hour from mytime) ||':'|| extract(minute from mytime) ||':00' AS timestamp 
FROM 
    test 
group by 2;

To test it out simply 

CREATE TABLE test (mytime timestamp not null);
INSERT INTO test VALUES (NOW());

Insert some data like this in different minutes, or manually write it down.

You only need to add CASEs providing 0's for month, day ... when it's not double digit.

Upvotes: 1

Related Questions