[SERVER-41948] MongoDB not supporting all standard timezones Created: 27/Jun/19  Updated: 22/Sep/21  Resolved: 16/Aug/19

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 3.6.0, 3.6.13
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Alexandru Martin Assignee: Kelsey Schubert
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-59701 Error when working with some timezone... Closed
Participants:

 Description   

A specific AMI/repo results in a MongoDB deployment returning "unrecognized time zone identifier" for the command "db.test.aggregate([{$project: {"hour": {$hour:{date: "$dt", timezone:"Etc/GMT+7"}}}}])". The command works fine on many other 3.6.13 installations/instances.

This reproduces using:

  • An AWS instance in eu-west-1 launched using image amzn-ami-hvm-2016.03.3.x86_64-gp2 (ami-f9dd458a)
  • A /etc/yum.repos.d/mongodb-org.repo file with the following contents:

[mongodb-org-3.6]
name=MongoDB Repository
baseurl=https://repo.mongodb.org/yum/amazon/2013.03/mongodb-org/3.6/x86_64/
gpgcheck=1
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-3.6.asc

  • A mongod installed/started using:

sudo yum install -y mongodb-org
service mongod start

  • The following shell commands:

> db.test.insert({dt: new ISODate()})
WriteResult({ "nInserted" : 1 })
> db.test.aggregate([{$project: {"hour": {$hour:{date: "$dt", timezone:"Etc/GMT+7"}}}}])
assert: command failed: {
	"ok" : 0,
	"errmsg" : "unrecognized time zone identifier: \"Etc/GMT+7\"",
	"code" : 40485,
	"codeName" : "Location40485"
} : aggregate failed
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:403:5
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1212:12
@(shell):1:1
 
2019-07-17T18:49:22.777+0000 E QUERY    [thread1] Error: command failed: {
	"ok" : 0,
	"errmsg" : "unrecognized time zone identifier: \"Etc/GMT+7\"",
	"code" : 40485,
	"codeName" : "Location40485"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:403:5
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1212:12
@(shell):1:1

original description

Hello! I was trying to make a project like this: 

{
  { 
    $project: { hour:{$hour:{date:'$eventCreatedAt',timezone:'$timezone'}} 
  } 
},

where $eventCreatedAt is always an ISODate and $timezone can be any standard timezone from https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

I saw that when the timezone is Etc/GMT+7 mongo throws an exception saying it is not recognized.

 

I have read the documentation which states that the recognized timezones are:

  • an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or
  • a UTC offset in the form:
    • +/-[hh]:[mm], e.g. "+04:45", or
    • +/-[hh][mm], e.g. "-0530", or
    • +/-[hh], e.g. "+03".

My question is why there not supported all standard timezonez such as Etc/GMT+7 ? All programming languages supports them so mongo why not? In the end that is a standard and shoul be completely covered.



 Comments   
Comment by Kelsey Schubert [ 16/Aug/19 ]

Hi axiodis,

I'm not aware of any issues around changing the mongod's timezone. All date type objects are stored as UTC, so there shouldn't be any different between documents before or after or across different nodes.

Please note that the SERVER project is for reporting bugs or feature suggestions for the MongoDB server. For MongoDB-related support discussion please post on the mongodb-user group or Stack Overflow with the mongodb tag. Questions like this involving more discussion would be best posted on the mongodb-users group.

Kind regards,
Kelsey

Comment by Alexandru Martin [ 09/Aug/19 ]

We managed to solve the problem on a test standalone mongo server by commenting the line 

timeZoneInfo: /usr/share/zoneinfo

and forcing mongo to use its internal timezones database.

So the problem is that the timezones database from the operating system that mongodb is reading is not up to date and must be updated either manually like you pointed out(A) or automatically through the package manager. Manually updating i am certain it is not a good idea giving the fact that there may be other applications using it and it may cause problems.

Giving the fact that on the production server we can not automatically update the OS timezones database through the package manager as there isn't any updates we must either use your second solution (B) or our solution by commenting that line and letting mongo to use its internal database. As you can see this 2 cases are almost identical as we must change the configuration file and we cannot do this without additional info:

  • Is there any problem if we use the internal database? Is there a reason not to use it?
  • We have a replica set, if we make this change on secondary will there be a difference on how the documents are written between secondary and primary?
  • What is your recommended way of doing this for a replica set? Do you have any suggestions to make this process safer?
  • We use this mongoDB server for analytics so we store datetime related events in it. If we change the timezones database will there be a difference between the documents before and those after the update?
Comment by Eric Sedor [ 26/Jul/19 ]

Thanks for your patience, axiodis

A colleague has pointed me at this documentation that could describe what's going on. Can you try either A) updating /usr/share/zoneinfo with the mentioned zip file, or B) extracting that file to another location and providing an explicit --timeZoneInfo config pointing there?

Comment by Eric Sedor [ 17/Jul/19 ]

Thanks axiodis,

I have been able to reproduce this using:

  • An AWS instance in eu-west-1 launched using image amzn-ami-hvm-2016.03.3.x86_64-gp2 (ami-f9dd458a)
  • A /etc/yum.repos.d/mongodb-org.repo file with the following contents:

[mongodb-org-3.6]
name=MongoDB Repository
baseurl=https://repo.mongodb.org/yum/amazon/2013.03/mongodb-org/3.6/x86_64/
gpgcheck=1
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-3.6.asc

  • A mongod installed/started using:

sudo yum install -y mongodb-org
service mongod start

  • The following shell commands:

> db.test.insert({dt: new ISODate()})
WriteResult({ "nInserted" : 1 })
> db.test.aggregate([{$project: {"hour": {$hour:{date: "$dt", timezone:"Etc/GMT+7"}}}}])
assert: command failed: {
	"ok" : 0,
	"errmsg" : "unrecognized time zone identifier: \"Etc/GMT+7\"",
	"code" : 40485,
	"codeName" : "Location40485"
} : aggregate failed
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:403:5
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1212:12
@(shell):1:1
 
2019-07-17T18:49:22.777+0000 E QUERY    [thread1] Error: command failed: {
	"ok" : 0,
	"errmsg" : "unrecognized time zone identifier: \"Etc/GMT+7\"",
	"code" : 40485,
	"codeName" : "Location40485"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:403:5
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1212:12
@(shell):1:1

I am passing this ticket to an appropriate team for deeper investigation.

Comment by Alexandru Martin [ 11/Jul/19 ]

We encountered the same problem on both ami ids:

Our test ami on which we have run the second test: amzn2-ami-hvm-2.0.20190618-x86_64-gp2 (ami-0bbc25e23a7640b9b)

Our production ami on which we first encountered the problem: amzn-ami-hvm-2016.03.3.x86_64-gp2 (ami-f9dd458a)

Comment by Eric Sedor [ 10/Jul/19 ]

Thanks axiodis; Can you please provide the AMI ID you see this behavior with? (e.g., ami-XXXXXXXXXXX)

Comment by Alexandru Martin [ 09/Jul/19 ]

I have tried again on a new standalone mongoDB server on aws and it gives the same error.

MOngoDB repo:

$ cat /etc/yum.repos.d/mongo.repo 
[mongodb-org-3.6]
name=MongoDB Repository
baseurl=https://repo.mongodb.org/yum/amazon/2013.03/mongodb-org/3.6/x86_64/
gpgcheck=1
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-3.6.asc

Comment by Alexandru Martin [ 04/Jul/19 ]

Yes i can confirm:

ReplicaSet:PRIMARY> use testDb
switched to db testDb
ReplicaSet:PRIMARY> db.version()
3.6.13

Also the mogo we are using is a replicaSet as you can see running on amazon ec2 instances.

cat /etc/system-release
Amazon Linux AMI release 2018.03

Our replicaSet is basic configuration made from 2 EC2 instances (primary and secondary) on which the data is written and 1 arbiter.

I have also tested these commands on a local replicaSet which have been set up using docker and this docker [file|http://37yonub.ru/articles/mongo-replica-set-docker-localhost] and it worked perfectly.

Given the fact that i encounter this problem only on the mongoDB running on amazon is there a possibility that the problem can be caused by the amazon operating system? A missing dependency or something else? If yes how can i check this?

Comment by Eric Sedor [ 03/Jul/19 ]

Strangely I am not able to reproduce this by pasting the commands you provided:

> use testDb
switched to db testDb
> db.test.find()
> db.test.insert({dt: new ISODate()})
WriteResult({ "nInserted" : 1 })
> db.test.find()
{ "_id" : ObjectId("5d1d1b470ea371aa314cca0b"), "dt" : ISODate("2019-07-03T21:16:55.841Z") }
> db.test.aggregate([{$project: {"hour": {$hour:{date: "$dt", timezone:"Etc/GMT+7"}}}}])
{ "_id" : ObjectId("5d1d1b470ea371aa314cca0b"), "hour" : 14 }
> db.test.aggregate([{$addFields: {"hour": {$hour:{date: "$dt", timezone:"Etc/GMT+7"}}}}])
{ "_id" : ObjectId("5d1d1b470ea371aa314cca0b"), "dt" : ISODate("2019-07-03T21:16:55.841Z"), "hour" : 14 }
> db.version()
3.6.13
>

"mongo --version" is providing the version of the shell itself. Can you confirm the "db.version()" from the shell?

Comment by Alexandru Martin [ 01/Jul/19 ]

More info

I managed to reproduce the problem in our mongoDB shell and I have searched the log and nothing about the agg operation showed.

 

$ mongo --version
MongoDB shell version v3.6.13
git version: db3c76679b7a3d9b443a0e1b3e45ed02b88c539f
OpenSSL version: OpenSSL 1.0.0-fips 29 Mar 2010
allocator: tcmalloc
modules: none
build environment:
 distmod: amazon
 distarch: x86_64
 target_arch: x86_64

ReplicaSet:PRIMARY> use testDb
switched to db testDb
 
ReplicaSet:PRIMARY> db.test.find()
 
ReplicaSet:PRIMARY> db.test.insert({dt: new ISODate()})
WriteResult({ "nInserted" : 1 })
 
ReplicaSet:PRIMARY> db.test.find()
{ "_id" : ObjectId("5d19a94ebddd745bf60be7bd"), "dt" : ISODate("2019-07-01T06:33:50.401Z") }
 
ReplicaSet:PRIMARY> db.test.aggregate([{$project: {"hour": {$hour:{date: "$dt", timezone:"Etc/GMT+7"}}}}])
assert: command failed: {
    "operationTime" : Timestamp(1561962845, 146),
    "ok" : 0,
    "errmsg" : "unrecognized time zone identifier: \"Etc/GMT+7\"",
    "code" : 40485,
    "codeName" : "Location40485",
    "$clusterTime" : {
        "clusterTime" : Timestamp(1561962845, 146),
        "signature" : {
            "hash" : BinData(0,"YtgMvh0BBqugIwuLsYVz9LDVw1Y="),
            "keyId" : NumberLong("6659328882434375681")
        }
    }
} : aggregate failed
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:403:5
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1212:12
@(shell):1:12019-07-01T06:34:05.378+0000 E QUERY    [thread1] Error: command failed: {
    "operationTime" : Timestamp(1561962845, 146),
    "ok" : 0,
    "errmsg" : "unrecognized time zone identifier: \"Etc/GMT+7\"",
    "code" : 40485,
    "codeName" : "Location40485",
    "$clusterTime" : {
        "clusterTime" : Timestamp(1561962845, 146),
        "signature" : {
            "hash" : BinData(0,"YtgMvh0BBqugIwuLsYVz9LDVw1Y="),
            "keyId" : NumberLong("6659328882434375681")
        }
    }
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:403:5
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1212:12
@(shell):1:1
 
ReplicaSet:PRIMARY> db.test.aggregate([{$addFields: {"hour": {$hour:{date: "$dt", timezone:"Etc/GMT+7"}}}}])
assert: command failed: {
    "operationTime" : Timestamp(1561962862, 374),
    "ok" : 0,
    "errmsg" : "unrecognized time zone identifier: \"Etc/GMT+7\"",
    "code" : 40485,
    "codeName" : "Location40485",
    "$clusterTime" : {
        "clusterTime" : Timestamp(1561962862, 374),
        "signature" : {
            "hash" : BinData(0,"ksd4wSHplFSm2SEld73fOpSzWnA="),
            "keyId" : NumberLong("6659328882434375681")
        }
    }
} : aggregate failed
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:403:5
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1212:12
@(shell):1:12019-07-01T06:34:22.975+0000 E QUERY    [thread1] Error: command failed: {
    "operationTime" : Timestamp(1561962862, 374),
    "ok" : 0,
    "errmsg" : "unrecognized time zone identifier: \"Etc/GMT+7\"",
    "code" : 40485,
    "codeName" : "Location40485",
    "$clusterTime" : {
        "clusterTime" : Timestamp(1561962862, 374),
        "signature" : {
            "hash" : BinData(0,"ksd4wSHplFSm2SEld73fOpSzWnA="),
            "keyId" : NumberLong("6659328882434375681")
        }
    }
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:403:5
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1212:12
@(shell):1:1

 

 

Comment by Alexandru Martin [ 29/Jun/19 ]

Hi i have tried again on a local mongoDB and it works as it works for you. I don't have access to the database with the problem until monday, but i promise that on monday i will give you all the info i can. Please keep the issue open.

 

Local test:

> use test
switched to db test
> db.test.insert({dt: new ISODate()})
WriteResult({ "nInserted" : 1 })
> db.test.find()
{ "_id" : ObjectId("5d174d086f0d2508387ec2b4"), "date" : Timestamp(1561808207, 1) }
{ "_id" : ObjectId("5d174dbf29012c44aabed473"), "dt" : ISODate("2019-06-29T11:38:39.678Z") }
> db.test.aggregate([$project: {"hour": {$hour:{date: "$dt", timezone:"Etc/GMT+7"}}}])
2019-06-29T14:40:40.092+0300 E QUERY [thread1] SyntaxError: missing ] after element list @(shell):1:27
> db.test.aggregate([{$project: {"hour": {$hour:{date: "$dt", timezone:"Etc/GMT+7"}}}}])
{ "_id" : ObjectId("5d174d086f0d2508387ec2b4"), "hour" : null }
{ "_id" : ObjectId("5d174dbf29012c44aabed473"), "hour" : 4 }
> db.test.aggregate([{$addFields: {"hour": {$hour:{date: "$dt", timezone:"Etc/GMT+7"}}}}])
{ "_id" : ObjectId("5d174d086f0d2508387ec2b4"), "date" : Timestamp(1561808207, 1), "hour" : null }
{ "_id" : ObjectId("5d174dbf29012c44aabed473"), "dt" : ISODate("2019-06-29T11:38:39.678Z"), "hour" : 4 }

Comment by Eric Sedor [ 28/Jun/19 ]

Hi axiodis. I am not able to reproduce the issue you've reported "Etc/GMT+7" seems to be acceptable input both as a string to the $project stage and as a reference to a string within documents.

MongoDB shell version v3.6.13
...
> use test
switched to db test
> db.tz.insert({dt:new ISODate()})
WriteResult({ "nInserted" : 1 })
> db.tz.aggregate([{$project:{"hour":{$hour:{date:"$dt",timezone:"Africa/Bangui"}}}}])
{ "_id" : ObjectId("5d1672629ec6981016e40757"), "hour" : 21 }
> db.tz.aggregate([{$project:{"hour":{$hour:{date:"$dt",timezone:"Etc/GMT+7"}}}}])
{ "_id" : ObjectId("5d1672629ec6981016e40757"), "hour" : 13 }
> db.tz_embedded.insert({dt:new ISODate(),tz:"Etc/GMT+7"})
WriteResult({ "nInserted" : 1 })
> db.tz_embedded.insert({dt:new ISODate(),tz:"Africa/Bangui"})
WriteResult({ "nInserted" : 1 })
> db.tz_embedded.aggregate([{$project:{"hour":{$hour:{date:"$dt",timezone:"$tz"}}}}])
{ "_id" : ObjectId("5d1673249ec6981016e40758"), "hour" : 13 }
{ "_id" : ObjectId("5d1673679ec6981016e40759"), "hour" : 21 }

Can you provide more information about how this query is being executed? Or, are you able to reproduce in the MongoDB shell connected to your own database?

Generated at Thu Feb 08 04:59:09 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.