Grouping data by hour intervals in SQL Server

Asked

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:

inserir a descrição da imagem aqui

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:

inserir a descrição da imagem aqui

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?

  • 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.

  • How the column is declared E3TimeStamp? How does it store the date and time? I found her conversion to datetime very strange.

  • 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.

  • 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.

1 answer

2


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.

  • I tried to implement your solution. I added a print with the feedback I got. However, in doing so the grouping occurred from 00:00:00 to 00:59:59, when the same should be 00:00:00 to 01:00:00

  • If the E3timestamp column is declared as datetime, then there is no need for that CAST sequence.

  • @Agnaldojunior I added code #2

  • That’s right. Thank you!

Browser other questions tagged

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