Date Issue with UTC


#1

Hello!

I have a large amount of data in a MongoDB server that is very dependent on two dateTime fields. One of the analysts using MetaBase pointed out a piece of data expected wasn’t showing up in MetaBase but was when a Mongo query was run with same parameters.

I went down the rabbit hole with this one and noticed the issue was MetaBase is adjusting the time displayed vs the time in my database. Those two tables both display a time that is 4hrs (my timezone) earlier of the actual data in my db.

I tried setting the general timezone to UTC but that didn’t work. The two fields in MetaBase don’t have an option for any special data type either. I am using ISO8601 date formatting for my data in Mongo (“2014-12-28T23:00:00Z”), so I figured MetaBase would just display that?


#2

Worked out what my problem was so I figured I would post it here in case someone else had issues.

The general setting for timezone in MetaBase won’t work because MongoDB doesn’t support Report Time Zone. So my db is reporting UTC and my MetaBase server system time is EDT. This causes the discrepancy. What you have to do is pass the timezone to the Java environment as UTC or whatever you need.

See this article from MetaBase about this exact issue:

http://www.metabase.com/docs/latest/troubleshooting-guide/timezones


#3

Adding to @adunbrook’s comment. From what I’m seeing this only works as long as you’re working with Raw Data without Groupings.

The moment you start Groupings, the grouped fields ignore the system variables and default to UTC.

It doesn’t make sense why the final view render behaves differently in either case. If its able to convert timestamps without groupings on the recordset, it should be the same behaviour even if its group right?