How to Use Math in Date Values

Goal

Better understand how Datameer does math operations over date objects.
 

Learn 

By default Datameer displays date values like MMM d, yyyy HH:mm:ss AM/PM, without milliseconds, e.g. May 1, 2018, 12:00:00 AM. However, the math with date values is being done in ms, so under the hood when you compare two dates in a join operation or add/subtract # of days, the machine works with the integer values of milliseconds.

For example:

STARTOFMONTH() function result is displayed by default as the start of the first day of the month, e.g. May 1, 2018, 12:00:00 AM, which is 2018-05-01 00:00:00.000.

ENDOFMONTH() function result is displayed by default as the end of the last day of the month, e.g. Apr 30, 2018, 11:59:59 AM, which is 2018-04-30 23:59:59.999.

When you generate some data using a formula e.g. ENDOFMONTH(#A)+1s. it shows the result as May 1, 2018, 12:00:00 AM, which looks similar to May 1, 2018, 12:00:00 AM generated by STARTOFMONTH() function.

However, if fact this is 2018-05-01 00:00:00.999 from ENDOFMONTH(#A)+1s and 2018-05-01 00:00:00.000 from STARTOFMONTH(#A). It would be obviously when you try to join two sheets using these date values as there are no results.

In order to avoid any mismatches in date value calculation results, it is recommended to use the ROUNDTIME function whenever possible to ensure that dates are in the expected format.

Alternatively you could also use the FORMATDATE function in the formula formatdate(<Sheet Name>;"yyyy-MM-dd HH:mm:ss.SSS") to check if the millisecond value is expected.