topic How to subtract a fixed duration from Date Time column? in Discussions

How to subtract a fixed duration from Date Time column?

Neo — Tue, 09 Jan 2024 17:01:52 GMT

I have a data table with entryDate as one of the columns. It is Numeric & Continuous type and has row entries in the following format

07/07/2023 08:20:21

I want to subtract the following an offset from it and get newEntryDate in a new column.

184 days, 14 hours, 14 minutes and 43 seconds
(or 6 months, 2 days, 14 hours, 14 minutes, 43 seconds)

How to do this in JMP/JSL?

Re: How to subtract a fixed duration from Date Time column?

jthi — Tue, 09 Jan 2024 17:25:54 GMT

Convert your time to seconds and then you can deduct it

:entryDate - (In Days(184) + In hours(14) + In Minutes(14) + 43)

Re: How to subtract a fixed duration from Date Time column?

Neo — Tue, 09 Jan 2024 22:26:48 GMT

@jthi Thanks. I almost managed it but did not realize that 43 could be subtracted directly I was looking for a In Seconds () function.

While I am on this topic, how do I get the difference/duration in seconds between :entryDate and :newEntryDate ?

Re: How to subtract a fixed duration from Date Time column?

Jeff_Perkinson — Tue, 09 Jan 2024 22:34:38 GMT

Dates, times, and datetimes are stored as a number of seconds. So to get the difference between two dates in seconds just subtract one from the other.

If you want the difference in other units you can use the Date Difference() function.

Re: How to subtract a fixed duration from Date Time column?

Neo — Tue, 09 Jan 2024 23:07:09 GMT

@Jeff_Perkinson . OK thanks. How do I convert the date in the format I have in seconds?

Re: How to subtract a fixed duration from Date Time column?

pmroz — Wed, 10 Jan 2024 02:46:31 GMT

>>It is Numeric & Continuous type

If your date column is numeric/continuous as you have specified, then it is already in seconds, behind the scenes as it were. You must have specified a column format of m/d/y h:m:s. If you want to display seconds, then use a numerical format.

Here's a table where the first column uses the date format, and the second one uses numeric format:

New Table( "Test Date", Add Rows( 2 ),
	New Column( "Date Format", Numeric, "Continuous",
		Format( "m/d/y h:m:s", 23, 0 ),
		Input Format( "m/d/y h:m:s", 0 ),
		Set Values( [3771562821, 3771562821] )
	),
	New Column( "Date in Seconds", Numeric, "Continuous", Format( "Best", 12 ),
		Formula( :Date Format )
	)
);