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

[Polido]

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:

Spoiler

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

[Or · Accepted solution]

Floor(YourTimeStamp/86400)

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

[Polido · Accepted solution]

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 🙂