MongoDB Extension - timezones


#1

I’m having trouble getting datetime to save consistently to our cloud mongoDB cluster using the extension at

When querying the database for this record (raw data as seen by compass) 2018-06-29-00:28:29
The response from both cursor.toArray() or cursor.next() looks like {ts ‘2018-06-29 07:28:29’}

As if the mongo extension has applied a transform of +7:00 to the date fields, presumably an unecessary PDT -> UTC conversion.

Running as docker container on my local machine (Docker for Windows /w linux containers) -> CommandBox lucee 5.27

Lucee server set to UTC - and reporting time as expected.
commandBox JVM is set to UTC using jvmarg -Duser.timezone=UTC
Note that container bash reports time correctly.


#2

Similar thing happens when saving data.
I send this date
image

and mongoDB ends up with 2018-06-29 17:35:08.000


#3

Forgive me for interfering in this discussion. :blush:
I’m curious about what you use MongoDB for and what (real) advantages you have over a relational database, such as mysql.


#4

We are using Mongo to power non-transactional CRUD stuff.

There are many answers to this question if you do some research.
For me, the impact is felt in 2 major ways.

  1. It allows for faster iterative development in an environment where specifications are shifting, or have not quite been defined yet. Example: you’ve developed an interface + model for a client, and they’re changing their minds about stuff or adding features every other week.
    There’s no need to commit a plethora of sql alter / drop scripts. Simply save out your model differently, lets say we end up adding a new key to a collection (table). There’s nothing to deploy to the database itself. In a relational database you got to worry about whether a new column exists, before your code attempts to query for that column. Lets say you drop an entire collection and existing live code is still querying for that data. Nothing breaks, you just end up fetching no data. Also, due of the lack of coupling, there are unlikely to be a plethora of existing relationships that need to be honored or are brittle.

  2. It shifts the way developers thinks about modelling. There’s a saying in the Mongo ecosystem about modelling your data the way you’d want to query it. When modelling out a product review system (e-commerce) in a relational db environment, you’ll probably end up relating at least 3 tables (normalized). In the NoSQL camp, you’d ask, what’s the most common way that user data will be queried? Maybe instead of relationships between reviews, products, and customers (reviewer), we could just store the customer data on the Review itself. Maybe the reviews are modeled into the products altogether. The resulting models and relationships will tend to be simpler, faster, and more durable (of course, at the expense of normalization and data integrity). But did we really ever need customer data on a review to be perfectly synced with the actual customer record?


#5

Thank you for your explanation!


#6

In which timezones are your server running?
Are MongoDB and Lucee both in UTC?


#7

MongoDB doesn’t have a timezone setting and saves naked dates meant for use as UTC (afaik).
Lucee is UTC.


#8

Correct, i forgot it, i dont use mongodb often.
However, i tested it on my side and its working fine. (Server running in Timezone UTC+2 and Lucee in UTC).
When inserting and reading a Date i got the utc as expected.
So maybe its a Docker or Commandbox (Configuration) problem.
Are you using the latest Lucee/Java and MongoDB-Extension?

Here is my testscript and the result:

<cfscript>
	//Connect
	db = MongoDBConnect("testdb","mongodb://admin:admin@localhost:27017");
	//Insert Now
	db.getCollection("test_tz").insert(
		{
			first_name: "Test1",
			dt_created: Now()
		}
	);
	//Dump Data
	dump(db["test_tz"].find().toArray());
</cfscript>


#9

So strange.
I used your same logic, spun up a fresh local mongo instance, and ended up with mismatching dates.
image

One thing that’s probably different about our environments is that I’m using a lex file built from the latest on github/lucee/extension-mongodb to use mongo 3.6
(3.6.3 lex)
which is not yet availale from download.lucee.org/?type=ext


#10

I installed it from the webinterface. (version: 3.4.2.59)
But i also used a older MongoDB Version (3.4.9).
One thing you could try is to set the timezone in your application.cfc, maybe your web-context dont get the correct timezone.

this.timezone = "UTC";

#11

Found the issue. I should not be performing data verification using Compass.
Apparently when viewing your datetime data using Compass, their data exploration tool, Compass applies your system timezone to the displayed data.

For example, when viewing this record in compass
image
The actual value in Mongo for created and modified is [2018-07-05 19:30:08.935] despite what compass is showing.

Here’s a jira link to the Compass issue
https://jira.mongodb.org/browse/COMPASS-115