Aggregate time stamp data on hourly basis

[Former Member]

Hello Everyone,

I have following table:

The table stores temperature multiple times in a minute and the timestamp field holds the timestamp.

Timestamp                              Temperature

2010-01-11 1:00:00.0                    30

2010-01-11 1:00:01.0                    35

2010-01-11 1:00:02.0                    29

2010-01-11 1:00:04.0                    31

2010-01-11 1:00:07.0                    32

-------------------                                --------

2010-01-11 2:00:00.0                    31

2010-01-11 2:00:01.0                    34

2010-01-11 2:00:02.0                    37

Now I want to show average temperature on hourly basis in my report.

Date                    Temperature

2010-01-11 1          32

2010-01-11 2          34

2010-01-11 3          31

How to proceed with this.

I tried creating a calculated column as leftstr(timestamp,11) and got the aggregated data but I am having issues calculating the AVG as timestamp data is not stored every second but multiple times in a minute. I tried using Counter but still am not able to get the right output.

Regards,

Rohit

[Former Member · Accepted solution]

Hi Rohit,

I have tried to execute your scenario and achieved the same with Average Aggregation function(Available on HANA SP11) on Temperature as shown below.

Table "TEMPERATURE"

But what do you mean by 'data is not stored every second but multiple times in a minute'? Can you give sample data for that?

Regards,

Shireesha