What Working on Calendar Plus has Taught me about Ninox Date Fields

By Adam Davidson, Senior Developer

12/08/2020

If you’ve worked with Ninox date fields chances are you’ve noticed that they don’t always work as you might expect.  If you haven’t read David’s article titled “Do As Server – Chapter 2″ in the October 27th issue of our newsletter I highly recommend you check it out here as this article expands on some of the stuff that was mentioned there. 

Here are the biggest things that tripped me up about date fields when developing Calendar Plus.  You probably know about some of them already.  A lot of these things are equally applicable to date-time and appointment fields, which are both stored in the same way.  I don’t have the space to provide solutions to these issues here, but my hope is that a knowledge of what is going on with dates behind the scenes will help you work around the weirdness!

  1. Dates are stored as Unix timestamps behind the scenes and include a time component which is not visible in the date field. 

As an example, if I set a date field to December 10th 2020 here in New York, David looks at it in Dallas Texas (1 hour behind) and sees December 9th 2020. If he were to add a formula field with the code:

							
					format('Date Field', "YYYY-MM-DD hh:mma");

He would see: 2020-12-09 11:00pm. The timestamp was set to midnight on the 10th New York time,  and it has been read in his local time (1 hour behind), and displayed without the time component. AH! 

  1. Dates are accessed in local time when code is executed on the browser, and in server time when code is executed on the server.

This is explained in David’s article linked to above. As an example of this, if I in NYC place a button on my record with the following code:

							
					do as server 'Date Field' := date(2020, 12, 10) end;

and click the button, I will see the date field change to 12/09/2020.  This is because my database is on the public cloud, and the code is being executed in Germany. The situation is the same as if someone has gone into my database in Germany and changed the date to the 12th, resulting in the same problem we saw in number 1.  David shows a neat way to address this using the format() function in his article linked to above.  

  1. Trigger after update and Trigger on create functions are always executed on the server.  

This is something that is very useful to know given the above information. As an example of how this affects dates, let’s say I insert the following code into a trigger after update function on ‘Date Field’:

							
					'Date Field' := date('Date Field')

then I set the date field to 12/10/2020. What do you think will happen? This is a bit of a tricky one to think through, but the date is immediately changed to 12/09/2020. Any date that I choose gets changed to the previous day. Here’s why:

  1. When I click on the date in NYC, first it sets the Unix timestamp to midnight of the chosen date, NYC time, which is 6am on that day German time.
  2. Then the trigger after update function runs on the server in Germany, and reads the time as 6AM on the 10th. 
  3. The date() function then takes this timestamp and lops off the 6am part, changing the timestamp to midnight of the 10th German time. 
  4. When that value is sent back to my browser, it is changed back into my local time, which is 6pm NYC time, on the previous day.  AH!
 

In general if you are manipulating dates in script you need to know exactly where your code is being run.  Trigger after update and Trigger on create functions are always run on the server, and there is no way around it.  On click and formula functions are run locally (on your computer) unless you specify “do as server”.  Global functions are run wherever you call them, unless the global function itself includes “do as server”. 

  1. Don’t forget about the time shift!

I’ve seen this come up in a couple of circumstances.  A big one  is when adding a day or days  to a date.  Let’s say we have this code in a button: 

							
					let firstDate := date(2020, 10, 1);
let secondDate := date(2020, 11, 1);
alert(text(firstDate + 1) + " " + text(secondDate + 1));

When we click the button we see an alert that reads “10/02/2020 11/01/2020”.  A day was added to the first date, but it doesn’t look like the second one was affected!  This is because of the time shift on November 1st 2020 in New York.  The results may be different where you are, depending on when your time shift occurs.  By changing date + 1 to date(date + 1.5) this issue can be avoided.