Average on historical data for production shifts

Ignition
1

Hello.

I’m creating my first Ignition project for real. Everything is going great, only one thing gives me trouble.

I have a table in PostgreSQL database, where I store data (in transaction group) for flow every two minutes. In the table there is a field ‘t_stamp’ (timestamp) and field ‘flow’ (real).
These data will be stored in the table 24/7/year.

The customer wants to have a table (and export to csv) where he can see the average values for flow for 8 hours (for every shift). Shifts are starting at:
1.shift at 06:00
2.shift at 14:00
3.shift at 22:00

So, they want to see the average value for flow for 1. shift (from 06:00 to 13:59), average value for flow for 2. shift (from 14:00 to 21:59) and average value for flow for 3. shift (from 22:00 to 05:59) for every day in the year.

The expected result is then:

Date Shift avg_flow
2013-03-09 1 3.78
2013-03-09 2 2.64
2013-03-09 3 1.76
2013-03-10 1 2.99
2013-03-10 2 4.23
2013-03-10 3 1.12
2013-03-11 1 4.01
2013-03-11 2 3.11
2013-03-11 3 1.45
.
.
.
.
(I’ve made the avg_flow values up)

I’ve tried with my knowledge of SQL, but failed. :frowning:
Obviously, my knowledge is not enough… :slight_smile:

Please, can anybody help me?

I’ve prepared (because I don’t have any real data yet) a sample data (attachment).
test_data.txt (64.7 KB)

2

Hi,

This was surprisingly hard :slight_smile: This post was very helpful. The query uses MySQL functions but PostgreSQL should be similar:SELECT DATE(ADDDATE(DATE(SUBDATE(t_stamp, INTERVAL 6 HOUR)), INTERVAL ((HOUR(SUBDATE(t_stamp, INTERVAL 6 HOUR)) DIV 8) * 8) + 6 HOUR)) AS date, ((HOUR(SUBDATE(t_stamp, INTERVAL 6 HOUR)) DIV 8) + 1) AS shift, AVG(flow) AS avg_flow FROM shift_data GROUP BY ADDDATE(DATE(SUBDATE(t_stamp, INTERVAL 6 HOUR)), INTERVAL ((HOUR(SUBDATE(t_stamp, INTERVAL 6 HOUR)) DIV 8) * 8) + 6 HOUR)

3

Thank you very much. :prayer:

I’m still struggling to convert this query to PostgeSQL…
I’ll let you know, what are the results.

4

Finally… success. :smiley:

Converting MySQL query to PostgreSQL isn’t easy, if you don’t have enough practice in SQL…

This is PostgreSQL query, which is now working (I just added MIN and MAX also):
(I hope it will help someone, because I spend a lot of time on it… :smiley: )

SELECT
	DATE(DATE(t_stamp - INTERVAL '6h')+ ((((EXTRACT(hour FROM (t_stamp - INTERVAL '6h'))::integer /8)*8) + 6) * '1 h'::interval)) AS date,
    ((date_part('hour', (t_stamp - INTERVAL '6h'))::integer / 8)+1) AS shift,
    AVG(flow) as avg_flow,
    MIN(flow) as min_flow,
    MAX(flow) as max_flow
FROM test2
GROUP BY  DATE(DATE(t_stamp - INTERVAL '6h') + ((((EXTRACT(hour FROM (t_stamp - INTERVAL '6h'))::integer /8)*8) + 6) * '1 h'::interval)),
	((date_part('hour', (t_stamp - INTERVAL '6h'))::integer / 8)+1)
ORDER BY date;

and the result is:


Thank you again @AlThePal for the link and your query and for pointing me in right direction.

Regards

5

Well done :thumb_left:

I’m sure summarising by shift will prove really useful to others in the future.