0

Which is the correct or ideal or preferred method to convert a CST Date and/or Datetime field to UTC along with DST aware settings and store in MongoDB in ISO format in Python/PyMongo ? The source date/datetime field can come from any timezone (right now we know its CST), I need to convert all of them to UTC and store into target MongoDB.

As per MongoDB docs, MongoDB stores times in UTC by default, and will convert any local time representations into this form. Applications that must operate or report on some unmodified local time value may store the time zone alongside the UTC timestamp, and compute the original local time in their application logic.

Examples:

Method#1: with Timestamp (local timezone defined)

from datetime import datetime
import pytz
local_timezone = pytz.timezone("US/Central")
utc_datetime = local_timezone.localize(datetime.strptime ("1/2/2017 12:43 pm",'%m/%d/%Y %H:%M %p'),is_dst=True).astimezone(pytz.utc)
print(utc_datetime)
print(type(utc_datetime))

2017-01-02 18:43:00+00:00
<class 'datetime.datetime'>

without timestamp i.e. just date: - it adds an offset value of 6 hours in timestamp and during DST 5 hours. Removing or without astimezone(pytz.utc) , it returns date/time like 2017-01-02 00:00:00-06:00 i.e. showing -6 hours offset difference. Should we really be using astimezeon(pytz.utc) ??

from datetime import datetime
import pytz
local_timezone = pytz.timezone("US/Central")
utc_datetime = local_timezone.localize(datetime.strptime ("1/2/2017",'%m/%d/%Y'),is_dst=True).astimezone(pytz.utc)
print(utc_datetime)
print(type(utc_datetime))

2017-01-02 06:00:00+00:00
<class 'datetime.datetime'>

Method#2: with Timestamp (local timezone NOT defined)

from datetime import datetime, timezone
utc_datetime=datetime.utcfromtimestamp(datetime.strptime ("1/2/2017 12:43 pm",'%m/%d/%Y %H:%M %p').replace(tzinfo = timezone.utc).timestamp())
print(utc_datetime)
print(type(utc_datetime))

2017-01-02 12:43:00
<class 'datetime.datetime'>

without Timestamp i.e. just date part - no offset

from datetime import datetime, timezone
utc_datetime=datetime.utcfromtimestamp(datetime.strptime ("1/2/2017",'%m/%d/%Y').replace(tzinfo = timezone.utc).timestamp())
print(utc_datetime)
print(type(utc_datetime))

2017-01-02 00:00:00
<class 'datetime.datetime'>

After loading into MongoDB - it adds a "Z" at the end of the date/timestamp. Should I also add "tz_aware=True" when initiating connection with MongoClient ?

ISOFormat - changing above utc timestamp to isoformat() returns and gets loaded as string in MongoDB instead of a Date. So, how do we ensure it is still stored in ISO Date format in MongoDB ?

utc_datetime_iso=datetime.utcfromtimestamp(datetime.strptime ("1/2/2017",'%m/%d/%Y').replace(tzinfo = timezone.utc).timestamp()).**isoformat()**
print(utc_datetime_iso)
print(type(utc_datetime_iso))

2017-01-02T00:00:00
<class 'str'>
ManiK
  • 377
  • 1
  • 21
  • Insert these values into your MongoDB and check what has been actually inserted (preferable use a native tool like the mongo shell for that) – Wernfried Domscheit Sep 06 '20 at 19:57
  • I've mentioned above as well that it adds a "Z" at the end of datetime like this: 2017-01-02 06:00:00Z, but its from robo3t mongodb UI interface, haven't checked from Mongo native shell/CLI. Am curious to know what is the best and recommended practice for handling UTC conversion from local timezone(s) , storing to MongoDB - format and all - just in case if this data is to be used globally for reporting purposes. – ManiK Sep 06 '20 at 20:19
  • side-note: consider *not* using [pytz](https://pypi.org/project/pytz-deprecation-shim/) and [utcfromtimestamp](https://blog.ganssle.io/articles/2019/11/utcnow.html) at all. – FObersteiner Sep 07 '20 at 07:32
  • Then, what is the other alternative in Python for converting a local time zone to UTC ? Out of above two approaches am using **pytz** instead of utcfromtimestamp, as per these two links:- [link] (https://julien.danjou.info/python-and-timezones/) and [link] (https://medium.com/@jmarhee/handling-timestamps-and-timezone-conversion-in-python-3d7cc5759088). – ManiK Sep 07 '20 at 09:28
  • 1
    you can simply use `timezone.utc` from the datetime module, e.g. to get local time to UTC: `datetime.now().astimezone(timezone.utc)`. if you need to work with other time zones than *local time*, have a look at [dateutil](https://pypi.org/project/python-dateutil/) or [zoneinfo](https://docs.python.org/3.9/library/zoneinfo.html) (Python 3.9). – FObersteiner Sep 07 '20 at 14:48

1 Answers1

1

I never worked with python, so I can give only some general notes.

Never store date/time values as string, use proper Date object. Storing date/time values as strings is usually a design failure.

All Date values in MongoDB are stored in UTC - always and only. Some client applications implicitly converts UTC to local times and display local values, however internally in MongoDB it is always UTC.

If you run db.collection.insertOne({ts: ISODate("2020-09-07T14:00:00+02:00")}) then MongoDB stores ISODate("2020-09-07T12:00:00Z"), the original time zone information is lost. If you need to preserve the original time zone, then you have to store it in a separate field.

ISODate is just an alias for new Date. However, there is a difference. If you don't specify any time zone (e.g. "2020-09-07T14:00:00") then new Date() assumes local time but ISODate() assumes UTC time. I don't know which method is internally used by python.

So, new Date("2020-09-07T14:00:00") results in 2020-09-07 12:00:00Z whereas ISODate("2020-09-07T14:00:00") results in 2020-09-07 14:00:00Z

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110