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
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
How the column is declared
E3TimeStamp
? How does it store the date and time? I found her conversion to datetime very strange.– José Diz
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
@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