How can I make Ecto to show that a record was inserted/updated at 17:25hrs, my computer’s time at the moment, instead of 6 hours ahead?
I am writing my first WebApp, after +30 years developing systems, for an enterprise with ~15 employees delivering groceries within the city limit.
The fields show ~U[2024-12-12 23:13:47], from psql and iex.
Since my time zone is UTC-6 I guess it sounds just about right. I understand that the Time Zone is important if I am handling distinct time zones … but I am not.
Hoping to force Ecto to use my local timestamp, I changed the config/config.exs file from
And also all the schemas inside my Context directory from:
timestamps(type: :utc_datetime)
to
timestamps(type: :naive_datetime)
Then I dropped/migrated my database and after running my priv/repo/seeds.exs using the functions created by mix phx.gen.live task (MyApp.Context.create_*) the dates are still 6 hours ahead, but with an “N” instead of a “U”: ~N[2024-12-12 23:25:52].
In general, I would strongly advise that you store all data in UTC, and then simply convert to the desired timezone you want to display things in at the time you want to display it.
There are some caveats to that. e.g. if it is the time of a future event.
Generally, store them in the database as UTC and display them in local time with DateTime.shift_zone! and in psql with x AT TIME ZONE ..., which you might need to write as x AT TIME ZONE 'UTC' AT TIME ZONE 'Australia/Brisbane' to tell it that the original is in UTC.
Well, I was hoping that using the Naive version, it would store the local time. I totally failed in that matter. Which leads me to 2 points. One is a rhetoric question the other is an Elixir question.
1. Tons of flops being wasted in the Whole Wide Elixir World.
I totally understand there are cases when the Time Zone conversion is needed. No doubt about it … but not always.
So, why is the Elixir Team forcing Everybody, even when it is not needed, to do Time Zone Calculations?
I don’t have a deep knowledge about how computers works, but it looks to me that:
We get the date and time from the computer’s clock.
The data is converted to a UTC-timestamp.
The UTC-timestamp is stored in the DB.
And then, after a while:
We get the UTC-timestamp from the DB.
The UTC-timestamp is converted back to its original Local Time Zone. (Why?)
We show the information in the screen.
I think it could be better to let the timestamp in its Local Time Zone by default. If the computer’s clock says 7:25am, the inserted_at field will also say 7:25am. No calculation needed … at all. Plain, simple, easy. Then, when a Time Zone Calculation is needed, and only then, the developer will convert the timestamp from its Local Time Zone to whatever Time Zone is needed.
I mean, if we need to do a calculation from UTC to Local Time Zone every single time we manage a timestamp, why not calculate from a Local Time Zone to another Time Zone only when it is really needed?
But that’s just me.
2. Is there a way to include the conversion at the Schema level?
I managed to include the dependency for tzdata and all the way up to use the DateTime.shift_zone! function to show the timestamp the way I need.
Is there a way to include that function in the Schema so I could have the right Time Zone ready out of the gate? Or I need to include it, lets say, in my MyApp.Context.get_employee!(3) function?
Ok…I think I am a lot more lost than what I originally thought. I am totally failing to understand what’s the problem with a future event. Other than that, I really appreciate that you were the one that actually mentioned some sample functions and commands, I really do.
I mean, I know the computer understands <, > or = (Less Than, Greater Than or Equal To), but the computer has no sense about past nor future.
Let’s say it is 10am. How come that is it OK for the computer to handle a 9am timestamp that is in the past, but it might be a problem to handle an 11am timestamp that is in the future? I don’t understand.
The computer will be just fine running calculations back and forth between 9am to 10am, but it might have problems running calculating back and forth between 9am and 11am, because 11am has not come yet. It doesn’t make sense to me.
It seems like … there are a lot of things I still need to learn.
I’m not expert in this, so just guessing now, but time zones are really complicated in practice. For example depending on the season Romania is in UTC+2 or UTC+3, see: Time in Romania - Wikipedia, so 3 pm is not always equal to 3 pm
But that’s not the best, as you now - it’s rather a political decisions, so in practice 2 cities which are in exactly same meridian could be in different time zone, so we’ve got: 3 pm (city A standard time) is not always equal to 3 pm (city A summer time) which is not always equal to 3 pm (city B standard time) which is not always equal to 3 pm (city B summer time).
Inserting date time with UTC-6 says nothing in real world use case. That’s how we run into special SQL syntax like the one mentioned by @cmo i.e. AT TIME ZONE "…/…". Storing everything in UTC is not just extra step, but a huge simplicity, because you don’t have to deal with time zones until you show the information in the screen.
Just think about case like: you have to add 1 hour to some date time, but it’s 43 minutes to midnight and on next day the rules would change and you would have to deal with summer time. Now take in mind that in every country rules are different and said summer time starts and ends in other day … Dealing with all that stuff would be a nightmare and it would be extremely error prone.
Coordinated Universal Time (UTC) is the primary time standard globally used to regulate clocks and time. It establishes a reference for the current time, forming the basis for civil time and time zones. UTC facilitates international communication, navigation, scientific research, and commerce.
(…)
UTC does not change with a change of seasons, but local time or civil time may change if a time zone jurisdiction observes daylight saving time (summer time). For example, local time on the east coast of the United States is five hours behind UTC during winter, but four hours behind while daylight saving is observed there
By today we do not know if on April 1st 2026 in Hamburg there will be DST in place or not. Neither do you know, if on that date Hamburg will still belong to the Timezone “Europe/Berlin” of if it perhaps moves to “Europe/Amsterdam” or establishes “Europe/Hamburg” by then.
This is why you store future events naively, and only store them with a timezone once the even happened and you know what the timezone actually was.
edti
As you only talked about a -6 hours offset and no actual timezone, if you are in a timezone that has or might get or even had DST in the past, the timezone or offset at least is historical data you want to keep, as 17:00 during summer is not the same as 17:00 during winter.
So…once again…I got it totally wrong. The problem is not the time itself, but the Time Zone.
They are like … floating around. In the past we already know exactly where they were at a particular moment, but we don’t know where they would be in the future, or if they would even exist at that moment. So, we picked a fixed position, UTC, and work from there.
Totally makes sense now.
Just a part of my brain is still fighting me: <Conversation with myself>
— NO, NO, NO!!! We were talking about the insertED_at/updatED_at fields, they are in the past. Still no conversion is needed!!!
— HEY!!! … He took the time to explain what’s the problem with future events. You were wrong and he kindly explained why you were wrong. He is already seeing things you could not. He is trying to save you from future problems, so … say “Thank you” and zip it!!! He’s helping us. </Conversation with myself>
I had heard that the Elixir Community is a great community, but now I am seeing it by myself. I really appreciate your kindness and time to explain this. I was trying to avoid a minor discomfort, but that would lead me to future problems.
Modern history knows occasions when countries or even only parts of countries changed their timezones. Also the EU is not far away from abandoning the whole winter / summer time stuff as well.
Additionally, if you want to store a recurring event and we still have the winter / summer time then you’d have a nasty surprise how your “D&D night every Friday evening at 19:00” suddenly jumps one hour behind or ahead if you used local timezone.
Storing UTC, or even as some argue – textual representation of the desired future date + time – is more future-proof. Allows you to cope with the shifting sands beneath our feet.
Yes, it totally makes sense. My city was not going ahead during the hot season like other cities in my country, we were going behind during the cold season, and a few years ago we abandoned the daylight time saving. So, I now totally get it.
Thank you very much for your time and effort. Best regards,
I have seen that happened in my city. Fortunately, I didn’t have any problem with my applications, I was not even aware of that problem, but I have been developing locally only. That does not prevents it from happening in the future, though. Thank you for making me aware that those shifting sands exists.
Time is a tricky subject, because we commonly tend to gloss over the details. You were already made aware of the issues with timezones and “the future”, but I’d also argue you’re not that wrong either about local time as shown by this nice blog post:
There’s really two kinds of times. There’s some notion of absolute time (commonly UTC) which is a continuous stream of time, which can be compared with each other, but there’s also wall time as in the time you see when looking at a clock (on the wall). For scheduling and other usecases around “time of day” you might actually care about the walltime, not UTC. If you want to have the freedom of “both” values you need to store not just a datetime, but also the releated timezone (and for future datetimes maybe both wall datetime and utc datetime, see TzDatetime — tz_datetime v0.1.3).
For inserted_at/updated_at people are usually mostly concerned about the relative timing of things, so just utc is generally enough, but that doesn’t mean it’s always enough.
Simple: 10x less code rule. You could have your code base full of (what) if or make a simple pipe with no edge cases. It’s much simpler to add |> to_local_time() rather than:
|> then(fn
data when future_event -> future_staff(data)
data -> data
end)
For sure Elixir is extendable in various places. You can create a DateTime-based framework, integrate it with ecto by using fragment/1 macro and deal with any possible consequences in future, but the question is … do you need a new framework when you just want to show the a local time?
Please pay attention that Elixir is generic purpose. It uses English, UTF-8, UTC and many other stuff we call “standards” in programming. Supporting local time in database requires from core team support all cases (including future) which is extremely hard. It’s simpler to deal with one language, encoding and time zone and convert them when really needed than supporting everything including conversion from and to every of those.
Simply when we exclude such problematic cases the only one left is simple UTC support. We do not lose local time support when showing time, so we don’t win anything for support it in databases. That’s pragmatic and practical. That’s something that would always work in production. That way you develop deploy your apps faster.
The problem is not that it’s hard – elixir still needs to deal with the hard parts – but the maintenance. Elixir releases every 6 months, timezone databases sometimes update many times a year. Hence elixir chose to externalize timezone databases to third party providers, which can more freely update and provide latest information. Shipping the utc only tz db implemenation with elixir is a reasonable tradeoff for letting people use elixir without always needing a third party timezone library.
I was reading that there was an after_load callback in the Ecto.Schema, but not any more. It seems like it could be used to calculate virtual fields right after a record was loaded. It looked like the perfect place to convert the dates.
These days, what, or when in the retrieving process, is the preferred way to convert the dates from UTC to my local time zone? I understand I could convert them when rendering the page, but I would like to have the dates converted as close to the Schema as possible. I mean, if we all are converting dates, I guess there should be some sort of “standard” by now. Right?
I remember we had this article mentioned in past. After some time I think that the overall idea is completely wrong. It’s about aggregating Time events in specified Date range. In such cases I would not store DateTime or NaiveDateTIme. It’s not really about UTC vs local time. I would store Date and Time separately or store NaiveDateTime and place.
It’s not even about time zone, but where you were and what you did, so for example extra field like in_travel could be used or activity_type enum with values like residence, travel, family visit and so on.
There are lots of possible solutions and blaming UTC for a bad choice of table structure is wrong in my opinion. This article is not about a problem dealing with UTC itself, but about an edge cases that a developer did not think when writing code, so yes standards are not solutions for everything if you use them in wrong way.
That’s one point, but I remember that the idea about formatting numbers like 10_000 was dropped because it was “hard” i.e. it heavily depends on locale and there was no single standard saying what’s better 1000 vs 1_000 + not every language have same rules. While the discussion was about 1_000 in Japan people do not say 10 thousand, but (1) 万 (man), so there could be 1000_0000(1000万) which is 10_000_000 for us.
I feel like you missed my point. Yes UTC by itself is not the problem, but telling people to “just use UTC, it’ll be fine” is. You’re correct that there are better ways to store time of day related data, but it takes an understanding of the complexities of time to get to that result.
Take a look at Ecto.Type. It’s used to convert between representations of a value. It can do both conversion of input as well as autogenerated values.
Maybe I was not clear enough, but I fully understand it. It’s not even related to programming. In the entire world there is no single rule to cover all cases. I think in many countries you may be sued if you give wrong advices (especially financial ones). To give advice or post a solution on forum we have to know context as advising same thing in 2 cases may give completely different results.
Things like “Be descriptive” and “ask/look for the context” are obvious for me and I believe they should also be basics when educating children. This way people would have much more creative solutions for many problems.
Unfortunately in many countries the educational system derives from the Prussian one i.e. “listen to orders” and “don’t ask why”. This was “good” for many countries to have citizens prepared for upcoming wars. Now it’s overused by governments and corporations, so “creativity” today for mass means “blindly follow GPS” and “get a few bucks for that”.
