Round to nearest second and grouping by DateTime field isn't working

Polido — Thu, 19 Sep 2024 12:07:02 GMT

Hello,

I've searched for similar problems and tried everything but this seems to not be working.

I need to round my data to nearest second and grouping by DateTime field.

I receive a list of Signals and their values and after I put them into columns and group them by every second of data to have data from every signal in one line.

I extracted the first 2 steps of my code with 3 signals:

table1:
LOAD
SignalID,
timestamp(Floor(SignalTimestamp_UTC,1/86400000), 'YYYY-MM-DD hh:mm:ss') as DateTime,
IF(SignalID = '5aa29df4-7e022ba55784', SignalValueFloat) as Latitude,
IF(SignalID = '7b963165-76a2fbb67ead', SignalValueFloat) as Longi,
IF(SignalID = 'd5513705-980546b31115', SignalValueFloat) as TempOel01,
SignalValueFloat
FROM [lib://Files DEV-V3/Step1-ETL-E-output/raw_localtime_WW_2024-09-W3.QVD]
(qvd);

table2:
Load
DateTime,
maxstring(Latitude) as Latitude,
maxstring(Longi) as Longi,
maxstring(TempOel01) as TempOel01
resident table1
group by DateTime;

drop table table1;

exit script;

The results are in the image attached.

Thank you very much in advance

Re: Round to nearest second and grouping by DateTime field isn't working

Or — Thu, 19 Sep 2024 11:22:27 GMT

Floor(YourTimeStamp/86400)

You seem to have had the right idea, but you were rounding to the nearest millisecond.

Re: Round to nearest second and grouping by DateTime field isn't working

Polido — Thu, 19 Sep 2024 12:03:08 GMT

Hi Or, thank you very much for the fast reply.

I changed to: timestamp(Floor(SignalTimestamp_UTC,1/86400), 'YYYY-MM-DD hh:mm:ss') as DateTime,

and it works beautifully!

Thanks again, you're a life saver 🙂

topic Re: Round to nearest second and grouping by DateTime field isn't working in App Development

Round to nearest second and grouping by DateTime field isn't working

Re: Round to nearest second and grouping by DateTime field isn't working

Re: Round to nearest second and grouping by DateTime field isn't working