The notions of TIME and DURATION and how to use in...

Joseph_Seddik · 01-03-2022 08:05 AM

The notion of TIME:

A TIME is a specific hour of the day
You should write it in the following format: "00:00:00", corresponding to HH:MM:SS
You should include it between quotes "" in order for the expression to work.

The notion of DURATION:

A DURATION refers to the amount of time between two moments, each moment is a TIME.
You should write it in the following format: "000:00:00", corresponding to hours:minutes:seconds.
Note the leading 3 digits:* 000, this is the only way you tell appsheet that this is a DURATION not a TIME (beginning with 2 digits: 00).
Alternatively, you can also use the following format: "00.00:00:00", here you begin with 2 digits then a dot then 2 digits: 00.00, this corresponds to days.hours:minutes:seconds.
Note that the output of AppSheet will display only two leading digits 00 for a duration value. However, your input should always begin with either three digits 000, or 2-dot-2 digits 00.00, to specify a duration.
And you should include it between quotes “” in order for the expression to work.

Examples:

Now, let’s say that TIMENOW() translates to 9AM. Here’s how the expressions would work, and how to set the type of your column so you don’t get an error:

TIME + TIME → This is meaningless.
TIME + DURATION = TIME
→ Example: TIMENOW() + "000:30:00" = 09:30:00 (9:30AM).
Note the leading 3 zeros: 000.
DURATION + DURATION = DURATION
→ Example "001:00:00" + "000:30:00" = 01:30:00 (1 hour 30 minutes)

And,

TIME - TIME = DURATION
→ Example: TIMENOW() - "12:00:00" = -03:00:00.
The amount of time starting from 12AM to 9AM is -3 hours.
TIME - DURATION = TIME
→ Example: TIMENOW() - "012:00:00" = 21:00:00.
The TIME 12 hours earlier is 9PM.
DURATION - DURATION = DURATION
→ Example: "001:00:00" - "000:30:00" = 00:30:00 (30 minutes)
DURATION - TIME → Meaningless

How to transform separate Date and Time values into a combined DateTime value:

When would you need that?

One example is that you have Date and Time values in two separate columns, and you need to join them in a single value for an expression, say, for example in a YES/NO expression to determine whether you are past a certain hour on a specific day.
Another example is when you have a Date only and you need to associate and hour to that date.

Considerations:

A Date-only value is internally a DateTime value, with the Time set to "00:00:00", that is Midnight.
A Time-only value is internally a DateTime value, with the Date set to the famous 30/12/1899.

Solution:

Make use of the concept TIME + DURATION = TIME mentioned above.

Example:

Suppose you have two separate Date and Time columns. To combine them together, just add a zero-Duration to them:
DateTime value = [Date] + [Time] + "000.00.00"

Aurelien

Hi @KJS

Thanks for sharing this !
You may want to set it as “Tips & Tricks” category, it’s relevant

Also, for additional reference:

EDIT : also, you may want to specify that TIME and DATETIME are pretty similar in their use when it comes to compute with duration

EDIT 2:

!!!

Steve

This is new to me! Thanks!

Joseph_Seddik

@Steve @Aurelien Welcome and thanks much for your feedback!
Moving to Tips & Tricks.

Joseph_Seddik

Update - Added the last section: How to transform separate Date and Time values into a combined DateTime value

Douglas_English

I still need to be able to calculate duration for times that go past 12 AM into the next day WITHOUT DATETIME , is this possible?

Joseph_Seddik

[time2] - [time1] + IF([time2] < [time1], "012:00:00", "000:00:00")

Assuming you have other mechanisms to make sure the end time might only fall in the next day, not after, and you don't get spans exceeding 24 hours, otherwise you should construct DateTime values.