Time field in A5 v 5

[Mantford]

I would like to create a time field for appointments in which records could ordered in "time order" - is this possible ?.

[Keith Hubert]

RE: Time field in A5 v 5

Hi Micheal

The answer is yes. But the you will have to set the fields a C. You must also decide if you are using the 24 hour clock.

Keith Hubert
London.

[rtbtat]

RE: Time field in A5 v 5

Do you want to use AM/PM or 24 hour time?

Everyone has a different solution. Personally, I like using 24-hour time so I create a 5-character field with a field rule mask of 00:00 That�s all you have to do.

However, if you want AM/PM, there are several different ways of doing such. I use the same exact field described above and then I create a second field called AM/PM, which is a character field with a width of 2. I then create a simple lookup list in field rules which contains only two choices AM and PM, with the �Force Closest Match� option checked.

I then create a third field, which is a calculated field that combines the two above.

The tricky part is creating a field rule that calculates elapsed time. That isn�t too hard, but it gets very tricky if you go past midnight into the next day. We can help with that.

Robert T.

[Melvin Davidson]

RE: Time field in A5 v 5

Michael,

From page 318 of the user guide:
:There is no Time data type in Alpha Five. Times are expressed by character values in the form
"DDD:HH:MM:SS" and its variations. However, using the TOTIME and TOSECONDS functions,
you can perform time arithmetic. For example, you can compute the number of seconds between
time values, or compute a new time by adding or subtracting a specified number of seconds to a
starting time."

However, there appears to be a conflict in that, because even though you cannot define a column as type "Time",
You can dim a variable as type Time.

Eg:
dim timenow as T
timenow = now()
?timenow
= 05/08/2003 08:18:03 86 am
?typeof(timenow)
= "T"

So while you cannot store a time in column (ask Alpha Software why), you can perform calculations with it in a script

However, if you do not need the date portion of the time, the you could store the time in a character or numeric column:

Dim ctime as c
ctime = time()
?ctime
= "08:37:05"

numtime = toseconds(ctime)
?typeof(numtime)
= "N"

[rtbtat]

RE: Time field in A5 v 5

Melvin wrote:

[So while you cannot store a time in column (ask Alpha Software why), you can perform calculations with it in a script]

That is not true, I store time on a regular basis. It depends upon your definition of time. For me time is generally when an event starts and/or ends. For example, an employee arrives at work 08:00 and leaves work at 16:00.

The calculation is a piece of cake for A5, the answer is obviously 8 hours. And you don't need a script to do the calculation.

Robert

[Melvin Davidson]

RE: Time field in A5 v 5

Robert,

I disagree, it is true. You cannot make a column of type 'time'.

Yes, you can store 'time', but you must save it to
a character (or numeric) data type as previuously
described, and convert back for comparison.

Are you saying you have created a column of type 'time',
or just storing the value? There is a significant difference.

I would be very interested to see if your table
definition contains a column data type of Time. If so, could you please attach it for everyone else to benefit?

[rtbtat]

RE: Time field in A5 v 5

Hi Melvin:

You and I don't necessarily disagree, we're using different terminology and focusing in different areas. Maybe I wasn't clear the first time around. I thought that I came up with my personal definition of time and how it�s utilized in Alpha Five, which is a field that stores the time [value] of an event. I gave the example of someone who starts work at 08:00 and finishes work at 16:00. So yes I store those times [values] in character fields.

Most of the time [no pun intended], people don't need the actual time the record was entered, which is where I think you are focused. There are occasions when that's appropriate, such as auditing changes to a record, you obviously want the exact date and time of that change.

This thread began with a practical question from Michael M. reference sorting the time of specific events, which is quite typical. My previous post was a response to Michael's question.

In the vast majority of cases, people need to store the value of an event[s], such as what time it stated and/or ended. So my point was simple, you can store time in A5 fields and then calculate the elapsed time of those events if you have a starting/ending time. However, my "practical definition" of storing time in terms of Michael's question is of course different than your definition.

Robert T.


Robert

[Mantford]

RE: Time field in A5 v 5

Thanks lads, all I would like to do is enter the time {09.30}, {09.45} for patient appointments and then sort the list starting with the 09.30 entry.

If I can get to do this, I'm in business.

Thanks
Michael Mee

[rtbtat]

RE: Time field in A5 v 5

Hi Michael:

No problem, you can do that quite easily. However, you may want some field rules and I'll be glad to help, as will many other participants on this discussion board. Are you familiar with field rules?

For now, may I suggest you make a character field for the appointment time with a width of 5. T

Then go to Field Rules, select the Data Entry tab, choose �Simple Mask�, and create a mask as follows [those are zeros, not letters]

00:00

While you�re at it, you could make another validation field rule such as Appt_Time > 09:29 so that no one can enter a time prior to 09:30. You will probably want another validation field rule for the latest time.

At the very least, that will get you started and then you can easily sort by time in queries and reports. When you run across problems, come to this board for additional help.

Robert

[Melvin Davidson]

RE: Time field in A5 v 5

Michael,

Given your example, and hopefully, that you do not need to store using AM/PM prefix, then the following should work:

Given that time_column is type character (min length of 8),

tbl = table.current()
tbl.enter_begin()
tbl.time_column = time()
tbl.enter_end(.T.)

Since the 'time' will be military, ie: 24 hr format,
there should be no trouble sorting on the 'time_column';

If you need to due 'calculations' on the column, then
toseconds(timenow) will give you a valid numeric value.

[Mantford]

RE: Time field in A5 v 5

Thanks lads - I'm almost there

Regards

M Mee