Grouping data by hour intervals in SQL Server

Question

Grouping data by hour intervals in SQL Server

Asked 5 years, 3 months ago

Viewed 981 times

0

I currently have the following query in the SQL Server database:

SELECT TOP 5 
CAST(CAST(CAST(CAST(E3TimeStamp as float)*24 as bigint) as float)/24 as datetime) as hora,
CAST(CAST(CAST(CAST(E3TimeStamp as float)*24 as bigint) as float)/24 as datetime)+0.04167 as hora_final,

AVG(TOTALIZADO_FIT300) as campo1,
AVG(TOTALIZADO_FIT100eFIT300) as campo2

FROM FATURAMENTO

GROUP BY CAST(CAST(CAST(CAST(E3TimeStamp as float)*24 as bigint) as float)/24 as datetime)
ORDER BY Hora ASC

Outcome of the consultation:

My problem is that with this grouping (GROUP BY), the query returns me the values of Time in Time, example:

Average values between 2019-02-01 00:00:00 á 2019-02-01 00:59:59

I need the grouping to occur for example between the interval:

Average values between 2019-02-01 00:00:00 á 2019-02-01 01:00:00

And so on and so forth.

Complement:

As it can be noted, for the reason that the grouping does not occur from 00:00 to 01:00 and yes from 00:00:00 to 00:59:59, with this, I have discrepancy of values.

Do you mean that you want the upper limit of your range to be also considered as the lower limit of the next range? If there is an occurrence right now it will not be considered in duplicate? Have you ever thought of truncating your timestamp field for time and aggregating by it?

– anonimo

2019/08/27 at 21:15
Exactly. Because the way I’m doing now is that there are problems with total values. This application saves data every 1 second in the database with the factory output value. I thought to do this, truncate the field for hour. I will do this test and put the result.

– Agnaldo Junior

2019/08/27 at 23:17
How the column is declared E3TimeStamp? How does it store the date and time? I found her conversion to datetime very strange.

– José Diz

2019/08/28 at 10:38
I added content to the problem description. I made the merge as commented above. @Josédiz this E3timestamp is a datetime and I use it as a Primary Key as well.

– Agnaldo Junior

2019/08/28 at 19:22
1

@Agnaldo: I insist again that if you consider the upper limit of an interval being exactly equal to the lower limit of the next interval (and not strictly lower than what seems correct to me) and you have an event right now you will consider the value associated with this instant in both ranges, which does not seem to me logically consistent, but is what you have been describing in your comments.

– anonimo

2019/08/28 at 19:47

1 answer

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.

by José Diz • **6,942** points · Answer 1 · 2019-08-28T10:27:32+00:00

Here is a suggestion to return the averages per hour:

-- código #1 v3
SELECT (convert (char(13), E3TimeStamp, 121) + ':00') as hora, 
       avg (TOTALIZADO_FIT300) as campo1,
       avg (TOTALIZADO_FIT100eFIT300) as campo2
  from FATURAMENTO_2
  group by convert (char(13), E3TimeStamp, 121);

If the lines whose time value is hh:00:00 must be in both the current and previous tracks, here is another suggestion:

-- código #2
with agFAT as (
SELECT E3TimeStamp,
       dateadd (hour, datediff (hour, 0, E3TimeStamp), 0) as Hora,
       TOTALIZADO_FIT300, TOTALIZADO_FIT100eFIT300
  from FATURAMENTO
  -- where ...

union all

-- obtém linhas que são hh:00.00
SELECT dateadd (second, -1, E3TimeStamp), 
       dateadd (hour, datediff (hour, 0, dateadd (second, -1, E3TimeStamp)), 0),
       TOTALIZADO_FIT300, TOTALIZADO_FIT100eFIT300
  from FATURAMENTO
  where (datediff_big (second, 0, E3TimeStamp) % 3600) = 0
        -- and ...
)
SELECT Hora,
       avg (TOTALIZADO_FIT300) as campo1,
       avg (TOTALIZADO_FIT100eFIT300) as campo2
  from agFAT
  group by Hora;

I have not tested; may contain error(s).

Lines whose time value is hh:00:00 are doubled, but with one second less. This causes them to also participate in the previous track.

Lines with value hh:00:00 are those whose value converted to seconds are multiples of 3,600.