David Gouldin - import time_travel: A Primer on Timezones in Python

Slide 1

Slide 1 text

import time_travel A Primer on Timezones in Python by David Gouldin @dgouldin

Slide 2

Slide 2 text

Why support timezones?

Slide 3

Slide 3 text

I don’t need timezone support because… • My app is only intended for use in 1 locale. • I’m not doing anything complicated with datetimes. • It’s easier to not think about now. I can always add support later.

Slide 4

Slide 4 text

I have some bad news.

Slide 5

Slide 5 text

There is no escape. • You probably already encounter machines with different local timezones. • Even if you only cared about 1 timezone, you still have to be aware of its rules. • Choosing not to support timezones up-front can result in lossy or at best ambiguous data.

Slide 6

Slide 6 text

Ok, but how hard can it be?

Slide 7

Slide 7 text

By Evan Siroky  CC BY-SA 4.0 (https://creativecommons.org/licenses/by-sa/4.0)  via Wikimedia Commons

Slide 8

Slide 8 text

Who am I? And why do I care about timezones?

Slide 9

Slide 9 text

No content

Slide 10

Slide 10 text

I work at Clara Labs • My job is meeting scheduling over email. • Our system is “human in the loop”. • Contractors around the globe label scheduling data for us & proof our automation.

Slide 11

Slide 11 text

Our customers don’t care about timezone bugs. They only care about the important meetings we’re scheduling for them.

Slide 12

Slide 12 text

What makes a timezone?

Slide 13

Slide 13 text

Timezones contextualize datetimes • Just like all byte strings have an encoding, all moments in time have a timezone. • A timezone-naive datetime is like a byte string whose encoding you don’t know. clock by Mr. Minuvi from the Noun Project Map by jayati bandyopadhyay from the Noun Project “wall time” + timezone point by Gianne G from the Noun Project moment in time =

Slide 14

Slide 14 text

Vienna Airport by Martin Tomitsch  https://www.flickr.com/photos/martinonflickr/3001031996/

Slide 15

Slide 15 text

Coordinated Universal Time • Roughly analogous to Greenwich Mean Time (GMT). • Solar mean at 0 latitude. • No DST offset. • Think of it as UTF-8 for timezones. (UTC for short … because reasons)

Slide 16

Slide 16 text

IANA Timezone Database • A set of historical rules for all timezones including UTC offsets and DST start/end dates. • Updated several times a year to account for changes made by political bodies. • Includes unique names and aliases for each timezone. Or the “Olsen” database

Slide 17

Slide 17 text

IANA Timezone Database America/Los_Angeles # Rule NAME FROM TO TYPE IN ON AT SAVE LETTER Rule CA 1948 only - Mar 14 2:01 1:00 D Rule CA 1949 only - Jan 1 2:00 0 S Rule CA 1950 1966 - Apr lastSun 1:00 1:00 D Rule CA 1950 1961 - Sep lastSun 2:00 0 S Rule CA 1962 1966 - Oct lastSun 2:00 0 S # Zone NAME GMTOFF RULES FORMAT [UNTIL] Zone America/Los_Angeles -7:52:58 - LMT 1883 Nov 18 12:07:02 -8:00 US P%sT 1946 -8:00 CA P%sT 1967 -8:00 US P%sT

Slide 18

Slide 18 text

IANA Timezone Database They do the legwork so you don’t have to # Tokelau # # From Gwillim Law (2011-12-29) # A correspondent informed me that Tokelau, like Samoa, will be skipping # December 31 this year ... # # From Steffen Thorsen (2012-07-25) # ... we double checked by calling hotels and offices based in Tokelau asking # about the time there, and they all told a time that agrees with UTC+13.... # Shanks says UT-10 from 1901 [but] ... there is a good chance the change # actually was to UT-11 back then. # # From Paul Eggert (2012-07-25) # A Google Books snippet of Appendix to the Journals of the House of # Representatives of New Zealand, Session 1948, # , page 65, says Tokelau # was "11 hours slow on G.M.T." Go with Thorsen and assume Shanks & Pottenger # are off by an hour starting in 1901.

Slide 19

Slide 19 text

Limitations of UTC Offset • UTC offset by itself is sufficient to express a specific moment in time. • Since multiple timezones share an offset, offset does not imply zone. • Modifications made using just an offset can result in an incorrect “wall time”.

Slide 20

Slide 20 text

Ambiguous Times PDT PST • When a timezone’s UTC offset shifts backward, it results in a period of wall time that could apply to either offset. • Since it’s impossible to resolve the moment in time from just the wall time and timezone, these times are considered “ambiguous”. • All ambiguous wall times occur (at least) twice. 2am 2am

Slide 21

Slide 21 text

Imaginary Times PST PDT • When a timezone’s UTC offset shifts forward, it results in a period of wall time that will never exist. • Since they don’t exist, these times are considered “imaginary”. 2am 3am

Slide 22

Slide 22 text

Timezones in the Python Standard Library

Slide 23

Slide 23 text

datetime.tzinfo Abstract base class • Intended to be subclassed, not directly instantiated. • Given a datetime, provides UTC and DST offsets. • Includes a method to convert a UTC datetime to the timezone’s local time.

Slide 24

Slide 24 text

datetime.tzinfo UTC offset • dt is assumed to be in the timezone’s local time. dt.tzinfo is ignored even if it’s not None. • Returns a timedelta instance. Its value could be negative since anything west of GMT has a negative UTC offset. • The same timezone may have different return values for different values of dt, due to factors like DST. tzinfo.utcoffset(dt)

Slide 25

Slide 25 text

datetime.tzinfo UTC offset tzinfo.utcoffset(dt) >>> from datetime import datetime >>> from dateutil.tz import gettz >>> tz = gettz("America/New_York") >>> tz.utcoffset(datetime(2018, 1, 1)) # EST datetime.timedelta(-1, 68400) >>> tz.utcoffset(datetime(2018, 6, 1)) # EDT datetime.timedelta(-1, 72000)

Slide 26

Slide 26 text

datetime.tzinfo DST offset • Like tzinfo.utcoffset, but only represents the offset specifically due to DST. • tzinfo.utcoffset already includes the DST offset, so this method is less often used on its own. • Generally, tzinfo.utcoffset(dt) - tzinfo.dst(dt) is constant for any value of dt (with the exception of changes in offset not relating to DST). tzinfo.dst(dt)

Slide 27

Slide 27 text

datetime.tzinfo DST offset tzinfo.dst(dt) >>> from datetime import datetime >>> from dateutil.tz import gettz >>> tz = gettz('America/New_York') >>> tz.dst(datetime(2018, 1, 1)) # EST datetime.timedelta(0) >>> tz.dst(datetime(2018, 6, 1)) # EDT datetime.timedelta(0, 3600)

Slide 28

Slide 28 text

datetime.timezone Python 3’s built-in tzinfo subclass class datetime.timezone(offset, name=None) • Introduced in Python 3.2. • If all you need is a static UTC offset, just create an instance with that offset and use it as you would any tzinfo instance. datetime.timezone.utc • UTC timezone instance • Functionally equivalent to datetime.timezone(timedelta())

Slide 29

Slide 29 text

datetime.datetime class datetime.datetime(year, month, day, hour=0, minute=0, second=0, microsecond=0, tzinfo=None, *, fold=0) Constructor • Accepts optional timezone (tzinfo). • If tzinfo is supplied, the instance is “timezone-aware”. • Otherwise, it is “timezone-naive”.

Slide 30

Slide 30 text

datetime.datetime classmethod datetime.now(tz=None) Getting the current time • Uses the system local time to compute the value of “now”. • If not timezone is provided, returns a timezone-naive datetime using the system’s local timezone. • Otherwise, converts to the provided timezone and returns a timezone-aware datetime. classmethod datetime.utcnow() • Uses the system local time, converts to UTC, and returns a timezone-naive datetime. • If you want “now” in UTC, you probalby don’t want a naive datetime anyway, so use now(tz=timezone.utc) instead.

Slide 31

Slide 31 text

datetime.datetime classmethod datetime.now(tz=None) Getting the current time classmethod datetime.utcnow() >>> from datetime import datetime, timezone >>> datetime.now() # PDT (local system time) datetime.datetime(2018, 4, 24, 20, 31, 47, 968041) >>> datetime.now(tz=timezone.utc) datetime.datetime(2018, 4, 25, 3, 32, 19, 408783, tzinfo=datetime.timezone.utc) >>> datetime.utcnow() datetime.datetime(2018, 4, 25, 3, 32, 53, 234654)

Slide 32

Slide 32 text

datetime.datetime datetime.replace(…tzinfo=self.tzinfo, …) Converting between naive and aware • Call with tzinfo=None to convert a timezone-aware datetime to timezone- naive. • Call with a timezone to convert a timezone-naive datetime to timezone- aware. • This does not modify the datetime’s wall time, so only use replace to create timezone-aware datetimes when you’re already sure of the timezone.

Slide 33

Slide 33 text

datetime.datetime datetime.replace(…tzinfo=self.tzinfo, …) Converting between naive and aware >>> from datetime import datetime, timezone >>> from dateutil.tz import gettz >>> dt = datetime(2018, 1, 1, tzinfo=timezone.utc) >>> tz = gettz(“America/New_York") >>> dt datetime.datetime(2018, 1, 1, 0, 0, tzinfo=datetime.timezone.utc) >>> dt.replace(tzinfo=tz) datetime.datetime(2018, 1, 1, 0, 0, tzinfo=tzfile('US/Eastern'))

Slide 34

Slide 34 text

datetime.datetime datetime.astimezone(tz=None) Converting to a different timezone • Returns a timezone-aware datetime converted to the given timezone. • If called on a timezone-naive datetime, assumes local system timezone.* (Don’t do this. It’s almost never what you want.) • If the timezone is not supplied, the local system timezone will be used. (Don’t do this. It’s almost never what you want.) * as of Python 3.6. Prior versions will raise an exception.

Slide 35

Slide 35 text

datetime.datetime datetime.astimezone(tz=None) Converting to a different timezone >>> from datetime import datetime, timezone >>> from dateutil.tz import gettz >>> dt = datetime(2018, 1, 1, tzinfo=timezone.utc) >>> dt datetime.datetime(2018, 1, 1, 0, 0, tzinfo=datetime.timezone.utc) >>> tz = gettz("America/New_York") >>> dt.astimezone(tz) datetime.datetime(2017, 12, 31, 19, 0, tzinfo=tzfile('US/Eastern'))

Slide 36

Slide 36 text

datetime.datetime Operators • All datetime operators that work with other datetimes require a like- zoned instance. (Both must be timezone-naive or timezone-aware.) • Operators between timezone-aware datetimes with the same timezone operate on wall time. If the timezones are different, Python treats them like moments in time.

Slide 37

Slide 37 text

datetime.datetime Operators >>> from datetime import datetime, timedelta, timezone >>> from dateutil.tz import gettz   >>> tz = gettz('America/New_York') >>> datetime(2018, 1, 1, tzinfo=tz) - datetime(2018, 1, 1, tzinfo=timezone.utc) datetime.timedelta(0, 18000) >>> datetime(2018, 1, 1, tzinfo=tz) - datetime(2018, 1, 1) Traceback (most recent call last): File "", line 1, in TypeError: can't subtract offset-naive and offset-aware datetimes

Slide 38

Slide 38 text

datetime.datetime Operators >>> dt1 = datetime(2018, 3, 1, tzinfo=tz) >>> dt2 = dt1 + timedelta(days=30) >>> dt2 datetime.datetime(2018, 3, 31, 0, 0, tzinfo=tzfile(‘US/Eastern')) >>> dt2 - dt1 datetime.timedelta(30) >>> dt2.astimezone(timezone.utc) - dt1.astimezone(timezone.utc) datetime.timedelta(29, 82800)

Slide 39

Slide 39 text

datetime.datetime Ambiguous times • Proposed in PEP-495. • Introduced in Python 3.6. • Used to differentiate between ambiguous times. • 0 means the earlier time, 1 means the later time. datetime.fold

Slide 40

Slide 40 text

3rd Party Timezone Libraries It’s dangerous to go alone

Slide 41

Slide 41 text

dateutil.tz gettz(name) Get timezone • Accepts an IANA timezone name. • Returns a tzinfo subclass instance for the corresponding IANA timezone definition. UTC • Singleton instance for UTC timezone • Also accessible via tzutc().

Slide 42

Slide 42 text

dateutil.tz gettz(name) Get timezone UTC >>> from datetime import datetime, tzinfo >>> from dateutil.tz import UTC, tzutc, gettz >>> tz = gettz('America/New_York') >>> isinstance(tz, tzinfo) True >>> tz.utcoffset(datetime(2018, 1, 1)) datetime.timedelta(-1, 68400) >>> UTC.utcoffset(datetime(2018, 1, 1)) datetime.timedelta(0) >>> tzutc() is UTC True

Slide 43

Slide 43 text

dateutil.tz enfold(dt, fold=1) Ambiguous times • Unified interface for “folded” datetimes. • Supports fold in versions of Python prior to 3.6 by using its own datetime subclass. • Evaluates whether the provided datetime is ambiguous for the timezone. • Uses dt.tzinfo if tz is None. datetime_ambiguous(dt, tz=None)

Slide 44

Slide 44 text

dateutil.tz enfold(dt, fold=1) Ambiguous times >>> from dateutil.tz import datetime_ambiguous, enfold, gettz >>> from datetime import datetime >>> dt = enfold(datetime(2018, 11, 4, 1, 30)) >>> dt _DatetimeWithFold(2018, 11, 4, 1, 30) >>> dt.fold 1 >>> tz = gettz('America/New_York') >>> datetime_ambiguous(datetime(2018, 11, 4, 1, 30, tzinfo=tz)) True >>> datetime_ambiguous(datetime(2018, 11, 4, 2, 30, tzinfo=tz)) False datetime_ambiguous(dt, tz=None)

Slide 45

Slide 45 text

dateutil.tz datetime_exists(dt, tz=None) Imaginary times • Returns False if the provided datetime is imaginary. • Uses the datetime’s timezone, or tz if provided. • Returns a datetime that is guaranteed to exist. • Always shifts forward by the size of the “imaginary time” gap. resolve_imaginary(dt)

Slide 46

Slide 46 text

dateutil.tz datetime_exists(dt, tz=None) Imaginary times resolve_imaginary(dt) >>> from dateutil.tz import datetime_exists, gettz, resolve_imaginary >>> from datetime import datetime >>> tz = gettz(‘America/New_York') >>> datetime_exists(datetime(2018, 3, 11, 2, 30, tzinfo=tz)) False >>> datetime_exists(datetime(2018, 3, 11, 3, 30, tzinfo=tz)) True >>> resolve_imaginary(datetime(2018, 3, 11, 2, 30, tzinfo=tz)) datetime.datetime(2018, 3, 11, 3, 30, tzinfo=tzfile('US/Eastern')) >>> resolve_imaginary(datetime(2018, 3, 11, 3, 30, tzinfo=tz)) datetime.datetime(2018, 3, 11, 3, 30, tzinfo=tzfile('US/Eastern'))

Slide 47

Slide 47 text

pytz timezone(name) Get timezone • Accepts an IANA timezone name. • Returns a tzinfo subclass instance for the corresponding IANA timezone definition. utc • Singleton instance for UTC timezone.

Slide 48

Slide 48 text

pytz timezone(name) Get timezone utc >>> from datetime import datetime, tzinfo >>> import pytz >>> tz = pytz.timezone('America/New_York') >>> isinstance(tz, tzinfo) True >>> tz.utcoffset(datetime(2018, 1, 1)) datetime.timedelta(-1, 68400) >>> pytz.utc.utcoffset(datetime(2018, 1, 1)) datetime.timedelta(0)

Slide 49

Slide 49 text

pytz tzinfo oddities • pytz’s tzinfo subclasses have a static utcoffset. • Setting a datetime’s tzinfo directly will result in the earliest historical UTC offset for that timezone. (This is not what you want.) • In order to get a tzinfo subclass with the correct UTC offset, you must call localize on the datetime instance. • pytz will then return a datetime with a properly offset tzinfo. tzinfo.localize(dt, is_dst=False)

Slide 50

Slide 50 text

pytz tzinfo oddities tzinfo.localize(dt, is_dst=False) >>> import pytz >>> from datetime import datetime >>> tz = pytz.timezone(‘America/New_York') >>> datetime(2018, 6, 1, tzinfo=tz) datetime.datetime(2018, 6, 1, 0, 0, tzinfo=) >>> tz.localize(datetime(2018, 6, 1)) datetime.datetime(2018, 6, 1, 0, 0, tzinfo=)

Slide 51

Slide 51 text

pytz tzinfo oddities • Because pytz’s UTC offsets are static, modifying a datetime across DST boundary will result in an incorrect offset. • Calling normalize on a datetime with a pytz tzinfo will recompute the timezone’s UTC offset and update the datetime if needed. • Normalize is idempotent. It’s always safe to call on datetimes with a pytz tzinfo subclass. tzinfo.normalize(dt)

Slide 52

Slide 52 text

pytz tzinfo oddities tzinfo.normalize(dt) >>> import pytz >>> from datetime import datetime, timedelta >>> tz = pytz.timezone('America/New_York') >>> dt = tz.localize(datetime(2018, 11, 4)) >>> dt datetime.datetime(2018, 11, 4, 0, 0, tzinfo=) >>> dt + timedelta(days=1) datetime.datetime(2018, 11, 5, 0, 0, tzinfo=) >>> tz.normalize(dt + timedelta(days=1)) datetime.datetime(2018, 11, 4, 23, 0, tzinfo=)

Slide 53

Slide 53 text

pytz Ambiguous times • pytz has no direct support for the “fold” attribute. • Use the localize’s is_dst keyword argument to choose which offset you want for a given ambiguous time. • If a time is ambiguous, calling localize with is_dst=None will raise AmbiguousTimeError.

Slide 54

Slide 54 text

pytz Ambiguous times >>> import pytz >>> from datetime import datetime >>> tz = pytz.timezone('America/New_York') >>> tz.localize(datetime(2018, 11, 4, 1, 30), is_dst=True) datetime.datetime(2018, 11, 4, 1, 30, tzinfo=) >>> tz.localize(datetime(2018, 11, 4, 1, 30), is_dst=False) datetime.datetime(2018, 11, 4, 1, 30, tzinfo=) >>> tz.localize(datetime(2018, 11, 4, 1, 30), is_dst=None) Traceback (most recent call last): File "", line 1, in File "/usr/local/lib/python3.6/site-packages/pytz/tzinfo.py", line 363, in localize raise AmbiguousTimeError(dt) pytz.exceptions.AmbiguousTimeError: 2018-11-04 01:30:00

Slide 55

Slide 55 text

pytz Imaginary times • Use normalize to shift an imaginary datetime backward or forward to an existing datetime. • pytz will shift in the direction that changes the datetime’s UTC offset. • If a time is imaginary, calling localize with is_dst=None will raise NonExistentTimeError.

Slide 56

Slide 56 text

pytz Imaginary times >>> import pytz >>> from datetime import datetime >>> tz = pytz.timezone('America/New_York') >>> dt_standard = tz.localize(datetime(2018, 3, 11, 2, 30), is_dst=False) >>> tz.normalize(dt_standard) datetime.datetime(2018, 3, 11, 3, 30, tzinfo=) >>> dt_daylight = tz.localize(datetime(2018, 3, 11, 2, 30), is_dst=True) >>> tz.normalize(dt_daylight) datetime.datetime(2018, 3, 11, 1, 30, tzinfo=) >>> tz.localize(datetime(2018, 3, 11, 2, 30), is_dst=None) Traceback (most recent call last): File "", line 1, in File "/usr/local/lib/python3.6/site-packages/pytz/tzinfo.py", line 341, in localize raise NonExistentTimeError(dt) pytz.exceptions.NonExistentTimeError: 2018-03-11 02:30:00

Slide 57

Slide 57 text

Best Practices

Slide 58

Slide 58 text

Timezone-Aware Sandwich • Convert datetimes to timezone-aware as early as possible. • Wait as long as possible to convert to timezone-naive (if you must at all). • For simplicity, convert timestamps to UTC datetimes inside the timezone-aware perimeter. • Avoids having to think about DST. • Keeps logging and inspecting locals consistent. (a la Ned Batchelder’s “Unicode Sandwich” *) *https://nedbatchelder.com/text/unipain.html

Slide 59

Slide 59 text

In the face of ambiguity, refuse the temptation to guess • Sometimes, all you have is a datetime with UTC offset. • Refuse the temptation to guess the intended timezone! • This datetime’s wall time is undefined and cannot be correctly inferred.

Slide 60

Slide 60 text

Keep your IANA data current • The IANA timezone database changes several times a year. • Both pytz and dateutil cut new releases when the database updates. • Staying current is the best way to prevent bugs due to changing timezone definitions.

Slide 61

Slide 61 text

Dealing with the edges

Slide 62

Slide 62 text

What do you need to preserve? • If you want to preserve a timestamp, just use UTC. • Preserving wall time is trickier, since the moment in time represented by that wall time could change if its timezone definition changes. • The naive time along with the timezone is required to preserve a wall time. A wall time or a timestamp?

Slide 63

Slide 63 text

Serialization

Slide 64

Slide 64 text

ISO 8601 2018-05-01T12:34:56-08:00 2018-05-01T20:34:56Z • Standard string representation of a datetime. • Supports UTC offset but not timezone names. • Z is short for 00:00 UTC offset.

Slide 65

Slide 65 text

Serializing a timestamp to ISO 8601 datetime.isoformat() datetime.datetime >>> from datetime import datetime, timezone >>> from dateutil.tz import gettz >>> tz = gettz("America/New_York") >>> datetime(2018, 1, 1, tzinfo=tz).isoformat() ‘2018-01-01T00:00:00-05:00' >>> datetime(2018, 1, 1, tzinfo=timezone.utc).isoformat() '2018-01-01T00:00:00+00:00'

Slide 66

Slide 66 text

Deserializing an ISO 8601 timestamp parser.isoparse(dt_str) dateutil.parser >>> from dateutil.parser import isoparse >>> isoparse('2018-01-01T00:00:00Z') datetime.datetime(2018, 1, 1, 0, 0, tzinfo=tzutc()) >>> isoparse('2018-01-01T00:00:00+00:00') datetime.datetime(2018, 1, 1, 0, 0, tzinfo=tzutc()) >>> isoparse('2018-01-01T00:00:00-05:00') datetime.datetime(2018, 1, 1, 0, 0, tzinfo=tzoffset(None, -18000))

Slide 67

Slide 67 text

Serializing a wall time Using a wrapper around gettz >>> from datetime import datetime >>> from dateutil.tz import gettz as _gettz >>> def gettz(name=None): ... tz = _gettz(name=name) ... tz.name = name ... return tz ... >>> dt = datetime(2018, 1, 1, tzinfo=gettz('America/New_York')) >>> (dt.replace(tzinfo=None).isoformat(), dt.tzinfo.name) ('2018-01-01T00:00:00', 'America/New_York') * https://github.com/dateutil/dateutil/issues/76

Slide 68

Slide 68 text

Deserializing a wall time >>> from dateutil.parser import isoparse >>> from dateutil.tz import gettz >>> dt_str, tz_name = ('2018-01-01T00:00:00', ‘America/New_York') >>> isoparse(dt_str).replace(tzinfo=gettz(tz_name)) datetime.datetime(2018, 1, 1, 0, 0, tzinfo=tzfile('US/Eastern'))

Slide 69

Slide 69 text

What about strptime/strftime? • No strptime format string will currently parse the result of datetime.isoformat(). • The %z directive will handle ±HHMM which, is still valid ISO 8601. • As of Python 3.2, deserializing ±MMSS via %z will yield a timezone-aware datetime instance. • Stay tuned for Python 3.7, which introduces datetime.fromisoformat(). (Note that this method is intended to be the reverse datetime.isoformat(), not to be a full ISO 8601 parser.)

Slide 70

Slide 70 text

json json.dumps(obj, … cls=None) JSON encoding a timestamp to ISO 8601 >>> import json >>> from datetime import datetime >>> from dateutil.tz import gettz >>> class DateTimeJSONEncoder(json.JSONEncoder): ... def default(self, obj): ... if isinstance(obj, datetime): ... return obj.isoformat() ... return json.JSONEncoder.default(self, obj) >>> tz = gettz('America/New_York') >>> json.dumps({'created': datetime(2018, 5, 1, tzinfo=tz)}, cls=DateTimeJSONEncoder) '{"created": "2018-05-01T00:00:00-04:00"}'

Slide 71

Slide 71 text

json json.dumps(obj, … cls=None) JSON encoding a wall time to ISO 8601 and timezone >>> import json >>> from datetime import datetime >>> from dateutil.tz import tzfile >>> class WallTimeJSONEncoder(json.JSONEncoder): ... def default(self, obj): ... if isinstance(obj, datetime): ... if isinstance(obj.tzinfo, tzfile): ... return { ... 'datetime': obj.replace(tzinfo=None).isoformat(), ... 'timezone': obj.tzinfo.name, ... } ... return json.JSONEncoder.default(self, obj) >>> json.dumps({'created': datetime(2018, 5, 1, tzinfo=tz)}, cls=DateTimeJSONEncoder) '{"created": {"datetime": "2018-05-01T00:00:00", "timezone": “America/New_York”}}'

Slide 72

Slide 72 text

Persistence

Slide 73

Slide 73 text

Postgres datetime types • Timezone-naive datetime data type. • Since this is the SQL default for timezone, the “without time zone” is optional. • Assumed to represent Postgres’s local timezone when used in operations with timezone-aware datetimes. • Accepts a variety of input types, but ISO 8601 is preferred. timestamp [without time zone]

Slide 74

Slide 74 text

Postgres datetime types timestamp [without time zone] postgres=# CREATE TABLE naive (dt timestamp); CREATE TABLE postgres=# INSERT INTO naive (dt) VALUES ('2018-05-01T00:00:00'); INSERT 0 1 postgres=# SELECT * FROM naive; dt --------------------- 2018-05-01 00:00:00 (1 row)

Slide 75

Slide 75 text

Postgres datetime types • Accepts timezone as UTC offset, short name, or full IANA name. • Postgres computes the UTC offset for the given datetime and timezone and converts to UTC before storing. • Timestamps are converted to Postgres local timezone’s UTC offset for display. • Postgres has no proper timezone type, so it has no facility to properly represent wall times. timestamp with time zone

Slide 76

Slide 76 text

Postgres datetime types timestamp with time zone postgres=# CREATE TABLE aware (dt timestamp with time zone); CREATE TABLE postgres=# INSERT INTO aware (dt) VALUES ('2018-05-01T00:00:00 America/New_York'); INSERT 0 1 postgres=# SELECT * FROM aware; dt ------------------------ 2018-05-01 04:00:00+00 (1 row) postgres=# SET timezone TO 'America/New_York'; SET postgres=# SELECT * FROM aware; dt ------------------------ 2018-05-01 00:00:00-04 (1 row)

Slide 77

Slide 77 text

psycopg2 >>> import psycopg2 >>> from datetime import datetime >>> from dateutil.tz import gettz >>> cursor = psycopg2.connect(host='postgres', user='postgres', dbname='postgres', password=‘mysecretpassword').cursor() >>> cursor.execute('INSERT INTO aware (dt) VALUES (%s)', (datetime(2018, 5, 1, 0, 0, tzinfo=gettz('America/New_York')),)) >>> cursor.execute('SELECT * FROM aware') >>> cursor.fetchall() [(datetime.datetime(2018, 5, 1, 4, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)] >>> cursor.execute('INSERT INTO naive (dt) VALUES (%s)', (datetime(2018, 5, 1, 0, 0),)) >>> cursor.execute('SELECT * FROM naive') >>> cursor.fetchall() [(datetime.datetime(2018, 5, 1, 0, 0),)]

Slide 78

Slide 78 text

MySQL datetime types • Timezone-naive datetime data type. • Tries its best to guess at the format, stores a “zero value” datetime if the format is considered invalid. datetime

Slide 79

Slide 79 text

MySQL datetime types datetime mysql> CREATE TABLE naive (dt datetime); Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO naive (dt) VALUES ('2018-05-01 00:00:00'); Query OK, 1 row affected (0.04 sec) mysql> SELECT * FROM naive; +---------------------+ | dt | +---------------------+ | 2018-05-01 00:00:00 | +---------------------+ 1 row in set (0.00 sec)

Slide 80

Slide 80 text

MySQL datetime types • Timezone-aware datetime data type (for some definition of “aware”). • Does not explicitly accept or display UTC offset. • Assumes all given values are in the session’s timezone. • Stores values in UTC and then converts back to the session’s timezone at query time. • If the retrieving session has a different UTC offset than the storing session, the datetime returned will be different than the datetime stored. timestamp

Slide 81

Slide 81 text

MySQL datetime types timestamp mysql> CREATE TABLE aware (dt timestamp); Query OK, 0 rows affected (0.10 sec) mysql> SET time_zone = 'UTC'; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO aware (dt) VALUES ('2018-05-01 00:00:00'); Query OK, 1 row affected (0.03 sec) mysql> SELECT * FROM aware; +---------------------+ | dt | +---------------------+ | 2018-05-01 00:00:00 | +---------------------+ 1 row in set (0.00 sec)

Slide 82

Slide 82 text

MySQL datetime types timestamp mysql> SET time_zone = '-04:00'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM aware; +---------------------+ | dt | +---------------------+ | 2018-04-30 20:00:00 | +---------------------+ 1 row in set (0.00 sec)

Slide 83

Slide 83 text

Storing wall times • The same rule as serializing applies: you must store the wall time and its timezone in separate columns. • Store the wall time as timezone-naive and reconstruct the timezone- aware datetime in your application layer from the 2 columns.

Slide 84

Slide 84 text

Storing timestamps • In Postgres, it’s fine to store timezone-aware timestamps directly using timestamp with time zone. • In MySQL, convert timestamps to UTC naive before storing to avoid getting tripped up by its implicit offset behavior. • If you need compatibility across multiple relational stores, it’s safest to assume little to no timezone support and store as naive timestamps in UTC.

Slide 85

Slide 85 text

tl;dl • Even if your users are in 1 locale, timezones matter. You can’t escape them. • Using a timezone-aware sandwich in your application will prevent bugs. • Always be aware of whether your data is a timestamp or a wall time. • You can do it! I believe in you! (Aren’t these supposed to be at the beginning?)

Slide 86

Slide 86 text

Special thanks to Paul Ganssle https://www.youtube.com/watch?v=l4UCKCo9FWY https://blog.ganssle.io/tag/timezones.html

Slide 87

Slide 87 text

Questions? David Gouldin @dgouldin david@gould.in