Add a Period to Date & Time

1

I’m struggling to find some Clarion code that would take a date & time as input, ADD a certain period, and then provide the END DATE & TIME as output.

Closest I could find is fsdates at IceTips. but it seems not to do this specific thing.

So I’m building a little procedure/function with this prototype:
CalculateEndDateAndTime procedure(LONG pStartDate,LONG pStartTime,LONG pPeriod,STRING TypeOfPeriod,*LONG pEndDate,*LONG pEndTime)
! pPeriod must be S (seconds), M (minutes), H (hours), D (days) or W (weeks).

Simple example:

When lStartDate is 15/01/2024 (deformatted LONG)
and lStartTime is 10:15:20 (deformatted LONG)

then
CalculateEndDateAndTime(lStartDate,lStartTime,70,'M',lEndDate,lEndTime)
must result in
lEndDate = 15/01/2024 (deformatted LONG)
lEndTime = 11:25:20 (deformatted LONG)

Any ideas/pointers on an algorithm to use?

Or is this type of thing available somewhere? Perhaps StringTheory? Perhaps Prodomus?

2

The following should get you started…

Subject: Re: Time Duration
From: "Ole Morten Heien <\"omheien at advisor dot no\">"
Date: Mon, May 4 2020 8:31 pm

Lots of ways to do this.
One way is to use TICKS.  There are 8640000 ticks in one day, meaning 
you can use some simple math to calculate this
You can use DECIMAL datatype, or BSTRING or whatever you like that can 
hold a the value of a 64bit INTEGER.  Hers an example using BSTRING

strtDate	DATE
strtTime	TIME
endDate 	DATE
endTime		TIME
startTics	BSTRING
endTicks	BSTRING
passedTicks	BSTRING
passedDate	DATE
passedTime	TIME
   CODE
   startTicks = strtDate * 8640000 + strtTime
   endTicks = endDate * 8640000 + endTicks
   passedTicks = endTicks - strtTicks
   passedDate = int(passedTicks / 8640000)
   passedTime = passedTicks % 8640000

A nice function :
dtGroup   GROUP, TYPE ! Or any group that has this struct
_date		DATE
_time		TIME
	END

CalculateTimeSpan  PROCEDRE(dtGroup startDate, dtGroup endDate)!, STRING
passedTicks		BSTRING
_ret			LIKE(dtGroup)
   CODE
   passedTicks = (endDate._date * 8640000 + endDate._time)-|
                 (startDate._Date * 8640000 + startDate._Time)
   _ret._date = int(passedTicks / 8640000)
   _ret._time = passedTicks % 8640000
   return _ret
3

There are quite a few ways to do this.

For example, have a look at stardate code mentioned recently near the end of this thread:

4

another thought - try this - off the top of my head and NOT tested.

using the time equates:

TIME:Second                   EQUATE(100)
TIME:Minute                   EQUATE(60*TIME:Second)
TIME:Hour                     EQUATE(60*TIME:Minute)
TIME:Day                      EQUATE(24*TIME:Hour)

CalculateEndDateAndTime procedure(LONG pStartDate,LONG pStartTime,LONG pPeriod,STRING TypeOfPeriod,*LONG pEndDate,*LONG pEndTime)
! pTypeOfPeriod must be S (seconds), M (minutes), H (hours), D (days) or W (weeks).
timeIncrement REAL
  code
  pEndDate = pStartDate

  case pTypeOfPeriod
  of 'W'
    pEndDate += (pPeriod * 7)
  of 'D'
    pEndDate += pPeriod
  of 'H'
    timeIncrement = pPeriod * TIME:Hour
  of 'M'
    timeIncrement = pPeriod * TIME:Minute
  of 'S'
    timeIncrement = pPeriod * TIME:Second
  end   

  if timeIncrement
    timeIncrement += pStartTime
    pEndDate += timeIncrement / Time:Day
    pEndTime  = timeIncrement % Time:Day
  else
    pEndTime = pStartTime
  end
1 Like
5

Read this recent post “How to Calculate the Time Lapsed Between Two Dates & Time” for some code dealing with Date / Time math. oops I see Geoff posted that.

You may also want to look at my Date Time Tool on GitHub. It only does Date calcs not Date & Time.

One question is do you allow your “Period” to be negative? I think you should. In that case the code would need to be careful to handle, the main issue probably being time as subtracting Days is simple.

Note in Geoff’s code he used a REAL for TimeIncrement. That’s good because using a LONG could be a problem since it is limited to 2.147 billion = 248 Hours = 10.3 days.

REAL’s make me uneasy so I would use a DECIMAL(15) so the BCD library does the math perfectly. I calc 12 digits enough for 10 years of hours 1036524*8640000.

6

Yeah, thanks.

I’ve seen all that…

7

Just checking, but the assumption seems to be Topspeed files and not SQL. Yes?

8

Think that needs to deal with Clarion TIME being “+1” so …

  if timeIncrement
    timeIncrement += (pStartTime - 1)      !<== Less -1 from Time = Duration
    pEndDate += timeIncrement / Time:Day
    pEndTime  = (timeIncrement % Time:Day) + 1    !<== Add +1 for Time
  else
    pEndTime = pStartTime
  end

Need to check that works right if TimeIncrement < 0.
I would also verify exactly one Day of Time change worked to affect Date and not Time so +/- 24 hours, 24 * 60 minutes, 24 * 60 *60 seconds.

The CASE OF line can be multi-statement which I prefer to align the code in columns out to the right:

  case UPPER(pTypeOfPeriod)
  of 'W' ; pEndDate += (pPeriod * 7)
  of 'D' ; pEndDate += pPeriod
  of 'H' ; timeIncrement = pPeriod * TIME:Hour
  of 'M' ; timeIncrement = pPeriod * TIME:Minute
  of 'S' ; timeIncrement = pPeriod * TIME:Second
  else   ; DebugOut('Unknown Period '& pTypeOfPeriod)
  end

If you decide to add a Month adjustment be aware you cannot pass Negative Months to the DATE() function. Instead subtract Years and Add Months. This is covered in my Date Tool on GutHub.

9

Thanks Carl

I have added your suggestions/corrections and this time even ran it past the compiler and did a few tests and this new version seems to work fine (so far).

I got rid of the modulus (%) as basically you cannot trust it if there is a chance of negative numbers.

eg. we all know 100%3 = 1 but did you know that in Clarion, -100%3 = 2 ?

There is a compatible_modulus #pragma but you cannot rely on someone having that set on or off and besides, I suspect it just substitutes one “wrong” answer for another!

So safer to calculate it yourself - so I have done that now and so this should be safe for negative “periods”.

I share Carl’s unease about REALs in certain circumstances, but here where it is used only for integers (no fractions) it should be accurate and give the same result as Decimal(15), but REAL should be a bit quicker.

And talking of ‘quicker’ I wrapped the case variables in val() for a very minor performance boost.

anyway without further ado:

CalculateEndDateAndTime PROCEDURE  (LONG pStartDate,LONG pStartTime,LONG pPeriod,STRING pTypeOfPeriod,*LONG pEndDate,*LONG pEndTime) ! Declare Procedure
! pTypeOfPeriod must be S (seconds), M (minutes), H (hours), D (days) or W (weeks).

timeIncrement REAL
daysIncrement LONG,auto

  CODE
  pEndDate = pStartDate

  case val(upper(pTypeOfPeriod))
  of val('W') ; pEndDate += pPeriod * 7
  of val('D') ; pEndDate += pPeriod
  of val('H') ; timeIncrement = pPeriod * TIME:Hour
  of val('M') ; timeIncrement = pPeriod * TIME:Minute
  of val('S') ; timeIncrement = pPeriod * TIME:Second
! else   ; DebugOut('Unknown Period '& pTypeOfPeriod)
  end   

  if timeIncrement
    timeIncrement += pStartTime - 1
    daysIncrement  = timeIncrement / TIME:Day
    if daysIncrement
      pEndDate      += daysIncrement
      timeIncrement -= daysIncrement * TIME:Day
    end
    pEndTime = timeIncrement + 1
    if pEndTime < 1
      pEndTime += TIME:Day
      pEndDate -= 1
    end 
  else
    pEndTime = pStartTime
  end

edit: I mentioned I did a few basic tests. I will include 20 tests here so that if you change anything or do a different implementation you can run these as a check. The tests are in pairs basically subtracting an amount then adding it back - so you should get back to the original value. These tests are certainly not comprehensive so it would be good to add extras and add them here in a comment in this thread.

  CalculateEndDateAndTime(deformat('20240101',@d12), 1, -1, 'D', endDate, endTime)
  if format(endDate,@d12) <> '20231231' or endTime <> 1 then stop('failed test 1'). 
  CalculateEndDateAndTime(endDate, endTime, 1, 'D', endDate, endTime)
  if format(endDate,@d12) <> '20240101' or endTime <> 1 then stop('failed test 2'). 
                                                                                    
  CalculateEndDateAndTime(deformat('20240101',@d12), 1, -24, 'H', endDate, endTime)
  if format(endDate,@d12) <> '20231231' or endTime <> 1 then stop('failed test 3: ' & format(endDate,@d12) & ' time: ' & endTime). 
  CalculateEndDateAndTime(endDate, endTime, 24, 'H', endDate, endTime)
  if format(endDate,@d12) <> '20240101' or endTime <> 1 then stop('failed test 4: ' & format(endDate,@d12) & ' time: ' & endTime). 
  
  CalculateEndDateAndTime(deformat('20240101',@d12), 1, -(24*60), 'M', endDate, endTime)
  if format(endDate,@d12) <> '20231231' or endTime <> 1 then stop('failed test 5: ' & format(endDate,@d12) & ' time: ' & endTime). 
  CalculateEndDateAndTime(endDate, endTime, 24*60, 'M', endDate, endTime)
  if format(endDate,@d12) <> '20240101' or endTime <> 1 then stop('failed test 6: ' & format(endDate,@d12) & ' time: ' & endTime). 
  
  CalculateEndDateAndTime(deformat('20240101',@d12), 1, -(24*60*60), 'S', endDate, endTime)
  if format(endDate,@d12) <> '20231231' or endTime <> 1 then stop('failed test 7: ' & format(endDate,@d12) & ' time: ' & endTime). 
  CalculateEndDateAndTime(endDate, endTime, 24*60*60, 'S', endDate, endTime)
  if format(endDate,@d12) <> '20240101' or endTime <> 1 then stop('failed test 8: ' & format(endDate,@d12) & ' time: ' & endTime). 
  
  CalculateEndDateAndTime(deformat('20240101',@d12), 1, -36, 'H', endDate, endTime)
  if format(endDate,@d12) <> '20231230' or endTime <> 1 + TIME:Day/2 then stop('failed test 9: ' & format(endDate,@d12) & ' time: ' & endTime). 
  CalculateEndDateAndTime(endDate, endTime, 36, 'H', endDate, endTime)
  if format(endDate,@d12) <> '20240101' or endTime <> 1 then stop('failed test 10: ' & format(endDate,@d12) & ' time: ' & endTime). 
  
  CalculateEndDateAndTime(deformat('20240101',@d12), 1, -84, 'H', endDate, endTime)
  if format(endDate,@d12) <> '20231228' or endTime <> 1 + TIME:Day/2 then stop('failed test 11: ' & format(endDate,@d12) & ' time: ' & endTime & ' expected: ' & 1 + TIME:Day/2). 
  CalculateEndDateAndTime(endDate, endTime, 84, 'H', endDate, endTime)
  if format(endDate,@d12) <> '20240101' or endTime <> 1 then stop('failed test 12: ' & format(endDate,@d12) & ' time: ' & endTime). 
  
  CalculateEndDateAndTime(deformat('20240101',@d12), 1, -1, 'H', endDate, endTime)
  if format(endDate,@d12) <> '20231231' or endTime <> 1 + 23*TIME:Hour then stop('failed test 13: ' & format(endDate,@d12) & ' time: ' & endTime & ' expected: ' & 1 + 11*TIME:Hour). 
  CalculateEndDateAndTime(endDate, endTime, 1, 'H', endDate, endTime)
  if format(endDate,@d12) <> '20240101' or endTime <> 1 then stop('failed test 14: ' & format(endDate,@d12) & ' time: ' & endTime). 
  
  CalculateEndDateAndTime(deformat('20240101',@d12), 1, -1, 'M', endDate, endTime)
  if format(endDate,@d12) <> '20231231' or endTime <> deformat('235900',@t5) then stop('failed test 15: ' & format(endDate,@d12) & ' time: ' & endTime & ' expected: ' & deformat('235900',@t5)). 
  CalculateEndDateAndTime(endDate, endTime, 1, 'M', endDate, endTime)
  if format(endDate,@d12) <> '20240101' or endTime <> 1 then stop('failed test 16: ' & format(endDate,@d12) & ' time: ' & endTime). 
  
  CalculateEndDateAndTime(deformat('20240101',@d12), 1, -1, 'S', endDate, endTime)
  if format(endDate,@d12) <> '20231231' or endTime <> deformat('235959',@t5) then stop('failed test 17: ' & format(endDate,@d12) & ' time: ' & endTime & ' expected: ' & deformat('235959',@t5)). 
  CalculateEndDateAndTime(endDate, endTime, 1, 'S', endDate, endTime)
  if format(endDate,@d12) <> '20240101' or endTime <> 1 then stop('failed test 18: ' & format(endDate,@d12) & ' time: ' & endTime). 
  
  CalculateEndDateAndTime(deformat('20240101',@d12), 100, -1, 'S', endDate, endTime)
  if format(endDate,@d12) <> '20231231' or endTime <> deformat('235959',@t5)+99 then stop('failed test 19: ' & format(endDate,@d12) & ' time: ' & endTime & ' expected: ' & deformat('235959',@t5)+99). 
  CalculateEndDateAndTime(endDate, endTime, 1, 'S', endDate, endTime)
  if format(endDate,@d12) <> '20240101' or endTime <> 100 then stop('failed test 20: ' & format(endDate,@d12) & ' time: ' & endTime). 
  
  stop('done date/time tests')

aside: Where I said

deformat('235959',@t5)+99

I could simply have said TIME:Day

1 Like