Select to view content in your preferred language

String with concat of Open-Close hours from Time type issue in Web App - can't get minutes!

131
1
Jump to solution
a month ago
Teresa_Blader
Frequent Contributor

Desired Output: HH:MMAM-HH:MMAM
Type: text
FieldType: String

Inputs:
${time_open_Sunday}
${time_close_Sunday}
Type: time
FieldType: null

In the S123 Connect Designer this works and returns what I expect:

concat(if((format-date(${time_open_Tuesday}, '%H') * 1) > 12, string((format-date(${time_open_Tuesday}, '%H') * 1) - 12), if((format-date(${time_open_Tuesday}, '%H') * 1) = 0, "12", string(number(format-date(${time_open_Tuesday}, '%H'))))), ":", format-date(${time_open_Tuesday}, '%M'), if((format-date(${time_open_Tuesday}, '%H') * 1) >= 12, "PM", "AM"), "-", if((format-date(${time_close_Tuesday}, '%H') * 1) > 12, string((format-date(${time_close_Tuesday}, '%H') * 1) - 12), if((format-date(${time_close_Tuesday}, '%H') * 1) = 0, "12", string(number(format-date(${time_close_Tuesday}, '%H'))))), ":", format-date(${time_close_Tuesday}, '%M'), if((format-date(${time_close_Tuesday}, '%H') * 1) >= 12, "PM", "AM"))


But "format-date" returns "invalid date" or "NaN" in S123 Web App... 😐 So it sounds like Designer/Field App converts time to Epoch, but web app turns it to 13:36:00.000-05:00-15:34:00.000-05:00

So alternatively tried this which returns the hours, but it won't return and concat the minutes and I'm going crazy: 1:PM-3:PM. I've tried so many iterations.

concat(
  if(substr(${time_open_Sunday}, 0, 2) > 12, substr(${time_open_Sunday}, 0, 2) - 12, if(substr(${time_open_Sunday}, 0, 2) = 0, 12, substr(${time_open_Sunday}, 0, 2))),":", substr(${time_open_Sunday}, 3, 2),if(substr(${time_open_Sunday}, 0, 2) >= 12, "PM", "AM"),"-",
  if(substr(${time_close_Sunday}, 0, 2) > 12, substr(${time_close_Sunday}, 0, 2) - 12, if(substr(${time_close_Sunday}, 0, 2) = 0, 12, substr(${time_close_Sunday}, 0, 2))),":", substr(${time_close_Sunday}, 3, 2), if(substr(${time_close_Sunday}, 0, 2) >= 12, "PM", "AM")
)

tried setting to int...

concat(
  if(int(substr(${time_open_Sunday}, 0, 2)) > 12, int(substr(${time_open_Sunday}, 0, 2)) - 12, if(int(substr(${time_open_Sunday}, 0, 2)) = 0, 12, int(substr(${time_open_Sunday}, 0, 2)))),
  ":",
  substr(${time_open_Sunday}, 3, 2),
  if(int(substr(${time_open_Sunday}, 0, 2)) >= 12, "PM", "AM"),
  "-",
  if(int(substr(${time_close_Sunday}, 0, 2)) > 12, int(substr(${time_close_Sunday}, 0, 2)) - 12, if(int(substr(${time_close_Sunday}, 0, 2)) = 0, 12, int(substr(${time_close_Sunday}, 0, 2)))),
  ":",
  substr(${time_close_Sunday}, 3, 2),
  if(int(substr(${time_close_Sunday}, 0, 2)) >= 12, "PM", "AM")
)

so I'm stumped. I can't even get this work in the web app no matter what I do:

substr(${time_open_Sunday}, 3, 2)

 

Teresa Blader
Olmsted County GIS
GIS Analyst - GIS Solutions
0 Kudos
1 Solution

Accepted Solutions
Teresa_Blader
Frequent Contributor


Wow!! With the help of Copilot AI and my suggesting decimal-time and watching for rounding this is what we came up with to concat two time question types together in a range. This seems very intense!!! 😭😮😎 

Stored output is now: 3:04PM-3:12PM

Teresa_Blader_0-1742501511662.png

if(decimal-time(${time_close_Monday}) > 0,
   concat(
      if(int(decimal-time(${time_open_Monday}) * 24) > 12, 
         int(decimal-time(${time_open_Monday}) * 24) - 12, 
         if(int(decimal-time(${time_open_Monday}) * 24) = 0, 12, int(decimal-time(${time_open_Monday}) * 24))),
      ":",
      if(int(((decimal-time(${time_open_Monday}) * 24 + 0.0001) - int(decimal-time(${time_open_Monday}) * 24)) * 60) < 10, 
         concat("0", int(((decimal-time(${time_open_Monday}) * 24 + 0.0001) - int(decimal-time(${time_open_Monday}) * 24)) * 60)), 
         int(((decimal-time(${time_open_Monday}) * 24 + 0.0001) - int(decimal-time(${time_open_Monday}) * 24)) * 60)),
      if(int(decimal-time(${time_open_Monday}) * 24) >= 12, "PM", "AM"),
      "-",
      if(int(decimal-time(${time_close_Monday}) * 24) > 12, 
         int(decimal-time(${time_close_Monday}) * 24) - 12, 
         if(int(decimal-time(${time_close_Monday}) * 24) = 0, 12, int(decimal-time(${time_close_Monday}) * 24))),
      ":",
      if(int(((decimal-time(${time_close_Monday}) * 24 + 0.0001) - int(decimal-time(${time_close_Monday}) * 24)) * 60) < 10, 
         concat("0", int(((decimal-time(${time_close_Monday}) * 24 + 0.0001) - int(decimal-time(${time_close_Monday}) * 24)) * 60)), 
         int(((decimal-time(${time_close_Monday}) * 24 + 0.0001) - int(decimal-time(${time_close_Monday}) * 24)) * 60)),
      if(int(decimal-time(${time_close_Monday}) * 24) >= 12, "PM", "AM")
   ),
   ""
)

 

Teresa Blader
Olmsted County GIS
GIS Analyst - GIS Solutions

View solution in original post

0 Kudos
1 Reply
Teresa_Blader
Frequent Contributor


Wow!! With the help of Copilot AI and my suggesting decimal-time and watching for rounding this is what we came up with to concat two time question types together in a range. This seems very intense!!! 😭😮😎 

Stored output is now: 3:04PM-3:12PM

Teresa_Blader_0-1742501511662.png

if(decimal-time(${time_close_Monday}) > 0,
   concat(
      if(int(decimal-time(${time_open_Monday}) * 24) > 12, 
         int(decimal-time(${time_open_Monday}) * 24) - 12, 
         if(int(decimal-time(${time_open_Monday}) * 24) = 0, 12, int(decimal-time(${time_open_Monday}) * 24))),
      ":",
      if(int(((decimal-time(${time_open_Monday}) * 24 + 0.0001) - int(decimal-time(${time_open_Monday}) * 24)) * 60) < 10, 
         concat("0", int(((decimal-time(${time_open_Monday}) * 24 + 0.0001) - int(decimal-time(${time_open_Monday}) * 24)) * 60)), 
         int(((decimal-time(${time_open_Monday}) * 24 + 0.0001) - int(decimal-time(${time_open_Monday}) * 24)) * 60)),
      if(int(decimal-time(${time_open_Monday}) * 24) >= 12, "PM", "AM"),
      "-",
      if(int(decimal-time(${time_close_Monday}) * 24) > 12, 
         int(decimal-time(${time_close_Monday}) * 24) - 12, 
         if(int(decimal-time(${time_close_Monday}) * 24) = 0, 12, int(decimal-time(${time_close_Monday}) * 24))),
      ":",
      if(int(((decimal-time(${time_close_Monday}) * 24 + 0.0001) - int(decimal-time(${time_close_Monday}) * 24)) * 60) < 10, 
         concat("0", int(((decimal-time(${time_close_Monday}) * 24 + 0.0001) - int(decimal-time(${time_close_Monday}) * 24)) * 60)), 
         int(((decimal-time(${time_close_Monday}) * 24 + 0.0001) - int(decimal-time(${time_close_Monday}) * 24)) * 60)),
      if(int(decimal-time(${time_close_Monday}) * 24) >= 12, "PM", "AM")
   ),
   ""
)

 

Teresa Blader
Olmsted County GIS
GIS Analyst - GIS Solutions
0 Kudos