import time_travel: A Primer on Timezones in Python

Timezones are one of those things every programmer loves to hate. Most of us, at least in the US, just try to ignore them and hope nobody notices. Then twice a year, we fear with impending doom those 3 small words: Daylight Saving Time.

It doesn't have to be this way. Armed with some best practices and a little help from supporting libraries, timezone-related bugs can be a thing of the past.

This talk explores standard library and 3rd party library timezone support, as well as persistence and serialization techniques for timezone-aware datetimes. By the end of the talk, the listener should feel confident in their ability to correctly store, send, receive, and manipulate datetime objects in any timezone.

Transcript

1. import time_travel A Primer on Timezones in Python by David Gouldin @dgouldin
2. Why support timezones?
3. 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.
4. I have some bad news.
5. 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.
6. Ok, but how hard can it be?
7. By Evan Siroky
 CC BY-SA 4.0 (https://creativecommons.org/licenses/by-sa/4.0)
 via Wikimedia Commons
8. Who am I? And why do I care about timezones?
10. 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.
11. Our customers don’t care about timezone bugs. They only care about the important meetings we’re scheduling for them.
12. What makes a timezone?
13. 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 =
14. Vienna Airport by Martin Tomitsch
 https://www.flickr.com/photos/martinonflickr/3001031996/
15. 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)
16. 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
17. 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
18. 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, # <https://books.google.com/books?id=ZaVCAQAAIAAJ>, 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.
19. 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”.
20. 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
21. 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
22. Timezones in the Python Standard Library
23. 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.
24. 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)
25. 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)
26. 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)
27. 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)
28. 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())
29. 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”.
30. 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.
31. 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)
32. 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.
33. 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'))
34. 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.
35. 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'))
36. 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.
37. 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 "<stdin>", line 1, in <module> TypeError: can't subtract offset-naive and offset-aware datetimes
38. 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)
39. 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
40. 3rd Party Timezone Libraries It’s dangerous to go alone
41. 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().
42. 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
43. 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)
44. 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)
45. 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)
46. 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'))
47. 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.
48. 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)
49. 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)
50. 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=<DstTzInfo 'America/New_York' LMT-1 day, 19:04:00 STD>) >>> tz.localize(datetime(2018, 6, 1)) datetime.datetime(2018, 6, 1, 0, 0, tzinfo=<DstTzInfo 'America/New_York' EDT-1 day, 20:00:00 DST>)
51. 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)
52. 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=<DstTzInfo 'America/New_York' EDT-1 day, 20:00:00 DST>) >>> dt + timedelta(days=1) datetime.datetime(2018, 11, 5, 0, 0, tzinfo=<DstTzInfo 'America/New_York' EDT-1 day, 20:00:00 DST>) >>> tz.normalize(dt + timedelta(days=1)) datetime.datetime(2018, 11, 4, 23, 0, tzinfo=<DstTzInfo 'America/New_York' EST-1 day, 19:00:00 STD>)
53. 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.
54. 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=<DstTzInfo 'America/ New_York' EDT-1 day, 20:00:00 DST>) >>> tz.localize(datetime(2018, 11, 4, 1, 30), is_dst=False) datetime.datetime(2018, 11, 4, 1, 30, tzinfo=<DstTzInfo 'America/ New_York' EST-1 day, 19:00:00 STD>) >>> tz.localize(datetime(2018, 11, 4, 1, 30), is_dst=None) Traceback (most recent call last): File "<stdin>", line 1, in <module> 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
55. 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.
56. 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=<DstTzInfo 'America/New_York' EDT-1 day, 20:00:00 DST>) >>> 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=<DstTzInfo 'America/New_York' EST-1 day, 19:00:00 STD>) >>> tz.localize(datetime(2018, 3, 11, 2, 30), is_dst=None) Traceback (most recent call last): File "<stdin>", line 1, in <module> 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
57. Best Practices
58. 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
59. 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.
60. 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.
61. Dealing with the edges
62. 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?
63. Serialization
64. 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.
65. 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'
66. 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))
67. 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
68. 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'))
69. 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.)
70. 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"}'
71. 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”}}'
72. Persistence
73. 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]
74. 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)
75. 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
76. 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)
77. 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),)]
78. 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
79. 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)
80. 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
81. 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)
82. 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)
83. 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.
84. 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.
85. 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?)
86. Special thanks to Paul Ganssle https://www.youtube.com/watch?v=l4UCKCo9FWY https://blog.ganssle.io/tag/timezones.html
87. Questions? David Gouldin @dgouldin david@gould.in