How does Ecto know the current UTC date/time?

Written using Ecto 3.9.4, Elixir 1.14.3 and Erlang 25.2.2

I recently spent some time looking into a timezone-related issue in an app I was working on, and one of the things I noticed was that Ecto was correctly saving timestamps for inserted_at/updated_at in UTC, despite my database being on my local machine (and not configured to be in UTC, hence issues).

This is the expected behaviour, so at the time I thought nothing of it. But I never told my app or Ecto what time zone I/my database are in - how exactly does Ecto know how to convert from my local time to UTC? Is it doing any conversion at all? Are timezones a figment of my imagination? And thus I started investigating.

🔗 Ecto timestamps

When we use timestamps() as part of an Ecto schema definition, it calls a macro that generates two fields for insert/update timestamps. These are actually pretty configurable - but by default they use :naive_datetime for their field type, and they call a private __timestamps__ function when it's time to actually generate a timestamp.

For a :naive_datetime field, __timestamps__ will generate a timestamp by calling NaiveDateTime.utc_now/0, and removing the microsecond value. Now we're getting somewhere.

🔗 NaiveDateTime

I've always been a little bit murky about the differences between different date/time-related types. In this case, it turns out that there's no difference between using :naive_datetime and :utc_datetime - both generate a timestamp in UTC, but :utc_datetime will record that it is in UTC (and :naive_datetime just discards timezone-related information).

(There are some interesting issues around NaiveDateTime - for example, if you use it as a pseudo-local-time, it may not actually represent a valid time due to daylight savings or other time changes! They don't apply here though.)

NaiveDateTime generates current UTC timestamps by calling Erlang's :os.system_time/0, which returns an integer - a Unix timestamp.

🔗 Unix timestamps

Unix timestamps don't actually encode any timezone-related information - they simply record the number of seconds since the Unix epoch, January 1, 1970, at midnight UTC. If you've ever seen Jan 1 1970 on a website, it's likely because they've incorrectly tried to parse 0 (or some other not-a-real-timestamp value) as a Unix timestamp.

So in this case, Ecto isn't doing any timezone-related conversion magic, it's reading my system time in UTC, and using that value as-is. Wait, how does Erlang know what the system time is in UTC?

🔗 Erlang `os:system_time`

The docs about OS system time in Erlang has some interesting notes about the accuracy of system time. (Let's do the time warp again!) It also tells us how Erlang sources the system time:

iex(1)> :erlang.system_info(:os_system_time_source)
[
  function: :clock_gettime,
  clock_id: :CLOCK_REALTIME,
  resolution: 1000000,
  parallel: :yes,
  time: 1676519847842762000
]

I'm on a Mac, so Erlang uses the POSIX clock_gettime function to fetch the current "realtime" clock value, aka the wall clock time. (On Windows, a different function is used.). clock_gettime appears to be part of GNU core itself - and this is sadly where my trail ends. I suspect from here it would go into the hardware clock, present in every computer to keep track of time even when the system is turned off.

This was a fun little deep dive for me, and I've learned about different types of system clocks, different types of date/time-types, and also the fun tidbit that Windows apparently uses a count of the number of 100-nanosecond ticks since 1 January 1601 00:00:00 UT as reckoned in the proleptic Gregorian calendar as its system time? I hope you found it interesting as well!

(Thanks to Theo Harris, Martin Stannard and Chris Hopkins for proofreading and providing useful feedback on this post!)