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

(Multi Tech Visions) #1

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) #2

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

(Grant Stead) #3

Especially important when you’re ignoring seconds

(Multi Tech Visions) #4

@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) #5

@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() )

(Multi Tech Visions) #6

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

(Grant Stead) #7

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

(Kirk Masden) #8

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 :wink:

).

(Stephen Mattison) #9

@Kirk_Masden

Sweet, thanks!

Useful!

(Aleksi Alkio) #10

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].

1 Like
(Stephen Mattison) #11

@Aleksi_Alkio

Another Wizardly solution! TYTY! Translate