Round datetime value to date and hourly bucket

[LeeSpears]

My original data contains a 'time' field with epoch timestamp values such as:
1674428490776

I've already used scripting to convert these into human readable timestamps in the 'toa' field as follows:
date((time/24/60/60/1000) + 25569,'YYYY-MM-DD HH:mm:SS') as toa

And this gives values such as:
2023-01-23 12:06:40

What I want is to round down the time to the nearest hour in the 'toa' field so that I get values such as:
2023-01-23 12:00
(in 24-hour format)

The point of this is that I want to create a time series chart showing the number of widgets produced per hour over my desired length of time, which may be hours, days, weeks, etc.

Thank you!

[SerhanKaraer · Accepted solution]

Hello Lee,

Try this:

date(floor((time/24/60/60/1000) + 25569,1/24),'YYYY-MM-DD HH:mm')