Why is adding time and subtracting time so hard?

[Mike Wilson]

I have two date/time fields. I'm trying to subtract start and end times, as so many have before me. Is there a good reference for this? It seems inordinately difficult. I have read, and re-read thread after thread, the docs and played with this until the frustration is too much to take.

The answers seem to point to using toseconds(). So I go to learn about this function.

docs:

Numeric Count = TOSECONDS( Formatted_Time as C )
Converts a Formatted_Time containing a time value to an integer Count equal to the number of seconds past midnight.

SO I GIVE IT A WHIRL -

dim vdtT as T

vdtT = now()
? vdtT
= 08/27/2007 12:03:05 61 pm

dim vcT as C
vcT = format_data(vdtT,"0")
? vcT
= "08/27/2007 12:03:05 61 pm"

dim vnT as N
vnT = toseconds(vcT)
? vnT
= 8

Can someone please explain why this answer is 8 and not 43385 (12*3600 + 3*60 + 5)?

[G Gabriel]

Re: Why is adding time and subtracting time so hard?

Mike:

Can someone please explain why this answer is 8 and not 43385 (12*3600 + 3*60 + 5)

toseconds() takes formated time as a parameter, NOT a character. vcT is character.

dim vdtT as T
vdtT = "08/27/2007 12:03:05 61 pm"
?toseconds(time("h:m:s am",vdtT))
= 43385

[Melvin Davidson]

Re: Why is adding time and subtracting time so hard?

As an aid in helping you with future time calculations, try having a look at my
Time Difference post in the code archive. Its a form that does vary time difference calculations. Although written in v4.5, it should work in all future versions.

http://msgboard.alphasoftware.com/al...elvin+Davidson

[Mike Wilson]

Re: Why is adding time and subtracting time so hard?

GABRIEL,
Thanks, you got me on the right track. Partially. Yes it will take it as a Character, just without the date component

dim vdtT as T

vdtT = now()
? vdtT
= 08/27/2007 01:58:32 56 pm

dim vcT as C
vcT = format_data(vdtT,"0")
? vcT
= "08/27/2007 01:58:32 56 pm"

[COLOR="Red"]dim vcT2 as C
vcT2 = substr(vcT,12,14)
? vcT2
= "01:58:32 56 pm"[/COLOR]

dim vnT as C
vnT = toseconds(vcT2)
? vnT
= 53536

The docs are very misleading/wrong:

Assume you stored the time at which an event started in a field called START, and the time at which the event ended in a field called end. To compute the duration of the event, you use the following expression:

toseconds(end) - toseconds(START)

maybe, but what value type does the variable that collects this difference have to be? 'cause ... as T, ... as A, ...as C ...as N, as... all return "argument is incorrect data type". I cannot get this to deliver a workable variable... so this documentation is either wrong, misleading, or something.... but it is not helpful. I'm sensing that getting to understand how to work with time values with math functions is going to be a grind and require expressions with compound functions. Again, why is this so convoluted?

[Mike Wilson]

Re: Why is adding time and subtracting time so hard?

Melvin,
Thanks. I'll download and study.

[G Gabriel]

Re: Why is adding time and subtracting time so hard?

Yes it will take it as a Character, just without the date component

I'm sensing that getting to understand how to work with time values with math functions is going to be a grind and require expressions with compound functions. Again, why is this so convoluted?

Mike:
Remeber, there is no such a thing as a true "Time" type field. I would venture to say, this is not just alpha's problem. Think about it. What is time? Time is a very weired animal. Is it character? not really, we measure time in terms of numbers. So, is it numeric? yes, but..not really, it does not follow the 0 to 9 pattern. So what the heck is it or how to accomodate it?
In the past, alpha never had "Time" fields, only character. Time fields are a "genetically engineered character field", so yes, if you eliminate the date part you could use the time part. You could create your own function that does that, but I am not sure if it will be much simpler than alpha's. You already created one using substr().

If you want to calculate the time difference between two "Time" fields (i.e. type T), the simplest way is:

dim t1 as t=now()
dim t2 as t=now()
?t1
= 08/27/2007 02:59:18 87 pm

?t2
= 08/27/2007 02:59:34 95 pm

?t2-t1
= 16.078

No need for toseconds()
P.S
Can you see what a slow typist I am?

[CALocklin]

Re: Why is adding time and subtracting time so hard?

GABRIEL,
Thanks, you got me on the right track. Partially. Yes it will take it as a Character, just without the date component

dim vdtT as T
 
vdtT = now()
? vdtT
= 08/27/2007 01:58:32 56 pm
 
dim vcT as C
vcT = format_data(vdtT,"0")
? vcT
= "08/27/2007 01:58:32 56 pm"
 
[COLOR=red]dim vcT2 as C[/COLOR]
[COLOR=red]vcT2 = substr(vcT,12,14)[/COLOR]
[COLOR=red]? vcT2[/COLOR]
[COLOR=red]= "01:58:32 56 pm"[/COLOR]
 
dim vnT as C
vnT = toseconds(vcT2)
? vnT
= 53536

The docs are very misleading/wrong:


maybe, but what value type does the variable that collects this difference have to be? 'cause ... as T, ... as A, ...as C ...as N, as... all return "argument is incorrect data type". I cannot get this to deliver a workable variable... so this documentation is either wrong, misleading, or something.... but it is not helpful. I'm sensing that getting to understand how to work with time values with math functions is going to be a grind and require expressions with compound functions. Again, why is this so convoluted?

First I have to agree - Yes, it is very awkward to work with (add/subtract) time values.

As you pointed out in your first post, the Help file says the ToSeconds() function takes a character value and this is absolutely correct:
Numeric Count = TOSECONDS( Formatted_Time as C )

BUT formatting the Time() function with "am" messes up the ToSeconds() function because ToSeconds() doesn't look at the am/pm designation. Just leave off the "am" - note the difference in the result:

vdtT = now()
? vdtT
= 08/27/2007 04:40:05 92 pm

?time("h:m:s am",vdtt)
= "4:40:5 pm"

?time("0h:0m:0s",vdtt)
= "16:40:05"

Or, just do it the easy way if you don't need the date to start with:

?time()
= "16:45:03"

I haven't done it for awhile but when I needed to check if a date/time range was from before midnight to after midnight, I simply saved a separate date value and time value at each point then checked them both.

I suppose it would be nice if Alpha could build a function that could take two time variables (like from the Now() function) and determine the number of seconds between them. It shouldn't be terribly difficult to write your own but it would be nice to have one built-in and preferably written in a very fast language (C++?) in case it gets used in a loop.

[G Gabriel]

Re: Why is adding time and subtracting time so hard?

I suppose it would be nice if Alpha could build a function that could take two time variables (like from the Now() function) and determine the number of seconds between them.

Cal:
You must not have read my last post. That function is already there and it's a simple one, it's the minus operator as in:

end-start

Also:
The presence of "am" is reduntant when it comes to toseconds() but it does not mess up anything:

vdtT = now()
? vdtT
= 08/27/2007 04:05:38 46 pm

?toseconds(time("h:m:s am",vdtt))
= 57938
?toseconds(time("h:m:s",vdtt))
= 57938

[Mike Wilson]

Re: Why is adding time and subtracting time so hard?

Gabriel,
I think you are incorrect in your statement that am/pm are not problematic.

Cal was responding to the inaccuracy of this in the documents:

Assume you stored the time at which an event started in a field called START, and the time at which the event ended in a field called end. To compute the duration of the event, you use the following expression:

toseconds(end) - toseconds(START)

If the fields are Time fields, the output of this equation when 'end' and 'START' are date-time fields is what?

Numeric: "argument is incorrect data type".
Character: "argument is incorrect data type"
short time: "argument is incorrect data type"

You can't make this equation resolve into a variable as far as I can tell without a format change of the date-time value in the date-time fields. You have to end up using time() to format the date-time field values. The docs are not accurate. Time() is the method to get rid of the am/pm that Cal was referring to, which you, yourself, resorted to.

You just cannot use math functions directly to a date-time value. It must be re-formated.

[G Gabriel]

Re: Why is adding time and subtracting time so hard?

Mike:
You lost me!
If you have a field, START (type T)
and another field, END which is also type T
and you want to find the time difference in seconds between the two, then all you have to do is:

End-Start.

As I illustrated earlier:

dim t1 as t=now()
dim t2 as t=now()
?t1
= 08/27/2007 02:59:18 87 pm

?t2
= 08/27/2007 02:59:34 95 pm

?t2-t1
= 16.078

As you can see in the above code, the difference is 16.078 seconds, and as I mentioned earlier, you don't need to use toseconds() to get that difference.

But, in response to Cal's comment, again, if you are using toseconds() for some other reason, the am makes no difference, again, as I illustrated earlier:

vdtT = now()
? vdtT
= 08/27/2007 04:05:38 46 pm

?toseconds(time("h:m:s [COLOR="Blue"][B]am[/B][/COLOR]",vdtt))
= 57938
?toseconds(time("h:m:s",vdtt))
= 57938

I am not making any comments in regards to what the docs says as I have no idea what it says and whether it is misleading or not.

[G Gabriel]

Re: Why is adding time and subtracting time so hard?

I just read the docs in regards to toseconds()
Is it misleading?
No.. but obscure:
it says:
".....Assume you stored the time at which an event started in a field called START, and the time at which the event ended in a field called end. To compute the duration of the event, you use the following expression:
toseconds(end) - toseconds(START)..."

But if you follow all along from the beginning it tells you:
"Syntax
Count as N = TOSECONDS( Formatted_Time as C )"
and also tells you:
"...Converts a Formatted_Time containing a time value.."

and all the examples provided show formatted time.
I am not defending the docs and understand your frustrations, but after looking at the docs for long enough time, you get to understand the idiosyncracies and get to know what they mean. Remember, these docs weren't written by Shakespeare either.

[Tim Kiebert]

Re: Why is adding time and subtracting time so hard?

Mike,

Here is a help page on date time subtraction which I got to from this page of Date and Time Functions which I found by Index > "date" > functions.

Like Gabe said all you need to to do is Endfield-Startfield. This gives a numeric result in seconds. Then use ToTime() to display the seconds as a time value.

There is I think a typo in the Date Time Subtraction page. I think
Result as D = Operand1 as T - Seconds as N should be

Result as T = Operand1 as T - Seconds as N

[Tim Kiebert]

Re: Why is adding time and subtracting time so hard?

I haven't done it for awhile but when I needed to check if a date/time range was from before midnight to after midnight, I simply saved a separate date value and time value at each point then checked them both.

Cal,

It would seem from the below interactive window results that subtracting a before midnight date time value (type T) from an after midnight date time value returns the correct elapsed time.

t2 = ctodt("28/08/2007 02:39:06 59 am")
?t2
= 28/08/2007 02:39:06 59 am

t1 = ctodt("27/08/2007 11:15:06 59 pm")
?t1
= 27/08/2007 11:15:06 59 pm

?t2-t1
= 12240

?totime(t2-t1,2,0)
= "3:24:00"

even spanning more than one day

t2 = ctodt("28/08/2007 02:39:06 59 am")
?t2
= 28/08/2007 02:39:06 59 am

t1 = ctodt("26/08/2007 11:15:06 59 pm")
?t1
= 26/08/2007 11:15:06 59 pm


?t2-t1
= 98640

?totime(t2-t1,2,0)
= "27:24:00"

[CALocklin]

Re: Why is adding time and subtracting time so hard?

OK guys, give me a chance to reply! My wife has me doing some work around the house, too. (It only took her 22 years to get me to finish the basement stairs - as in "make custom trim to cover all the cat scratches, install it, sand the steps to clean up the years of dirt accumulation, stain 'em, and polyurethane 'em" - and I'm still not sure why it's a big deal since it's basically used only for storage. Oh, well, it's now 95% done. All I need to do is add the handrail once it dries.)

Cal:
You must not have read my last post.

Actually, you're right. I didn't read it because I was still writing my reply to the previous post. There were so many interruptions while I was putting together my post that it probably took at least an hour from start to finish. By the time I finally posted it, you had already replied.

But you are correct. That is awfully easy, isn't it! And I missed it completely.

As I said to a new developer just a couple days ago (bold added), "Always remember that you can always learn from any other programmer. ... [including beginners] ... Sometimes "experience" can put you in a rut. Most beginners know they should listen to an "old hat" but some "old hats" should learn to listen to beginners as well - the smart ones already do."

If you read on you'll see where the "rut" started this time.

I am not defending the docs and understand your frustrations, but after looking at the docs for long enough time, you get to understand the idiosyncracies and get to know what they mean. Remember, these docs weren't written by Shakespeare either.

And, if you've been dealing with A5 long enough, you begin to understand that some of these issues may have occurred because many of the Help topics were written long ago. I opened my copy of A5v1 and checked the Help files. (I'm finally converting my last A5v1 customer to A5v8. Hurray!!) There was no now() command back then and this is from the A5v1 Help for the toseconds() command:

Assume you stored the time at which an event started in a field called START, and the time at which the event ended in a field called END. To compute the duration of the event, you use the following expression:

TOSECONDS(END) - TOSECONDS(START)

Look familiar? It should because the current Help still says the same thing. It doesn't help today's user much but it does make more sense when you realize that the Now() command didn't exist back then.

Note that it also says a FIELD called END. Trust me, you could not use a VARIABLE named END because the script would read it as the end of the script. At that time, when the script compiler saw END at the beginning of the line, it apparently ended the script no matter what came after the word "END".

So, I learned something new and useful today. Let's see, I now know this worked in A5v4 so how many years have I been stuck in that rut?? Well, the only thing I can say in my defense is: Guess which app it was that I had to get the elapsed time between a start and end time - Yep, that's right, the one that I'm just now converting from A5v1! What perfect timing!

[G Gabriel]

Re: Why is adding time and subtracting time so hard?

I think a small detail was forgotten:

toseconds() calculates the time in seconds SINCE MIDNIGHT. That's it. It is not meant to calculate the difference between two time fields, that's unless they fall in the SAME DATE.

So, if you have time fields, i.e. type T, they will have both the date & time elements and if you wish (and here is why I said the docs are not misleading, but rather obsecure) to calculate the difference using toseconds(), you've got to have a separate calculation for the date differnce and multiply that by seconds.

When the docs say:

".....Assume you stored the time at which an event started in a field called START, and the time at which the event ended in a field called end. To compute the duration of the event, you use the following expression:
toseconds(end) - toseconds(START)..."
it makes the assumption that you are still aware of the fact that toseconds CALCULATES ONLY TIME DIFFERENCE, Not date & time differnce.