Re: How can I add minutes to a timestamp? If I'v....

MultiTech · 08-31-2018 05:00 PM

How can I add minutes to a timestamp?

If I’ve got a column that’s a Time, and a column that’s a number, and I want to add the minute equivalent of the number entered/selected… how?

For the examples below, let’s assume that: TimeNow() = “08:00:00”, and [Log_Start_Mod_Minutes] = 1 (number type)

----- You cant add a number, for this adds the hour equivalent of what you’ve entered.

-> TimeNow() + 1 = “09:00:00” (aka, plus one hour)

-It’s valid: https://cl.ly/2a541ed1f97a

The result: https://cl.ly/9132c8444eff < plus one hour

You can’t divide the number entered/selected by 60 to get the decimal, because that truncates to 0.

-> TimeNow() + 1/60 = “08:00:00” (1/60 = 0 so no change)

-It’s valid: https://cl.ly/2e9381a7cd04 | https://cl.ly/09c08ebe55ea

The result: https://cl.ly/04a64b77b121 | https://cl.ly/e73cd699ac0f < no change (for both)

You can’t concatenate together a duration, using the number entered as the minutes, and add that to the timestamp

-> TimeNow() + concatenate(“000:”, [Log_Start_Mod_Minutes], “:00”)

-It’s NOT valid: https://cl.ly/771e462e644c

But you can hard code it

-> TimeNow() + “000:01:00” = “08:01:00” (aka, plus one minute)

-------------------------------------- I believe this is the culprit of my issue: (https://cl.ly/3051dc94dc07) <- there are only two data types allowed to operate with the Time type:

number and duration.

Can we get decimal added to this list?

Other than something like that… anyone got any suggestions on how I can add minutes to a timestamp?

@praveen @Adam_Stone_AppSheet

Grant_Stead

Yeah man, it’s lame. I had to do it hardcore style.

Grant_Stead

Especially important when you’re ignoring seconds

MultiTech

@Grant_Stead I’m not sure about lame, but it’s something that I’d really like to get working.

How’s your hardcore style work?

Grant_Stead

@MultiTech_Visions

It’s something crazy =IF( OR( MINUTE(TIME(NOW()) - “00:00:00”)=MINUTE(TIME([clock_priorclock_datetime_device]) - “00:00:00”), [clock_priorclock_datetime_device]>NOW() ),

DATETIME(CONCATENATE(TODAY()," “,HOUR(TIME([clock_priorclock_datetime_device]) - “00:00:00”),”:",MINUTE(TIME([clock_priorclock_datetime_device]) - “00:00:00”)+1,":00")),

NOW() )

MultiTech

So basically extract each element out (hour, minute, seconds), do your math, then build it back together.

Grant_Stead

@MultiTech_Visions Yep, I worked this super hard for a very long time, and that was the best/only way…

Kirk_Masden

Since @Grant_Stead mentioned working hard to get times and dates to look the way he wanted, I thought I’d share something I put a lot of time into recently.

The column that contains the timestamp in my app is [Dated].

An expression to produce something short like

8/31 18:39

is

=concatenate(MONTH([Dated]),"/",DAY([Dated])," ",number(left(concatenate(TIME([Time])),2)),left(right(concatenate(TIME([Time])),6),3))

To produce a longer, more user friendly date like

September 2, 2018 (Sunday)

I concatenated

=concatenate(ifs(MONTH([Dated])=1,“January”,MONTH([Dated])=2,“February”,MONTH([Dated])=3,“March”,MONTH([Dated])=4,“April”,MONTH([Dated])=5,“May”,MONTH([Dated])=6,“June”,MONTH([Dated])=7,“July”,MONTH([Dated])=8,“August”,MONTH([Dated])=9,“September”,MONTH([Dated])=10,“October”,MONTH([Dated])=11,“November”,MONTH([Dated])=12,“December”,)," “,day([Dated]),”, ",year([Dated]))

with

=concatenate([Text date]," (",ifs(WEEKDAY([Dated])=1,“Sunday”,WEEKDAY([Dated])=2,“Monday”,WEEKDAY([Dated])=3,“Tuesday”,WEEKDAY([Dated])=4,“Wednesday”,WEEKDAY([Dated])=5,“Thursday”,WEEKDAY([Dated])=6,“Friday”,WEEKDAY([Dated])=7,“Saturday”),")")

It works but it’s not easy.

One advantage, though, of building date expression up like this from scratch is that the system works in any language. This part of my app is in English but if I wanted to do something similar in Japanese, it would be just as easy (or hard, depending on your perspective

).

Stephen_Mattiso

@Kirk_Masden

Sweet, thanks!

Useful!

Aleksi_Alkio

One workaround is if you convert the minute number as duration in a virtual column like CONCATENATE(“000:”,[NUMBER],":00"), you can then calculate the time like [OriginalTime]+[VirtualDuration].

Stephen_Mattiso

@Aleksi_Alkio

Another Wizardly solution! TYTY! Translate

Kirk_Masden

I’ve been working on this topic today because I’m interested in using USERTZOFFSET() to adjust the “ChangeTimeStamp” values produced by devices. The “ChangeTimeStamp” column depends on the location of the device. So, if you want to have all of your times adjusted to local time in, say, in New York, you need to use USERTZOFFSET() to record the time zone of the device. USERTZOFFSET() tells you how many minutes the devices time is from UTC. So, this number is usually bigger than 60. That led me to use the following formula to put the time in a format that could be added to or subtracted from the ChangeTimeStamp:

concatenate(
left(“00”,2-len(text(floor(abs([Minutes of adjustment])/60)))),
floor(abs([Minutes of adjustment])/60),
“:”,
left(“00”,2-len(text(mod(abs([Minutes of adjustment]),60)))),mod(abs([Minutes of adjustment]),60),
“:00”
)

This converts 90 minutes, for example, to “01:30:00” – the duration format that can be added to or subtracted from a time stamp value.

In this example, [Minutes of adjustment] is a column with the USERTZOFFSET() value. I needed to use abs() to make the negative numbers positive values, otherwise I wouldn’t be able to use the result to compute the new time because the format would get messed up. I then set up an if() formula to add if the value was positive and subtract if it was negative.

Steve

Kirk_Masden

Thanks @Steve! I think you’re telling me that the answer is in the TEXT() article but I’m not seeing it there. The USERTZOFFSET() value produces fairly big numbers ("-540" for Japan) that are numbers of minutes and not in a date format. If there’s a quick and easy way to convert “-540” to the duration “09:00:00” I wonder if you could help me out by showing me how to do it.

Steve

Sorry–I just skimmed your post, saw you appeared to be trying to format a date, and went with that. My bad.

To compute the device’s “UTC adjustment”, a Duration value that can be added to an arbitrary local DateTime value to get the corresponding UTC DateTime value:

(UTCNOW() - NOW())

Then:

([ChangeTimestamp] + (UTCNOW() - NOW()))

or, if stored in column UTC Adjustment:

([ChangeTimestamp] + [UTC Adjustment])

Honestly, I can’t imagine a good use for USERTZOFFSET() since I realized we could easily compute the offset as a readily-usable Duration.

Kirk_Masden

Thanks much, @Steve! I always appreciate your help and expertise.

I agree with you that a USERTZOFFSET() would be much more useful if it were produced in a readily-usable Duration.

I see now that your much simpler solution will probably work in my case and allow me to avoid USERTZOFFSET().

I’ll play around with these. My only concern about UTCNOW() is that I’ve read that it is recorded as the server time but I want the device time (for instances where the sync has be delayed, I want to know when the user tapped something, not when it got written):

Steve

I don’t believe that’s accurate.

Kirk_Masden

Indeed. My testing indicates that it isn’t true. I put my phone on airplane mode, used my app, then synced. The UTCNOW() times were those of my phone.

Kirk_Masden

Hi again @Steve!

After having tested your (UTCNOW() - NOW()) alternative for USERTZOFFSET() I would concur that (UTCNOW() - NOW()) works perfectly and that USERTZOFFSET() causes more problems than it solves because it renders the difference between the device’s time and UTC time in minutes, rather than a ready-to-compute format.

In regard to the original topic of this thread (how to add minutes to a timestamp) I’d like to offer the following expression as a solution:

concatenate(
if([Minutes]>0,"","-"),
left(“00”,2-len(text(floor(abs([Minutes])/60)))),
floor(abs([Minutes])/60),
“:”,
left(“00”,2-len(text(mod(abs([Minutes]),60)))),
mod(abs([Minutes]),60),
“:00”
)

It’s a bit convoluted, but it’s the best I could come up with in AppSheet. It works with both positive and negative values and values significantly larger than 60, such as those produced by USERTZOFFSET(). On a Google spreadsheet, the following will work:

=time(0,540,0)

The only problem is that the minute value has to be absolute so one needs another way to switch times to negative when that’s what’s called for. I’ve done it with an IF() formula.

Thanks again for your help with this, @Steve! I hope this can be a reference for others who have to deal with similar issues.