Denny Biasiolli - The time paradox: Building Timezone-safe python/django applications.

This talk focuses on the challenges of handling time zones in software applications, particularly in the context of finance. The speaker recounts a real-world issue faced by the finance team regarding unexpected revenue reporting due to improper time zone handling in the database. It emphasizes the importance of storing timestamps in UTC to ensure consistency across different regions and situations, and discusses the significance of user intent when scheduling tasks, highlighting the distinction between fixed offsets and actual time zones. The speaker also explores technical implementations in Python and JavaScript, explaining how to effectively manage date and time data across various frameworks such as Django and PostgreSQL. Additionally, the talk covers common pitfalls, such as daylight saving time changes, and offers strategies for ensuring accurate time representation for users around the world.

Yeah, I was trying to talk really slow just to make uncomfortable the people behind the scenes. But, yeah. Thank you for your job, guys. Sorry. Uh well, good afternoon, I should say, or good morning if you are in New York, or good evening, good night if you are in Tokyo, maybe. We don't know. We are here, so good afternoon. And this talk will be about time zones.

Yep. That's the reason for my intro. Let's start with a a brief story from our finance team. In April, one day, uh the head of finance called us asking a strange question about why our US East Coast revenue [applause] is lower than expected. Well, well, it turned out uh lots of payments from March 31 didn't make it to Q1. And well, what's the reason? Let's analyze this.

And the query from our Python ORM, Django especially, correct. Kind of correct because well, we were filtering everything from quarter start to quarter end, and everything should be fine. then looking at the row SQL query, it was the same thing. So, filtering by the date. the limit date for the quarter, and everything was great. It runs official revenue recognition recognition and everything. And well, what was the

problem? Well, let's try to analyze one of the payments that never made it to Q1 the time. One of the customers paid that amount of money on March 31 at 7:17 local time, New York New York time. And then that was recorded in our database correctly. So New York time minus 5 hours from UTC. uh we store that in UTC in our database and you may well

spoiler alert, you may notice the problem here. So all the queries on the database, Django settings, everything uh everything was in UTC. So everything was moved to Q2 to the second quarter instead of staying in the first quarter like it should have been. That led to like money disappearing from Q1 and wrong tax provisioning different paying payments with sale commissions not correct and everything. And last but

not least, board deck and auditors start asking uncomfortable questions. welcome here to time zone therapy. >> [sighs] >> How many of you need to handle different time zones in their application? Yeah. Lots of you. I think that's the reason you're here, I suppose. And well, I used to think, well, it's just a number that you store in the database. Who cares? It works. not exactly. So let's

start from the beginning. Uh what's a time stamp? Well, a time stamp is a record in printed or digital form that show the time at which something happened or was done. Easy. Perfect. Then, to represent this, you have different standards, different ways of representing a timestamp, and you can use this ISO here. It's a an an international standard covering the exchange of date and date time related

data. And basically, it covers this. So, values are ordered from the largest to the smallest. And each component is zero zero padded. And you can remove the stuff you don't need. So, for example, just year, month, day or just the part you need. And well, I'm storing all my uh family photo albums in this format, and my family doesn't know why. And well, this is the reason,

but I won't explain this to them because they are not in the tech world, so in the technical world, to represent a timestamp, to store a timestamp, you use uh the Unix timestamp. That's is a count of a running total of seconds starting from the Unix epoch, that is the January 1st at 1970. If you want to know why, then ask some engineer that one day decided,

"Oh, let's start from this." And well, about timestamps, let's speak two languages, two languages I use for work, so I'm a little bit biased on this. Uh so, in Python, the timestamp is stored as a floating point number representing the number of seconds and um millisecond and so on, fractions of seconds. uh after the in the decimal part since the Unix epoch. And in JavaScript is stored

like an integer number representing milliseconds. So, if you put in the same room one backend developer and one frontend and they need to exchange their sometimes you may end up in having like a date time in 50 in the year 50 55 thousand thousand or in 1970. So, yeah, don't do that unless they know how to convert from one to each other. But, let's just ignore this.

Let's think about a perfect world where they can understand each other and well, a timestamp has no time zone. So, it's a specific moment in time and that moment in time it's the same one everywhere in the world. We added time zones. Not working anymore. in order to understand better the time because we want to I don't know. I want to wake up at 7:00 each here

in I live in Italy, so in Italy. But, if I move to UK or to United States, then I want to wake up at 8:00 7:00 a.m. again, but in their local time because otherwise, well, might be difficult to wake up at 8:00 wrong time and they are already sleeping. So, yeah, it does mean something when you display this to a human. And well, timestamps don't lie.

They are always the same thing and timestamps are just the interpreter. But what is a time zone? Well, a time zone is a region that observes a uniform standard time for legal and commercial social purposes. And well, you can invent your own time zone if you like. For example, in India is UTC plus 5:30. In Nepal, they are nearby. It's plus 5:45 because yeah, you can. So,

let's just invent another time zone with strange offset. And well, this is a quick map of the time zones in the real world. And well, now this is interesting. Uh the green part I decided to highlight uh midnight in New Year's Eve in Rome. to me that midnight is the timestamp at uh 23 UTC. So, 11 uh in the 11:00 p.m. uh the day before in UTC.

But for example, in Los Angeles that time zone that timestamp, sorry. Uh it's on a different time zone. So, it's on a different time. If you want to follow midnight, you the time you want in every single time zone, you need to follow the red um the red line, not the green line. The green line is the timestamp. Now, another nice bug. Always from FinTechs because yeah,

we love FinTech. They wanted uh a daily statement at 9:00 a.m. every day. Every single day. And the job run correctly for months. One morning in March, every statement arrived at 10:00 a.m. So, they lost their morning call and everything. we didn't change nothing. And well, we did think, "Oh, it might be daylight saving time." But, it wasn't the daylight saving time date change in Europe. So,

>> [snorts] >> yeah, maybe it's not. So, the debugging session was, "Well, let's check the logs. Oh, everything Let's go. We check the deployments. We haven't deployed for weeks. So, nothing changed there. Code, again, not changed. Maybe I changed some way. No. wait. Then, one of the engineers had the like enlightenment. And yeah, it was daylight saving time because in the US, they change the daylight saving

time uh weeks before uh Europeans. So, that was the alignment that was broke. And we didn't think about that uh straight away because well, we had to wait a couple of weeks before that in This leads to two problems. So, the first one is when you need to record the past or the present. So, when is happening or happened. And the second one is when you need

to schedule something in the future. First one, when you need to record something that happened, just record in UTC. Forget about everything else because UTC is your reference point. you you be whatever wherever in the world and this is unambiguous and then you can convert that timestamps in your local time zone or in the user local time zone. Then if you want to schedule something you don't

want to schedule you don't want to store the timestamp you want to store the intent of the user. So for example the finance team wanted the report every morning at 9:00 a.m. in their local time zone not at 14 UTC. Uh because 14 UTC it's just a universal representation but it doesn't follow the time zone rules. So again uh if you store a UTC and the DST

changes then you end up in having our problem. Uh well if you store the intent 9:00 a.m. New York time zone and you regenerate uh the scheduling every day every whatever uh you will get the correct UTC every day. And please remember the fixed offset it's not the same as the fixed time zone because UTC-5 in uh New York for example it's uh just for from November

to March. Then it it will be UTC-4 uh while American New York is a rule and it will follow the rule without thinking about the UTC offset uh the fixed UTC offset. Where are they stored? Well they are in the Yana time zone database. Thank you to all the people working on that because they are saving lives every day. And well all the rules for every single

um are defined there. So, when the daylight saving time starts, ends, when there are strange cases like in 2011 in Samoa, they decided to skip an entire day. So, yeah, I don't want to be the engineer committing that change in their database well, okay, let's skip an entire day. What could go wrong when generating date diffs everywhere. So, yeah. Um, well, will create two strange edge cases.

The first one is the spring forward when the clock skips an hour. Second one when the clock repeats an hour. there is a phantom hour because the clock jumps from 2:00 a.m. to 3:00 a.m. So, 2:30 a.m. in New York never happened that day. So, if you are scheduling something, you need to check your framework, your language, your program, and you need to be sure that you

are handling that case because otherwise some libraries throw an exception and your software will crash. Others will move the alert to 3:30 and that might be fine. It depends. So, maybe it's better to warn the user in this case. A nice thing to do. The other case is, uh, the fall back. So, you have a duplicated hour because the clock jumps back from, uh, 2:00 a.m. to

1:00 a.m. So, there's a moment in time that day that happens twice. So, if you have like some long-running task scheduled task, then maybe you don't want to trigger them twice. So, please warn the user or ask the user what they want to do. Or if it's your alarm, then just use the second one, so you have an extra hour of sleep. because of this, days aren't

always 24 hours long. They can be 23 or even 25. So, instead of adding 24 24 hours to your next schedule or 24 24 hours times 16 60 minutes time 60 seconds times 1,000 milliseconds, whatever, if you're using JavaScript. Thank you, JavaScript. Instead, use some kind of third-party libraries or remember to add days instead of units. Quick recap before the second part of this talk. Uh past

events, schedule in uh storing UTC, sorry. Future scheduling, store the intent, so the time and the time zone. And then test daily saving time edge because, well, uh it's nice to have. And then offset is a snapshot, not uh a real time zone, and it's the time zone that carries the let's go to the coding part. Uh so, talking about Python and a little bit of JavaScript

and Django. In Python, we have the datetime object that is used to represent datetimes, and it can be naive or aware. Naive without time zone information, aware with time zone information. And the difference is there. So, the a naive date time that represent that moment in time in the local time zone. If you run in your computer, then it in your time zone. If I run it

on a server in Australia with Australia time zone set, then it will be that time zone. So, not in my local time zone, but in their time Instead, if you want to be explicit, you need to be you need to use aware date times. So, you need to specify the time zone info as well in your date time. In JavaScript, well, [sighs] they have date object. It's

not so good in handling time zone internally. Everything is tracked in UTC But then, usually input and outputs uh are in the local time of the browser or the computer they are running on. So, if your browser, I suppose it's in a different time zone than UTC, then everything is is displayed as that. So, when you create a new date, it will be in the local time

zone. Oh, and just a as a reminder, months in JavaScript are zero indexed And yeah, you need to remember that as well. So, month one is February. A nice thing that JavaScript has is the ability to get the time zone of the browser with this international command here. So, you can get the time zone and work with it or pass it to the back end if you

want. A quick suggestion, if you want to work with time zones with different time zones in JavaScript, maybe it's better to use a third-party library like date-fns or Luxon. I'm using Luxon, but they are pretty good both of them. And in there, they have one indexed month, so you can use one as January and two as February and so on. So, yeah. It's not a a bad

thing. Now, time for the database. In Postgres, timestamp with time zone column type that doesn't store the time zone itself because it stores, again, UTC. It's the the standard. The good thing is that it normalizes the the timestamp to UTC and then converts it back to the desired time zone when you query the data. So, I don't know, maybe it's not a real use case, but sometimes

I'm querying the database directly, and when you query the data, you get the data in the time zone you set or you have set in the database. Where can you customize that in your Postgres instance? Well, you can customize that at server level in the Postgres config file or in the connection string or even at database or user level. Problem is when you have a backend application

running, you have just a connection string, not a single connection string for every single user or time zone all over the So, that might lead to problems. Luckily, you can even configure the time zone in Postgres for a single session or for a single single transaction, and that's the way Django works. So, when you use time zone, you enable that setting in in Django. Again, all daytime

fields will be stored in UTC in the database, but then they will be converted in the active timezone when retrieved. And Django automatically set that in the single transaction command set local timezone to whatever for you. And that local timezone will be the one you configure in your Django settings. So, again, global for everyone using your application. And by default, this is UTC. So, this was our

problem. Users in New York expecting something, but it's in UTC. how can we How can we solve this? I've said this. How can we solve this? We want users being able to get data in their local timezone. Well, in Django, we have this function here, timezone.activate, that activates a specific timezone you And well, you can configure it in a middleware. We will see the code later on.

To to handle your favorite timezone or the user timezone. So, one solution for this is use the timezone as a parameter for the fiscal report. It's great, but not for Django admin, for example. And again, users, they have to specify every time the timezone they need. Another possible solution could be reading the timezone from the user browser. You need JavaScript for this, the command we saw before,

and then you send a cookie to the server. It's great, so it's it's always reads the user device timezone. And you don't need to specify the timezone every time, but if you are using API uh um well, it's not working because it's cookie, so you need to use some kind of header. third solution, just use a header. Yes, it's perfect for API clients, but not for web

users and not for Django admin. Well, let's try with Django settings uh in a in the user profile, so we can the time zone set for the user, but what if the user is moving all around the world? So, it's here, but wants a report for New York because people move around. Well, that's not working. Well, we can combine all of them or pick the one we

need the most in a Django So, we can create Django middleware. This is an extract of the code, not the full code, but yeah, we can read the time zone from the user settings if the user is authenticated. Or we can read the time zone from a custom header if someone is using APIs in our Django application. Or even from a cookie if you have a web,

you can extend the base template for your Django admin or your Django application and just set the cookie when the application starts. And finally, you can double-check oh, do you prefer the user uh like the priority is the user, then cookie, then the header, whatever. You activate the time zone, and for the entire request, the entire session, the time zone will be that one. In the front

end, if you want to use headers for the front end up or just cookie for the base template of Django, you can just read with the common we saw before. You can just read the time zone and then set it to a Django a document cookie or passing that to the header. And that's it. Django will automatically convert the time zone the time zone to the selected

time zone when retrieving record. So, your financial report will be in your desired time zone, not in UTC. And managers will be happy. So, three rules to live by. Well, please in Python use time zone aware datetimes. Well, not Python, in every language. Sorry, not just Python. Uh because naive datetimes doesn't carry the time zone you want to handle. And then with that you need to handle

math manually, and you don't want to do that. Trust me. always start in UTC because it's universal, as we saw in the previous section. And then use a local time zone to display to user. And then third, ask for the time zone for sensitive operation because, well, I might have my time zone set on my browser, but I want the report in another time zone. So, for

sensitive operation, clarify the thing and ask for a for the So, yeah. Thank you for being here at my talk. If you need slides, repository, whatever, just scan the thing.