timestamp format, rounding, and aggregation

[#1]

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 solution]

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.

[#3]

Thanks!

[#4]

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

[#5]

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?

[#6 · Accepted solution]

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

[#7]

Tremendous guidance, works perfectly