topic Re: Combining Date Column and Time Column to Create Timestamp in Discussions

Combining Date Column and Time Column to Create Timestamp

user8421 — Sat, 10 Jun 2023 23:44:23 GMT

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.

Date	Time	Timestamp (formula)
2021 - 10 - 01	12:05 AM	10/01/2021 12:05 AM
2021 - 10 - 23	6:59 PM	10/23/2017 6:59 PM
2021 - 11 - 07	7:45 PM	11/07/2017 7:45 PM

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

Re: Combining Date Column and Time Column to Create Timestamp

txnelson — Thu, 17 Feb 2022 01:53:59 GMT

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,

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

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

Re: Combining Date Column and Time Column to Create Timestamp

user8421 — Thu, 17 Feb 2022 19:55:09 GMT

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?

Re: Combining Date Column and Time Column to Create Timestamp

txnelson — Thu, 17 Feb 2022 20:16:42 GMT

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