# Average on historical data for production shifts

**URL:** <https://forum.inductiveautomation.com/t/average-on-historical-data-for-production-shifts/6011>\
**Category:** Ignition\
**Created:** [March 11, 2013, 6:38pm UTC](https://forum.inductiveautomation.com/t/average-on-historical-data-for-production-shifts/6011 "2013-03-11T18:38:33Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [March 11, 2013, 6:38pm UTC](https://forum.inductiveautomation.com/t/average-on-historical-data-for-production-shifts/6011/1 "2013-03-11T18:38:33Z")

</div>

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. ☹  
Obviously, my knowledge is not enough… 🙂

Please, can anybody help me?

I’ve prepared (because I don’t have any real data yet) a sample data (attachment).  
[test\_data.txt](https://us1.discourse-cdn.com/inductiveautomation/original/2X/0/096bed423e119290331e96c4d85e2acd3bddb405.txt) (64.7 KB)

---

<div class="post-metadata">

**Author:** ![AlThePal](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/althepal/32/3603_2.png) [@AlThePal](https://forum.inductiveautomation.com/u/AlThePal)\
**Post date:** [March 12, 2013, 12:05pm UTC](https://forum.inductiveautomation.com/t/average-on-historical-data-for-production-shifts/6011/2 "2013-03-12T12:05:59Z")

</div>

Hi,

This was surprisingly hard 🙂[This](http://stackoverflow.com/questions/7087178/group-by-sets-of-values) 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)`

---

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [March 12, 2013, 1:59pm UTC](https://forum.inductiveautomation.com/t/average-on-historical-data-for-production-shifts/6011/3 "2013-03-12T13:59:12Z")

</div>

Thank you very much. ![:prayer:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e5a7e3b63facc151ea73e3b45db7bdb3d4bdc316.gif ":prayer:")

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

---

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [March 12, 2013, 8:38pm UTC](https://forum.inductiveautomation.com/t/average-on-historical-data-for-production-shifts/6011/4 "2013-03-12T20:38:14Z")

</div>

Finally… success. 😃

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… 😃 )

```auto
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:

![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/287b749c8b53f83dd53403b427d15dc54e98057e.png)

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

Regards

---

<div class="post-metadata">

**Author:** ![AlThePal](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/althepal/32/3603_2.png) [@AlThePal](https://forum.inductiveautomation.com/u/AlThePal)\
**Post date:** [March 12, 2013, 8:53pm UTC](https://forum.inductiveautomation.com/t/average-on-historical-data-for-production-shifts/6011/5 "2013-03-12T20:53:44Z")

</div>

Well done ![:thumb_left:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e26afb081ddde6df016df6ee49159cf56aaca688.gif ":thumb\_left:")

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