topic Re: How to aggregate data by hour in time series? in Discussions

How to aggregate data by hour in time series?

jhonnmarie — Fri, 25 Jul 2025 20:01:40 GMT

Hi JMP Community,

I’m working with a time series dataset that includes timestamps at the minute level (e.g., 2025-07-12 14:03:00). I’m trying to aggregate the data into hourly bins so I can visualize trends over the course of the day.

I’ve tried using the Group function with Hour(DateTime), but I’m not sure I’m applying it correctly in a summary table or graph. Also wondering if there's a cleaner way to round timestamps to the nearest hour before aggregation?

Any tips or scripting examples for hourly grouping would be really helpful.

Thanks!
Jhonn Marie

Re: How to aggregate data by hour in time series?

jthi — Fri, 25 Jul 2025 20:15:17 GMT

I would create new column which removes minutes and seconds from your date time column. You can create it with quick formulas JMP provides you with but it will require few of them. First you can create Date column

Then create Hour column

And combine these

Next open the created column, change format and modify the formula by multiplying the hour by In Hours(1) (same as 60*60 which is seconds in hours, this is basically how JMP manages time). Final formula would look something like this

In Hours(:"Hour[Column 1]"n) + :"Date[Column 1]"n

You can of course do this all in single column by picking the formulas from Hour (remember to convert these into hours) and Date columns and combining them. The final formula would look something like this (not the only option):

:Column 1 - Time Of Day(:Column 1) + In Hours(Hour(:Column 1))

where :Column 1 is the date time column you have

You could also just deduct seconds and minutes (remember to convert these into seconds) from your date time and you should have similar result

:Column 1 - Second(:Column 1) - Minute(:Column 1)*60

One place to read more about date-time in JMP Using JMP > Reference for JMP Functions in Formulas > Date Time Functions

Re: How to aggregate data by hour in time series?

markschahl — Sun, 27 Jul 2025 03:40:36 GMT

JM:

I work a lot with sensor timeseries data from process data historians.

Are your data evenly spaced, i.e. one minute average or snapshot, or have timestamps that could be from any time within the hour? Do you want to aggregate the data to an hourly average or just round the timestamps to the nearest hour?

In either case, the easiest way is to: rightclick on the timestamp column > New Formula Column > Transform > Round > Nearest 1 hour

Once you've created the rounded timestamp, to create the hourly average: Tables > Summary > Group: Rounded Timestamp; add the data columns with the statistics you want <Table Summary>

Happy to discuss this further.

Re: How to aggregate data by hour in time series?

hogi — Sun, 27 Jul 2025 07:14:55 GMT

@markschahl
very comfortable!!

nice : )

Re: How to aggregate data by hour in time series?

hogi — Sun, 27 Jul 2025 12:04:36 GMT

Ah, Date Increment() does the job in the background.
The same function does the binning in GraphBuilder - so helpful!

If you're wondering about axes which span all the way to the year 0:
Unfortunately, the Date Increment() function does not treat missing values as such; they are rounded to 0.

(view in My Videos)

& TS-00147884.

Re: How to aggregate data by hour in time series?

hogi — Sun, 27 Jul 2025 09:15:05 GMT

As a workaround, one can exclude the wrong rows,

change the formula to

tmp = Date Increment( :"date-time"n, "Hour", 0 );
If( tmp == -60084288000,.,tmp); // "0" = -60084288000

... or use -60084288000 as Missing Value Code via the column properties:

Re: How to aggregate data by hour in time series?

hogi — Sun, 27 Jul 2025 12:28:42 GMT

I just wondered about:

with date & time rounded to minutes, there is no issue with missing.

the explanation:

...no Date Increment()

So, another approach to round to hours:

: )