cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Check out the JMP® Marketplace featured Capability Explorer add-in
Choose Language Hide Translation Bar
user8421
Level II

Combining Date Column and Time Column to Create Timestamp

For a data table with a date column and a time column, I create a new column that uses the formula Date + Time to create a timestamp.  However the formula seems to work inconsistently where some of the timestamps result in being 4 YEARS previous than what they should be.

DateTimeTimestamp (formula)
2021 - 10 - 0112:05 AM10/01/2021 12:05 AM
2021 - 10 - 236:59 PM10/23/2017 6:59 PM
2021 - 11 - 077:45 PM11/07/2017 7:45 PM

 

 

Is there a better way to generate a timestamp that avoids whatever causes this issue?

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Combining Date Column and Time Column to Create Timestamp

Assuming that your Date column and your Time column are JMP date/time values, my suggestion is that you need to change the display format for your 2 columns to display both date and time to see if the Time column contains date values  In this example, Column 6 and Column 7 display the same time values, 

txnelson_0-1645062309651.png

however, when one examines the actual numeric values, they are completely different.

txnelson_1-1645062620851.png

The Column 6 actually contains the date component , so what I am guessing is that in your time value, there are additional date related values

txnelson_2-1645062823276.png

 

 

 

Jim

View solution in original post

3 REPLIES 3
txnelson
Super User

Re: Combining Date Column and Time Column to Create Timestamp

Assuming that your Date column and your Time column are JMP date/time values, my suggestion is that you need to change the display format for your 2 columns to display both date and time to see if the Time column contains date values  In this example, Column 6 and Column 7 display the same time values, 

txnelson_0-1645062309651.png

however, when one examines the actual numeric values, they are completely different.

txnelson_1-1645062620851.png

The Column 6 actually contains the date component , so what I am guessing is that in your time value, there are additional date related values

txnelson_2-1645062823276.png

 

 

 

Jim
user8421
Level II

Re: Combining Date Column and Time Column to Create Timestamp

Thanks for the explanation.  When I changed the format of the Time column it showed different dates with one being the 01/01/1994 and the other 12/31/1899.  The raw data that is pulled into JMP lists the time only, so I am unclear how the date is "assigned" to the times.  How can I address the discrepancy, so that the dates are consistent in the time columns?

txnelson
Super User

Re: Combining Date Column and Time Column to Create Timestamp

I would run the 2 scripts below to make sure the Date values only point to the start of the day you are specify.  Just up to the start of the day 00:00:00.

Names Default To Here( 1 );
dt = Current Data Table();
For Each Row(
	:date = Date MDY(
		Month( :date ),
		Day( :date ),
		Year( :date )
	)
);

And with a slightly different approach, fix the :Time column

Names Default To Here( 1 );
dt = Current Data Table();
For Each Row(
	:time = Hour( :time ) * 3600 
	+ Minute( :time ) * 60
	+ Second( :time )
);

These 2 scripts will insure that Date has no times in the data, and the second one has no dates in :Time

Jim