Conditional Formatting Calculation Using Timestamp and Current Date/Time

[jeffamm]

Filemaker Pro Advanced 14.0.6, running as a database on my PC

On a summary layout I display timestamp fields from various related tables and want to conditionally format them with a color to indicate that they are current within the last 2 days (green) or older (red).  I don't need a precise answer for this, +/- a day is OK.

Logically I know the calculation is current date/time minus timestamp, with a greater/less than calculation to change colors

I have tried Get (CurrentDate) - Timestamp, with the calculation result set to "number" and I get junk numbers, e.g.

Get (CurrentDate)              Timestamp                         Calculation Result

3/2/21 12:00 AM                 2/28/21 9:05:44 AM          140056

3/2/21 12:00 AM                3/2/21 12:12:21 AM             -36732

The negative sign is correct since the second case is newer than the current date but the magnitude of the 12 minutes old answer is too large compared to the 2 days old answer to represent seconds, minutes or something else.  Unless I'm missing something.

I assume I need to convert the timestamp to use the same format as get (currentdate) format but not sure what to do.  Can anyone point me in the right direction?

thanks

Jeff       

Edited March 2, 2021 by jeffamm

[comment]

The data you show in the leftmost column of your table is incorrect. The Get ( CurrentDate ) function returns a date, not a timestamp. It will never return a value of 3/2/21 12:00 AM .

Date and timestamp are two completely different data types. When you convert a date to a number, the result is the number of days elapsed since 1/1/0001. When you convert a timestamp to a number, the result is the number of seconds elapsed since 1/1/0001 0:00. Consequently subtracting a timestamp from a date is meaningless.

To find out how "old" a timestamp is, subtract it from Get ( CurrentTimestamp ). The result here will be the difference in seconds. If you want to see if more than two days have passed, look for a difference greater than 172,800 seconds.

Alternatively, you could convert the timestamp to a date using the GetAsDate() function, then subtract it from Get ( CurrentDate ).  But then your result will be less accurate: a timestamp taken towards the end of the day will have the same age as one taken during early morning.

 

[jeffamm]

Hi, thanks for the reply.  I missed that there was a Get ( CurrentTimestamp ) function so thanks for pointing that out.  FYI, the Get ( CurrentDate ) does show 3/2/2021 12:00 AM for me.  I have the calculation result set to timestamp if that changes anything, I'd be curious to know if it works differently for you.

With the Get ( CurrentTimestamp ) I get the following

Get ( CurrentTimestamp )        Timestamp                          Calculation Result

3/2/2021 11:32:33 AM              3/2/2021 10:12:12 AM         4821

So that is working very well, thanks again for the assistance

[comment]

3 minutes ago, jeffamm said:

I have the calculation result set to timestamp if that changes anything

Yes, that does change things. Because setting the result type to timestamp is equivalent to wrapping the calculation's formula in GetAsTimestamp(). Since you have the Advanced version, I suggest you compare these two in the Data Viewer:

Get ( CurrentDate ) 

vs.

GetAsTimestamp ( Get ( CurrentDate ) )

and even better:

GetasNumber ( Get ( CurrentDate ) ) 

vs.

GetasNumber ( GetAsTimestamp ( Get ( CurrentDate ) ) )