Hello !

Coming out of an interesting session on the OpenNTF slack channel today, I thought it would be a good idea to blog about our findings, before someone else false into the same trap(s).

What's the topic ?

DateTime-Values and their handling by domino-db/proton.

Why should you care ?

Well, if you're a LotusScript or Java Developer with Domino - not so much. If you're using the AppDevPack - well, maybe you should.

What's up ?

Timezones in one word. Imagine you have this code fragment to save a DateTime value to a Notes Document using domino-db:
(I'm using moment.js in my node code)

Datetime: {
type: 'datetime',
data:  moment(momentTZ().tz("Europe/Berlin")).format('YYYY-MM-DDTHH:mm:ss.SS');

You will run into two issues here. One is fairly obvious - domino-db will let you know that the you have a BAD_TIME value here:

at new DominoDbError (~\app\node_modules\@domino\domino-db\src\domino-db-error.js:6:16)
at wrapError (~\app\node_modules\@domino\domino-db\src\requests\grpc\utils\grpc-helpers.js:157:10)
message: 'Bad time value',
cause: Error [ProtonError]: Bad time value
at makeProtonError (~\app\node_modules\@domino\domino-db\src\requests\grpc\utils\grpc-helpers.js:74:17)
at ~\app\node_modules\@domino\domino-db\src\requests\grpc\utils\convert-from-proto.js:305:35
at Array.forEach (<anonymous>)
at commonResponseFromProto (~\app\node_modules\@domino\domino-db\src\requests\grpc\utils\convert-from-proto.js:298:15)
at createResponseFromProto (~\app\node_modules\@domino\domino-db\src\requests\grpc\utils\convert-from-proto.js:335:3)
at createSingleDocumentConverter (~\app\node_modules\@domino\domino-db\src\requests\grpc\utils\convert-from-proto.js:363:18)
at ~\app\node_modules\@domino\domino-db\src\requests\grpc\utils\bulk-document.js:245:21
at Object.onReceiveStatus (~\app\node_modules\grpc\src\client_interceptors.js:1212:9)
at InterceptingListener._callNext (~\app\node_modules\grpc\src\client_interceptors.js:568:42)
at InterceptingListener.onReceiveStatus (~\app\node_modules\grpc\src\client_interceptors.js:618:8)
at callback (~\app\node_modules\grpc\src\client_interceptors.js:847:24) {
code: 65545,

Why is that ? Let's check the documentation:

Of course, there may be situations where your application needs to convert between a JavaScript Date and a Domino DATETIME. For example, let's assume you want to write a document item of type DATETIME where the value is equal to myDate (and myDate is an instance of Date). You might be tempted to use toISOString() like this:

Datetime: {
type: 'datetime',
data: myDate.toISOString()

Unfortunately, toISOString() writes a value with millisecond precision. Therefore, when you use Database.createDocument() to write a document containing the above item, the request will fail. You must format myDate to be no more precise than a hundredth of a second. In other words:

'2013-03-01T14:09:01.009Z' // Invalid (too precise)
'2013-03-01T14:09:01.01Z' // Valid

Usually, this means you will want to use an existing Node.js package to format and parse date values for use by domino-db. For example, moment is one good package for handling dates, but ultimately it is up to you to find the package that meets your needs.
HINT: If you are using moment, use the following format string for parsing and writing date values: 'YYYY-MM-DDTHH:mm:ss.SS[Z]'.


So this means, we have to change our code to this:

Datetime: {
type: 'datetime',
data:  moment(momentTZ().tz("Europe/Berlin")).format('YYYY-MM-DDTHH:mm:ss.SS[Z]');

This will write our DateTime Value to Notes without the error above.

So far so good. Now, let's look at our Date/Time value in the written document in Notes.

Remember - we created a DateTime value in Node in GMT+1, so e.g. 12pm GMT+1.

What got saved ?

12pm GMT.

See the next problem ?  

Your Date/Time field in Notes is off by one hour.

Why is that ? After talking to development, this results from the C-APIs the proton task is calling when creating the items. The C-API *ALWAYS* stores Date/Time Values with GMT as the time zone, without converting the incoming value to GMT. And domino-db returns a GMT Timestamp as we re-read the document and the item. If we convert this to GMT+1 now, we get 1pm back, which is wrong !

Currently, we can't send any time zone parameter using domino-db/proton so our Node applications have to take care of this themselves, maybe by storing the time zone in an additional item on save and re-creating the appropriate timestamp from there on re-use.

The Notes Calendar btw. converts these dates automatically from GMT to the local time zone . So that's another can of worms to keep in mind. I will have to check the return values after the Notes Client changed the date fields - we will get back the converted time stamp with no time zone definitions whatsoever. Ugly.

In summary, be aware of these problems if you are dealing with multi time zone apps using domino-db/proton or with time zone aware apps of any kind.