Event Time Durations

[Ocean West]

working on a database that manages shows the contract is the main table. Currently the contract Event Date and Time are just two fields - and the time was something like 4pm - 9pm in a text field. On occasion the contract was for a multi day event and that was recored in just a notes field.

Additionally there is a Setup time - usually the same day as the event a few hours prior to the start time but many times setup is the night or day before the event. 

So I am structuring an 'event' table that is a Date / Start / Stop / Duration / and Event type [Show / Setup] 

some shows span the midnight hour and start at 7pm and then go till 2AM the next day so i am thinking that there needs to be an end date too, would you concur - which would be needed to calculate duration of the event. 

so a typical set of records would be:

Setup | 10/2/2015 | 5pm | 7pm
Show | 10/3/2015 | 8pm | 10/4/2015 | 2AM

and if there is an additional day for the show

Show | 10/4/2015 | 9am | 10/4/2015 | 3PM

The trick is to then figure to the best data entry mechanics ( triggers / modals ) to naturally store the dates & times or representative dates in the main table so that when looking at the list of contracts the data is not related and does not impede performance. 

[comment]

some shows span the midnight hour and start at 7pm and then go till 2AM the next day so i am thinking that there needs to be an end date too, would you concur - which would be needed to calculate duration of the event. 

No, an end date is not necessary. The duration can be calculated using only the start and end times as =

EndTime - StartTime +  86400 * ( EndTime < StartTime ) 

This is assuming no event ever spans more than 24 hours. And that StartTime and EndTime are Time fields.

[Ocean West]

thanks comment - for the  data entry they will be individual fields so data entry can be done thru pull downs for quicker and more consistent data.
 have a calculation time field that results as timestamp, perhaps the DateEnd will default to the DateStart and then thru some trigger scripted logic it will advance when the EndTime is ≥ 12AM, I still need to get confirmation from client but if I just have them provide the start time and the "total hour duration" then the end time could be calculated, it would save having enter all the extra end date/time elements. 

GetAsTimestamp ( DateStart & " " & StartHour & ":" & StartMinute & " " & StartPeriod ) 
GetAsTimestamp ( DateEnd & " " & EndHour & ":" & EndMinute & " " & EndPeriod ) 

i will be eventually using a plugin to push this data to Outlook - and in order for outlook to contain the values i think independent TimeStamps will be required, and be published from this entity. 

[comment]

I am afraid you kinda lost me there. If you're entering the duration instead of end time, then calculating the end - whether as date, time or timestamp - is rather trivial. There's no need for any 'default" value for the date of the end, Filemaker will do the adjustment automatically. For example:

Timestamp ( StartDate ; Time ( StartHour ; StartMinute ; 0 ) + 3600 * Duration )

will return "10/5/2015 2:45" when:

StartDate = 10/4/2015
StartHour = 22
StartMinute = 15
Duration = 4.5

I am not sure what StartPeriod and EndPeriod represent in your example.

Note also that EndTime is ≥ 12AM is always true. What matters is whether EndTime is greater than StartTime.

[Ocean West]

Period is AM or PM,  

The data entry for TIME will be three separate fields because some users just can't be bothered to enter data as military time or an actual time 7:15 PM. 

However - I think i see where you are leading me, presentation and  entry vs a data storage & calculation - I think the approach would be to provide what ever interface needed to facilitated the end users data entry requirements & validation - and then on commit the data is stored naturally 24 hour timestamp or even a simple numerical value. As all presentation references derived from these results can be formatted in any fashion.

[comment]

Period is AM or PM, 

Would you not prefer a checkbox for PM?

 

I think i see where you are leading me, presentation and  entry vs a data storage & calculation - I think the approach would be to provide what ever interface needed to facilitated the end users data entry requirements & validation - and then on commit the data is stored naturally 24 hour timestamp

Not necessarily. It doesn't really matter how the data is stored - you can always recalculate it in whatever format you need.

[Ocean West]

Thanks @comment using this made it work just fine. 

StartTimeStamp + 3600 * Duration

 

For data entry this works well 

http://fmforums.com/topic/93932-timepicker-for-filemaker/

 

 

 

Edited October 15, 2015 by Ocean West