cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
kray
Level I

timestamp format, rounding, and aggregation

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
gflex
Level III

Re: timestamp format, rounding, and aggregation

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.

View solution in original post

txnelson
Super User

Re: timestamp format, rounding, and aggregation

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

Jim

View solution in original post

6 REPLIES 6
gflex
Level III

Re: timestamp format, rounding, and aggregation

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.

UersK
Level III

Re: timestamp format, rounding, and aggregation

Thanks!

michaelhaslam_p
Level III

Re: timestamp format, rounding, and aggregation

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

KellyK
Level II

Re: timestamp format, rounding, and aggregation

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?
txnelson
Super User

Re: timestamp format, rounding, and aggregation

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

Jim
david_fuchs
Level I

Re: timestamp format, rounding, and aggregation

Tremendous guidance, works perfectly