Time Arithmetic Formulas

To Panorama, time is not hours, minutes, and seconds, but simply seconds. To be precise, a time is the number of seconds since midnight. For example, the time 4:32 AM is 16,320 seconds after midnight. As you can see, a Panorama time is really a number in disguise. Since times are numbers, it’s easy to compare them, sort them, or find the difference between them (number of seconds).

#### Converting Between Times and Text

Unlike dates, Panorama does not automatically provide a time data type that automatically converts a date in text format into a number. You must use a function to convert time in text format into seconds before you can do math calculations with the time, and use another function to convert back.

now() This function returns a number representing the current time. The number is the number of seconds since midnight. For example, if the time is 10:23 AM, this function will return the number 37,380.

seconds(text) This function converts text into a number representing a time. The function has one parameter — the text that you want to convert to a number representing a time. If the text includes and AM or PM suffix, the number of seconds is calculated from midnight (12 A.M.), otherwise it is calculated from 0:00:00 (elapsed time). The text must contain a valid time. Here are some examples of valid times:

    4:13 PM
    11:00 AM
    2:30
    18:45

The seconds( function returns the time you pass to it as the number of seconds since midnight. For example, if the time passed to it is 10:23 AM, the function will return the number 37,380.

timepattern(number,pattern) This function converts a number representing a time into text. The function uses a pattern to control how the date is formatted.

The function has two parameters: number and pattern. Number is the number that you want to convert to text. This number must be the number of seconds since midnight. Pattern is text that contains a pattern for formatting the date. The pattern is assembled from four components: hh (hours), mm (minutes) ss (seconds), and am/pm. Some of the more common time patterns are listed here, along with typical examples of the resulting text:

    "hh:mm:ss am/pm" ☞ 4:32:17 pm
    "hh:mm am/pm" ☞ 4:32 pm
    "hh:mm:ss" ☞ 16:32:17

If am/pm is left off the pattern the time will be formatted in 24 hour format, as shown on the last line of the table above. You should also leave off am/pm for converting elapsed times.

time(text)￼This function converts text into a number representing a time. The function has one parameter — the text that you want to convert to a number representing a time. The time function allows you to leave out the colons in the time, and also allows you to leave off the am/pm. Here are some examples of valid times:

    4:13 PM
    11:00 AM
    2:30
    18:45
    230
    4p
    midnight
    noon
    afternoon
    evening
    night
    nite

The time( function is very lenient about the format you use to enter the time. It will accept a time without colons, for example 425 pm instead of 4:25 pm. If there is no am or pm, the time function will assume 24 hour time.

The time( function will also convert “named” times: noon, midnight, morning, afternoon, evening, and night. This function assumes that morning is 9:00 am, afternoon is 1:00 pm, evening is 6:00 pm, and night is 10:00 pm.

timestr(number) Convert a number to text in am/pm time format (for example 9:34 AM).

#### Time Calculations

Once time has been converted into seconds you can perform arithmetic on it. For example, to calculate the number of hours worked from a time card use a formula like this (this formula assumes that In and Out are text fields containing times).

    (seconds(Out)-seconds(In))/3600

(The division by 3600 converts the result into hours.)

To find out when a task will be finished that takes 2 1⁄2 hours to complete, use the formula

    seconds(«Start Time»)+seconds("2:30")

Simple addition and subtraction does not compensate for time wrapping around midnight. For example, if you want to calculate the length of a shift that begins at 11 P.M. and ends at 7 A.M., you must add 24 hours to 7AM before subtracting the times. To solve this problem you can use one of the functions described below, or you can use a SuperDate, which combines time and date into a single number (see SuperDates).

time24(time This function takes a time and makes sure it falls within a 24 hour period. If the time is less than 24 hours, it is unchanged. If the time is greater than 24 hours, it is converted to the equivalent time in a 24 hour period (for example 30:00:00 is converted to 6:00:00).

The time24( function can help with calculations of an ending time from a start time and duration. The basic formula for such a calculation is shown here.

    EndTime=StartTime+Duration

This formula works fine unless the interval extends over midnight. The time24( function adjusts the result to make sure it starts over at zero as it crosses midnight.

    EndTime=time24(StartTime+Duration)

This formula will correctly calculate that 10:30 PM + 4 hours is 2:30 AM.

timedifference(start,end) This function calculates the difference between two times. It works correctly even if the interval between the two times crosses over midnight. This function returns a time interval between -12 and +12 hours. See also the timeinterval( function, which returns a time interval between 0 and 24 hours.

There are two parameters, start and end. Start is a number (number of seconds) representing the starting point of the time interval. End is a number (number of seconds) representing the ending point of the time interval. This function returns the number of seconds between the two times. For example, if the start time is 9:30 PM and the end time is 2:05 AM, the difference would be 4:35. But if the parameters are reversed and the start is 2:05 AM and the end is 9:30 PM, the difference is -4:35. If the result is positive, the end is after the start. But if the result is negative, the start is after the end.

timeinterval(start,end) This function calculates the time interval between two times. It works correctly even if the interval between the two times crosses over midnight. This function returns a time interval between 0 and 24 hours. See also the timedifference( function, which returns a time interval between - 12 and +12 hours.

There are two parameters, start and end. Start is a number (number of seconds) representing the starting point of the time interval. End is a number (number of seconds) representing the ending point of the time interval. This function returns the number of seconds between the two times. For example, if the start tine is 9:30 PM and the end time is 2:05 AM, the interval would be 4:35. But if the parameters are reversed and the start time is 2:05 AM and the end time is 9:30 PM, the interval is 19:25.

converttimezone(time,fromzone,tozone) This function converts a time from one time zone to another. This example converts from New York time to Berlin time. (The time24( function is needed in case the conversion goes over midnight. It should only be used with regular time, do not use the *time24(* function when converting superdates.)

    time24(converttimezone(time("3:18 PM"),"New York","Berlin"))

#### Time Calculations with Text

Unlike the functions in the previous sections, these functions operate with time values in strings. There aren’t as many functions available as for times expressed as numbers, but if your input and output values will be in strings using these function saves the intermediate conversion steps.

texttimedifference(start,end) This function calculates the difference between two times. Instead of being expressed as numbers, the input output times are expressed as text (for example 12:45 pm). This function works correctly even if the interval between the two times crosses over midnight. This function returns a time interval between -12 and +12 hours. See also the texttimeinterval( function, which returns a time interval between 0 and 24 hours.

There are two parameters, start and end. Start is a string representing the starting point of the time interval. End is a string representing the ending point of the time interval. This function returns the time difference between the start and end. For example, if the start time is 9:30 PM and the end time is 2:05 AM, the difference would be 4:35. But if the parameters are reversed and the start is 2:05 AM and the end is 9:30 PM, the difference is -4:35. If the result is positive, the end is after the start. But if the result is negative, the start is after the end.

texttimeinterval(start,end) This function calculates the time interval between two times. It works correctly even if the interval between the two times crosses over midnight. This function returns a time interval between 0 and 24 hours. See also the texttimedifference( function, which returns a time interval between - 12 and +12 hours.

There are two parameters, start and end. Start is a string representing the starting point of the time interval. End is a string representing the ending point of the time interval. This function returns the time between the start and end. For example, if the start tine is 9:30 PM and the end time is 2:05 AM, the interval would be 4:35. But if the parameters are reversed and the start time is 2:05 AM and the end time is 9:30 PM, the interval is 19:25.

#### Calculating Time Intervals Smaller Than One Second

The info(“tickcount”) function can be used to calculate time intervals down to 1/60th of a second (0.0165 second), while the info(“milliseconds”) function has resolution down to a thousanth of a second. This function returns the number of 1/60th or 1/1000th second intervals since the computer was turned on. Here is an example that uses this function to delay for 1/4 second.

    local beginDelay
    beginDelay=info("milliseconds")
    loop
        nop
    while info("milliseconds")<beginDelay+250

This loop will delay for 1/4 second on any computer, no matter what the processor speed is.

#### Time Code Calculations (Video/Film)

Panorama user and visual effects supervisor Chris Watts has built an asset management system that he has used in the production of several major motion pictures, including Pleasantville and 300. In the process, Chris has developed about a dozen Panorama functions that are useful in performing calculations with timecodes used in video and film, which he has donated so that other Panorama users can take advantage of them. Here at ProVUE we’re not video/film editing experts, so these functions are provided as-is with Panorama.

- feetandframes( -- The feetandframes( function takes an integer and returns a feet and frame count for 35mm film.
- kcadd( -- The kcadd( function takes a keycode for 35mm film and adds a number of frames to it and returns a keycode as a string.
- kcdiff( -- The kcdiff( function calculates the number of frames between two frames of 35mm film, using inclusive counts (submitted by chris watts)
- kcframes( -- The kcframes( function returns an integer based on a feet and frames count for 35mm film.
- kcoutfromlength( -- The kcoutfromlength( function answers the question "what will the last frame be if i start my cut at the incode and my shot is some number of frames long"?
- outcode( -- The outcode( function adds one frame to a timecode.
- tc24to30( -- The tc24to30( function converts 24 frame timecode to 30 frame non-drop timecode.
- tc30to24( -- The tc30to24( function converts 30 frame non-drop timecode to 24 frame timecode.
- tcadd( -- The tcadd( function accepts a timecode as text, and adds some number of frames, returns a timecode string.
- tcdiff( -- The tcdiff( function calculates the number of frames between two timecodes.
- tcframes( -- The tcframes( function returns a number of frames from zero of a 24fps, 25fps, or 30fps timecode.
- timecode( -- The timecode( function returns a timecode string corresponding to an integer.

See Also

- Arithmetic Formulas -- mathematical operators and functions.
- Characters and Unicode Values -- working with individual characters of text.
- Constants -- values embedded into a formula.
- converttimezone( -- converts a time or superdate from one time zone to another.
- convertvariablestoconstants -- converts all of the variables in a formula into constant values.
- Date Arithmetic Formulas -- performing calculations on dates, and converting between dates and text.
- daylightsavingstimeoffset( -- returns the current daylight saving time offset of the specified time zone.
- Formula Workshop -- formula workshop wizard for testing and experimenting with formulas.
- formulacalc -- allows you to evaluate a formula that you were not able to code into the procedure when it was being written.
- formulafields( -- returns a list of fields used in a formula.
- formulaidentfiers( -- returns a list of identifiers (fields and variables) used in a formula.
- Formulas -- basics of formulas: components and grammar.
- formulavalue -- calculates the result of a formula. Usually this is done with an assignment statement (for example `x=2 * y` ), but the *formulavalue* statement gives you more flexibility. You can specify what database is to be used for the calculation (an assignment statement always uses the current database) and you can specify the formula using a variable, making it easy to change on the fly. This statement also gives you more control over how errors are handled.
- formulavariables( -- returns a list of variables used in a formula.
- Functions -- index of all functions available for use in Panorama formulas.
- info("localtimezone") -- returns the local time zone for the current location of your computer.
- info("timezoneabbreviations") -- returns a dictionary of time zone abbreviations supported by the system.
- info("timezones") -- returns a list of time zone regions recognized by the system.
- Linking with Another Database -- techniques for relating multiple database files so that they work together.
- makemergeformula( -- builds a formula from an “auto-wrap" style merge template.
- nextdaylightsavingstimetransition( -- returns the next date on which a transition to or from daylight savings time occurs.
- Non Decimal Numbers -- working with numbers in alternative (non-decimal) bases, including binary, octal and hexadecimal.
- Numbers -- Working with numeric values in a formula, and converting between numbers and text.
- Operators -- index of all operators available for use in Panorama formulas.
- Quotes -- text constants embedded into a formula
- Recompiling Code -- recompiling code & formulas
- Statements -- index of all statements available for use in Panorama procedures.
- SuperDates -- date and time combined into a single value.
- Tag Parsing -- Panorama functions for working with text that contains data delimited by tags, including HTML.
- Text Formulas -- manipulating text with a formula (concatenation, extraction, rearranging, etc.)
- timezoneabbreviation( -- returns the abbreviation of the specified time zone.
- timezonename( -- returns the continent and city of the specified time zone.
- True/False Formulas -- logical boolean calculations.
- Using Fields in a Formula -- accessing database fields within a formula.
- Values -- the raw material that formulas work with -- numbers and text.
- Variables -- storing and retrieving individual items of data, not part of a database.

History

Version | Status | Notes
10.0 | No Change | Carried over from Panorama 6.0