Using dates, times, datetimes and durations in JMP

Jeff_Perkinson · Sep 4, 2018 09:19 AM

There are four common time-based values that you might have to deal with in JMP: dates, times, datetimes and durations. Whether you are importing data from a database or flatfile, whether you are referring to them in JSL or using them on an axis in JMP, it's important to understand how JMP stores these values and the implications for using them.

Datetime values

Datetime values represent a specific time on a specific date, for example:

12:00 PM, January 20, 2016
07Nov1918 3:00PM
07/20/1969 20:17

As in most analytic applications, datetime values are stored in JMP as a number of time units since a reference time. In JMP, the reference time is 12:00 AM, January 1, 1904, and the units are seconds. To say that more plainly: JMP stores dates as the number of seconds since midnight, January 1, 1904.

So, the numeric values corresponding to the datetime values in my examples above are:

Datetime	JMP datetime value (seconds since midnight, 01Jan1904)
12:00 PM, January 20, 2018	3536136000
07Nov1918 3:00PM	468687600
07/20/1969 20:17	2068661820

Dates

In JMP, date values are just a special case of datetimes in that they assume the time of day is 12:00 AM. So, they simply count the number of seconds from midnight, January 1, 1904, to midnight on the date being stored.

Times

Time values represent a time of day without a specific date. JMP stores time values as the number seconds into the day starting at midnight. So, 12:01:01 AM is 61. It is 61 seconds since midnight.

Durations

Durations are not really all that special, except that they are numbers representing a quantity of some time unit. Not surprisingly, JMP can deal with durations best when they represent a number of seconds. We'll talk more about this when we talk about formatting time-based values for display.

Formatting time-based values for display

Displaying time-based values as a number of seconds isn't particularly useful, so JMP has a number of date formats and time formats for displaying these values.

Remember though, changing the way a date is displayed doesn't change the underlying value or how it's analyzed. For example, changing the format to "m/y" (i.e., month/year) won't force JMP to summarize your data by month when you analyze or graph it. If you want to do that, you'll need to create a new column that contains the unique values you want to summarize to. To create these new columns, transform the column by right-clicking on the header and choosing New Formula column.

Importing time-based values

When importing data, it is important to ensure that your time-based values are stored as the numeric values described above. Most of the time, JMP will do this automatically as it tries to recognize time-based values during import or copy/paste operations. You can tell if this has been done correctly by looking at which way the values in the column are justified in the data table. Numeric values are right-justified, and character values are left-justfied.

Wrong is a character column. Notice it is left-justified. Right is numeric column. It is right-justified.

If you find that you have time-based values that are stored as character strings, you can convert them using the Column Info dialog box. Just change the Data Type to numeric, the Format to your desired date/time format, and the Input Format to the one that describes the character strings that are in your data. JMP will then convert the values to the appropriate numeric values.

First change the Data Type to Numeric.

Pick the Format that you want to use to display the time-based value.

Finally, change the Input format to match the format of the strings in the column.

Parse Date() a useful function

The Parse Date() (and its alias, Informat()) function is also useful for converting character strings to numeric values. When used without the optional third argument, it will scan the input string and try to find the appropriate input format. This makes it an all-purpose function, regardless of input format.

Computations (i.e., math) using time-based values

Storing all these time-based values as numbers means we can do math on them to compute age, time since some event, convert to other time units, etc.

For example, the difference between two dates, times or datetime values in JMP will be the number of seconds between the two values.

This means that you will need to convert the result into more friendly units like minutes, days, weeks or years (Hint: The In Hours(), In Weeks() and other In...() functions can help here.)

JMP has a number of built-in functions to make computations on time-based values easier.

Here are a couple of my favorites:

Time of Day(datetime)

This function returns the time of day (i.e., the number of seconds since midnight) of a datetime value. This is very useful for converting a datetime value to a date value. You just need to subtract the Time of Day() from the datetime:

datetime = Today();
date = datetime - Time Of Day( datetime );
Show( As Date( date ) );

Date Difference(datetime1, datetime2, "interval_name", <"alignment">)

This function returns the difference between two datetime values. Of course, you can do this just by subtracting one from another, and you'll get the number of seconds between them. But Date Difference() enables you to specify the time units to return the difference in using the "interval_name" argument. For example:

datetime1 = 01Sep2018:03:00:01;
datetime2 = 04Sep2018:14:10:15;

Date Difference( datetime1, datetime2, "Hour" );

Be sure to look at the entire list of Date Time functions. They make working with time-based values much easier.

Date and Time Constants in JSL

Representing date, datetime and time contants in JSL requires you to specify them in a specific format. For dates, you must specify them in ddMONyyyy format, and for times, you must use dd:hh:mm:ss format. For example:

time = 14:10:15;
date = 18Jan1957;
datetime = 11Jul2000:03:10:12;