topic Re: timestamp format, rounding, and aggregation in Discussions

timestamp format, rounding, and aggregation

kray — Thu, 05 Sep 2013 16:21:30 GMT

I have been given a data table that contains a couple of columns that require some formatting changes. Specifically I need to:

1. alter the "Date/Time" column format from 16/08/2013 2:32.01 PM, to 2013-08-13 14:32:01 (this does not appear to be one of JMPs predefined formats)

2. round the timestamp to the nearest 15 min interval. In this case 2013-08-13 14:32:01 would become 2013-08-13 14:30:00

Finally, does anyone know of a way to aggregate timestamp data (or any other datatype for that matter)? In my case I have a table of data collected every 10 minutes, that needs to be aggregated to 15 minute bins.

Re: timestamp format, rounding, and aggregation

gflex — Tue, 24 Sep 2013 12:55:05 GMT

1 I don't have suggestions to change formatting options on a numeric column beyond the predefined formats

(one could kludge a concatenated text solution with date time functions year, month, day, hour .... for cosmetic purposes only)

2 Round can work if you transform the units first.

JMP time units are seconds If you divide this by 60, the resulting number is in minutes, divide by 15 to change the units to 15's of minutes, then round, then change units back to seconds. Formula would look like Round(:Column/(60*15)) * (60*15)

Once the rounded time is known, a table summarize could potentially group similar time blocks together. This may meet your goals for aggregation of numeric values.

PS- instead of the "round" option, consider the "floor"- time groupings are often labelled based on the earliest time in the group, instead of the average.

Re: timestamp format, rounding, and aggregation

michaelhaslam_p — Tue, 24 Sep 2013 13:22:04 GMT

kray:

Some of the answer here depends on what you are trying to accomplish.

JMP has a date/time format that is very close: 2013-08-16T14:32.

Are you trying to process date/time data and then you want the display it in a graph in your format, perhaps the X-Axis? In that case, you have to use a JMP internal date/time format.

Are you planning on making your own summary reports? In that case, process the data in the native date/time format and then convert it to character data for display in your preferred format. You can pull out the components of JMP date and times using various built-in functions; Month(), Year(), etc.

Michael Haslam

Re: timestamp format, rounding, and aggregation

KellyK — Tue, 07 Feb 2017 21:09:43 GMT

I know you posted this a few years ago, but I am trying to do the same thing now. Did you ever figure out how to do this?

Re: timestamp format, rounding, and aggregation

txnelson — Tue, 07 Feb 2017 21:30:49 GMT

If you can not use the built in formula as @MichaelHaslam suggested, then following @gflex solution is what needed to be done.

Take you current timestamp

Create a new column and apply the Round(:Column/(60*15)) * (60*15) to convert it to 15 minute incremental data

Then finally, create a character column of the format specifications you need, using the various day,month,year,hour,minute etc. functions to create the final required character string

Re: timestamp format, rounding, and aggregation

david_fuchs — Tue, 10 Dec 2019 23:55:40 GMT

Tremendous guidance, works perfectly

Re: timestamp format, rounding, and aggregation

UersK — Wed, 08 Feb 2023 05:28:54 GMT

Thanks!