This is either a tips and tricks post or it’s a call to make this better. Could go either way!
Here’s an app that demonstrates “time ago”. Is there a better way to do this?
First we need a calc named “Time ago in Seconds” which is a unix timestamp derived from a Datetime field called “Timestamp”:
((HOUR(DATE(NOW()) - “01/01/1970”))*3600 + (MOD(HOUR(NOW() - “01/01/1970”),HOUR(DATE(NOW()) - “01/01/1970”)))*3600 + MINUTE(NOW() - “01/01/1970”)*60 + SECOND(NOW() - “01/01/1970”)) -
((HOUR(DATE([Timestamp]) - “01/01/1970”))*3600 + (MOD(HOUR([Timestamp] - “01/01/1970”),HOUR(DATE([Timestamp]) - “01/01/1970”)))*3600 + MINUTE([Timestamp] - “01/01/1970”)*60 + SECOND([Timestamp] - “01/01/1970”))
Then we need a IFS() calculation that looks like so:
ifs(
[Time ago in Seconds] < 0, "hasn't happened yet, why worry?",
[Time ago in Seconds] < 5, "just now",
[Time ago in Seconds] < 15, "a few seconds ago",
[Time ago in Seconds] < 60, [Time ago in Seconds] & "s ago",
[Time ago in Seconds] < 3600, [Time ago in Seconds]/60 & "m ago",
[Time ago in Seconds] < 86400, ([Time ago in Seconds]/60)/60 & "h ago",
[Time ago in Seconds] < 604800, (([Time ago in Seconds]/60)/60)/24 & "d ago",
[Time ago in Seconds] < 2419200, ((([Time ago in Seconds]/60)/60)/24)/4 & "w ago",
[Time ago in Seconds] < 29030400, ((((([Time ago in Seconds]/60)/60)/24)/4)/12)+1 & "mo ago",
[Time ago in Seconds] >= 29030400, ((((((([Time ago in Seconds]/60)/60)/24)/4)/12)+1)/12)+1 & "y ago"
)
Open to ideas here… thanks for reading.
TOTALSECONDS(NOW() - [Timestamp])
While not a Unix timestamp calc, it should produce the same result.
that’s way cleaner, thank you!!
There’s so many of these time-saving formulas that have been snuck into the system throughout the years.
A very useful concept as usual from you @TyAlevizos. Thank you.
If I may say, in essence, you have nicely converted the cryptic computer science language on the left-hand side of the expression into an art of human-friendly readable language on the right hand side.