Change Date Format

Ask for Help
21

Ideally, yes. I figure if I can break the calendar/time info into components starting from a Glide calendar column, then I can use a template to compose the final correct format for my use case. That first step–pulling out the separate components is what’s vexing me at present.

All constructive input welcome!

22

hmm yea I was thinking of using a template for the final result, but not sure how to get the date and time in that format.

Maybe this would help? Looks like it based on where you are located.

23

You can use something like this in a math column. Might need some massaging if you need leading zeros on a single digit minute or if you also need year, but that should be close to what you want.

MINUTE(date)*1000000+HOUR(date)*10000+DAY(date)*100+MONTH(date)
24

Thanks, that gets closer…
I’m trying to convert to Unix crontab format, so padding for leading zeros would be useful. Any suggestion on that?

Thanks!

25

That’s where it gets kind of gross. Glide currently doesn’t have any padding functionality, but there are a couple of ways to trick it.

  • This way requires an IF/THEN column to see what the range of numbers is, and then combines with a template column, it appends the leading zeros.
    Auto Numbering - #12 by Darren_Murphy

  • This is another way that calculates the number into decimal form, then you use a template and replacements to remove the decimal and anything to the left, but the last time I tried this, I could only replace with a column that contained a space, so I’m not sure if it would properly trim the result. Plus the precision starts to kick in on a math column, so anything beyond 4 decimal places would be lost.
    Auto Numbering - #17 by Jeff_Hager

  • The last option I can think of is to “prefix” the number by adding 999900000000 and then using a template column to replace 9999 with a blank. Again, I’m not sure if it properly trims the result, but now that I think about it, I guess if the column you are using for the replacement is just full of null values instead of spaces, then it should work. Same with the second method above.

(MINUTE(date)*1000000+HOUR(date)*10000+DAY(date)*100+month(date))
+
999900000000

So in the image below, the first column is the date. The second column is an empty column (null values). (It’s user specific in this image, but it doesn’t have to be.) The third column is the math column. The fourth column is a template column to convert the math column into a string. The fifth column is a template column that uses column W as the template and replaces ‘9999’ with the null value in column T.

image
image878×390 40.5 KB

3 Likes
26

From man 5 crontab:

minute        0-59
hour          0-23
day of month  1-31
month         1-12 (or names, see below)
day of week   0-7 (0 or 7 is Sun, or use names)

So you also need day of week?

1 Like
27

Jeff, you rock!

Really, I’m very appreciative of your reply and not just for the cleverness of your suggestion, but the fact that you’ve provided such a thorough, example-filled response.

Thank you!!

3 Likes
28

Hi all, here’s a solution for the time computation, which potentially could serve as the basis for cross platform push notification solution for Glide:

Several services, such as ClickSend, permit scheduled SMS delivery through an https:// API (in addition to more full featured RESTful JSON payloads). To use most of these requires conversion of human readable time/date data into Unix time, which is computed approximately as the seconds elapsed from midnight on the date Jan 1, 1970 (latter known somewhat erroneously as Unix Epoch–Unix actually went live in 1972). I say ‘approximately’, because Unix time skips leap seconds; for applications not requiring atomic clock accuracy, you should be fine.

Using a simple array formula, it’s easy to set up a column with that 1970 date. You can then perform a simple date-time subtraction, and multiply this by 86,400, which converts days into seconds (required for Unix time). The Glide date-time calculation carries forward the fractional days elegantly, so the user does not have to separately add back in hours and minutes–that’s a very nice freebie.

However, it’s important to remember that Unix time is based on UTC, formally GMT (Greenwich Mean Time in the UK). So for example, if one is operating in the Eastern US, one has to add in the 5 hour (EST-UTC) timezone difference; in seconds, that’s 18,000.

So, Unix time = ((Glide_event_start_time - Unix_Epoch_date)*86400) + 18000 [EST only]

That works just fine, and I’ve checked this against Unix Time stamp servers. It’s nice that Glide handles all the fractional day (min, sec) portions elegantly.

Hope this helps. My aim is that using an OpenLink action to call an SMS service one can schedule a message at the appropriately scheduled time. Voila, push notification.

Hope this helps…

2 Likes