MongoDB - Storing date without timezone

Question

We have a simple application in which we have all user in same timezone & therefore we are not interested to store timezone information in mongo date object.

Reason for such extreme step is we have multiple micro service using common database managed by different developers. Each of them requires to explicitly set timezone related stuff in query & forgetting same results in invalid dataset.

Since currently MongoDB folks Mongo Data Types

doesn't support storing dates without timezone.

Just eager to know that is their any alternative approach to represent date without timezone in mongo by which we can still able to take advantage of mongo database queries date based syntax like date ranges, date etc.

At the same time it would be convenient for DBA's to read and manage records.

Cambria

Answer 1

Look at this answer: https://mcmap.net/q/350383/-what-is-the-best-way-to-store-dates-in-mongodb

You can use two types of long representation (milliseconds or format yyyyMMddHHmmss). These are the only ways to not store timezone and still be able to make range queries.

Unfortunately you lost some aggregation properties. But you can do something like keeping two representations and use them at opportune times.

UPDATE:

Do not store date as I said before. You will lost many and many features of MongoDB and also will be hard to perform major operators on date fields.

Newer versions of MongoDB has operators to deal with timezone, and it should be enough to work with ISOTime formats. My application was using my own suggestion to store date. Now I have to let my users select their TimeZone (company has grown and we need to expand to other countries). We are struggling to change all models to use timestamp instead of a normalized date format. For further more explore the link: https://docs.mongodb.com/manual/reference/method/Date/

and you can also use MongoDB official community channel for questioning Here is the link: https://developer.mongodb.com/community/forums/

Matsumoto

hmmm I did checked this post before posting problem. Issue with this approach is that both of them makes date unreadable by humans. So any DBA operation which can be previously performed directly on console will need to use some conversion wrapper to convert human readable to int values and vice versa. Thus leading to a different problem. – Cambria

I don't think the format yyyyMMddHHmmss is unreadable haha But for your problem, I think the other way (maybe only one) you can do is to storing the timezone and subtract/add timezone offset in queries, just like this aggregation (in java): { "$project": { "date": { "$add": [ "$date", TimeZone.getDefault().getOffset(System.currentTimeMillis()) ] } } } – Matsumoto

oopss may be my understanding is wrong about yyyyMMddHHmmss, I was assuming to store same as a int/long only with extracted values. Yes I think it should work perfectly for even for ranges query as well though we need to write some custom regex where we need to ignore year. Also I would also try your second suggested solution. – Cambria

Answer 2

You could consider storing all the dates in UTC and presenting to the users in UTC, so you don't have the problem of silent conversion by either client JavaScript, server or MongoDB and therefore confusion. You can do it like this: new Date(Date.UTC(2000, 01, 28)) Here's MDN link on the subject.

Lal

Answer 3

create a separate date.js module and export it

function formatDate(dateString) {
  const date = new Date(dateString);
  const day = String(date.getDate()).padStart(2, "0"); // Add leading zero if single digit
  const monthIndex = date.getMonth();
  const year = date.getFullYear().toString().slice(0); // Get the last two digits of the year
  const formattedDate = `${year}-${String(monthIndex + 1).padStart(
    2,
    "0"
  )}-${day}`; // Add leading zero if single digit
  return formattedDate;
}

module.exports = formatDate;

then in the import it in your main app const formatDate = require("./date");

then change your schema to something like this in the date field mine looks like this

const clientSchema = new mongoose.Schema({
  name: String,
  phone: String,
  acc: Number,
  radio: String,
  date: { type: String, default: formatDate(new Date().toISOString()) },
  amount: String,
  due: String,
});

change the date type to STRING

Confiture