MongoDb BSON stores Date in UTC time

Question

If I try to put a date field in a Document (BSON) and write it to Mongo, BSON writes it in UTC. For example, a date

DateTime dateTime = new DateTime("2015-07-01");
Document doc = new Document("date", dateTime.toDate());

will be stored as

"date" : ISODate("2015-06-30T18:30:00Z")

in Mongo. And, if I retrieve it using the same Java Driver I get it as

Wed Jul 01 00:00:00 IST 2015

Great. Is there no solution to this? I mean, why can't I store my date as I want it? What If I need to query on the DB from another time zone? I will be getting different results? Date field is an important part of Mongo with a rich set of operators wrapped around it. Still, why doesn't Mongo provide this flexibility? Thanks

Sissel

"What If I need to query on the DB from another time zone?" What exactly do you mean? Basically, the Date type represents an instant in time, internally stored as milliseconds since the Unix epoch. If you don't want to store an instant in time, Date isn't going to help you :( – Masterson

Well, in general it's better to keep dates as UTC in database already. Maybe you should do timezone staff in your api side. – Marchal

@JonSkeet I have inserted the date field from IST zone. What If I need to query the DB from a different time zone? Won't I be getting the corresponding date-time in that time zone? How can I store the date as it is? – Sissel

Just repeating "What If I need to query the DB from a different time zone?" doesn't actually tell me what you mean by that. The date value doesn't have any knowledge of a time zone - it's just an instant in time. Likewise any query you perform will just compare that value with another instant in time. I don't know what you mean by "as it is" in this case, but you need to understand what the Date type means. It's not clear what behaviour you're trying to achieve. – Masterson

@JonSkeet Maybe the instant-in-time storage is causing my problem. I need to store the date as "2015-07-01". And would like to query for the same from India or London and get the result as "2015-07-01" in both cases. Maybe Mongo isn't my use-case db :( – Sissel

Indeed - you'll either have to store it as a different type, or parse your original date/time in UTC (i.e. store 2015-07-01T00:00:00Z). – Masterson

Answer 1

I have the problem solved by setting my timezone as UTC from within the code itself.

    DateTimeZone zone = DateTimeZone.UTC;
    DateTimeZone.setDefault(zone);

Sissel

Answer 2

IMO, mongo did everything right. You instantiate date using your local timezone and then store it in mongo in UTC. And then when you ask mongo to retrieve it for you it shifts date to your local timezone again.

If you dont want to deal with timezone shifting, just set your local timezone to UTC using the following flag:

-Duser.timezone="UTC"

Olympie

@AswinJoseRoy set this as your apllication launch parameters i.e. java -jar yourApp.jar -Duser.timezone="UTC" – Olympie

IMO mongo did not do everything right. I want to persist a day. Meaning a day in time. I don't want to persist a point in time, that is represented at different days depending on the timezone. If I want to do that, I store a Time object. – Breathe

@Breathe Sorry, that's not what the datetime does... in Python or BSON. It doesn't store a date, it doesn't store a timeoffset or anything else. You are asking for a datatype that is just not naturally supported in BSON. The good news is: you can roll your own. Use a string. Use an integer. Use an object like {"$date": "2022-02-09"}. – Turbidimeter

Answer 3

When using DateTime (from org.joda.time or java.time) a date with time zone is created and MongoDB has unfortunately no support for it. (see BSON Types)

Therefore persisting it in UTC is a easy and reliable solution without loosing any data.

As a alternative you should take a look at LocalDateTime. It contains no DateTimeZone and so nothing has to be converted first.

Fabric

If you meant org.joda.time LocalDateTime, it's not working either. – Sissel

Answer 4

Got a very simple solution. Let the date be in UTC in your MongoDB.
Whenever you fetch it to your UI, just do:

new Date(fetchedDate);

It converts it to the local date.

Natasha

Answer 5

I was facing the same issue while retrieving the date from MongoDB. MongoDB follows the UTC timezone that's why when you perform any date operations, it converts into UTC.

So I used post hook from mongoose to convert the date and time in my preferable timezone. Here is the snippet.

schema.post(['find', 'findOne'], async function(docs, next) {
    docs.map((doc) => doc.createdAt= convertToTz({tz: process.env.TZ, date: doc.createdAt}))
    next();
});

Here, createdAt is the date when the document is created. convertToTz is a common function that I mentioned below.

const moment = require("moment-timezone");

const convertToTz = (params) => {
  try {
        let tz = params?.tz ?? process.env.TZ;
        let format = params?.format ?? "";
        return moment(params.date).tz(tz).format(format);
    } catch (error) {
       throw new Error(error);
  }
};

NOTE: This code is in NodeJs but I hope this will solve your problem.

Roxanneroxburgh