Actually doing things in user's time zone
My previous article about timezones turned out to be useful for quite a few folks, which makes me happy. One candle lights another.
Ben Sheldon asked about then actually doing something with those converted times. How do you actually send a newsletter every morning on every working day, regardless of what the user’s time zone is?
There are a number of approaches to this - once you know the UTC time of the delivery. I will cover a few of them, including the one I prefer. Let’s wind the clocks!
🧭 I am currently available for contract work. Hire me to help make your Rails app better!
Approach 1: Anything can be done in Postgres
Remember how I told you that you should store your time_zone as an IANA identifier? There is a good reason for that. PostgreSQL natively supports timezone conversions. Observe:
-- What was the time of that drip experiment in Moscow?
SELECT TIMESTAMP '2011-11-25 18:00' AT TIME ZONE 'Europe/Moscow' AS time_at
For me the return value is 2011-11-25 15:00:00+01. The +01 UTC offset (which is what Postgres stores in the timestamp with time zone type) is because my laptop is currently in London, which is on DST and thus 1 hour ahead of UTC. The timezone setting of the box matters, thus. If we select the same time but a few years forward the conversion is also correct:
SELECT TIMESTAMP '2019-11-25 18:00' AT TIME ZONE 'Europe/Moscow' AS time_at
gives 2019-11-25 16:00:00+01. Now, if your database time is UTC (as it should be) and your application works with UTC internally (and it should!), we can tack another AT TIME ZONE into our SELECT to find out what time it was in UTC:
SELECT (TIMESTAMP '2011-11-25 18:00' AT TIME ZONE 'Europe/Moscow') AT TIME ZONE 'UTC' AS time_at_utc
which gives 2011-11-25 14:00:00 and no UTC offset. This is the time we know now the event took place at, and it is guaranteed to be correct. Now, let’s imagine we have a few users, and they have their timezone settings, and they all want to receive the newsletter at a certain time:
WITH accounts AS (
SELECT * FROM (
VALUES
(1, 'Asia/Kolkata', '9:10'),
(2, 'America/New_York', '9:00'),
(3, 'Europe/Amsterdam', '9:15')
) AS a (id, time_zone, delivery_time)
) SELECT * FROM accounts
| id | time_zone | delivery_time |
|---|---|---|
| 1 | Asia/Kolkata | 9:10 |
| 2 | America/New_York | 9:00 |
| 3 | Europe/Amsterdam | 9:15 |
I am specifically picking Kolkata as one of the timezones to test as it has a peculiar UTC offset - not in whole hours but whole hours and 30 minutes. We can then do a bit of expansion on that and select the 2 next delivery times as UTC (one for today’s date and one for the date after), and then pick the one which is not behind NOW() or - if both are ahead - just the first one (LLMs may or may not have been involved in writing this):
WITH accounts AS (
SELECT * FROM (
VALUES
(1, 'Asia/Kolkata', '9:10'),
(2, 'America/New_York', '9:00'),
(3, 'Europe/Amsterdam', '9:15')
) AS a (id, time_zone, delivery_time)
),
next_delivery_times AS (
SELECT
id,
time_zone,
delivery_time,
-- Create today's date with the delivery time in the user's timezone, then convert to UTC
(CURRENT_DATE + delivery_time::time) AT TIME ZONE time_zone AT TIME ZONE 'UTC' AS today_utc,
-- Create tomorrow's date with the delivery time in the user's timezone, then convert to UTC
(CURRENT_DATE + INTERVAL '1 day' + delivery_time::time) AT TIME ZONE time_zone AT TIME ZONE 'UTC' AS tomorrow_utc,
-- Current UTC time
NOW() AT TIME ZONE 'UTC' AS current_utc
FROM accounts
)
SELECT
id,
time_zone,
delivery_time,
CASE
WHEN today_utc > current_utc THEN today_utc
ELSE tomorrow_utc
END AS next_delivery_at
FROM next_delivery_times
ORDER BY next_delivery_at
which gives
| id | time_zone | delivery_time | next_delivery_at |
|---|---|---|---|
| 1 | Asia/Kolkata | 9:10 | 2025-10-02 03:40:00 UTC |
| 3 | Europe/Amsterdam | 9:15 | 2025-10-02 07:15:00 UTC |
| 2 | America/New_York | 9:00 | 2025-10-02 13:00:00 UTC |
From there, we can enqueue our job to deliver the newsletters. This needs to be run repeatedly. Postgres will even be “smart” enough to adjust the time returned if the chosen time is in a DST transition. For example, during the transition to DST in a given timezone a time like 01:30 won’t exist because the clocks go one hour forward at 01:00 - so Postgres will helpfully adjust the value for you. Neat and it is what you want almost always.
⚠️ Do note that how you get next_delivery_at depends on the Postgres configuration. On some installs (and via Blazer) I could not force Postgres to return me UTC timestamps - they would always get displayed in the timezone of the Postgres server and session. But rest assured - they are correct, just shown with that stupid offset. If you want to make your life easier - use UTC for your database and thank me later.
Approach 2: Our Fugit method from before
Essentially we do what we used to do via Postgres but in Rails. It will be substantially slower but can do the job. It is also likely to be much more readable for others on the team, and allows stuffing multiple events into one pattern (multiple times and multiple days of the week):
Account.find_each do |account|
pattern = "#{account.delivery_time} in #{account.time_zone.name}"
cron = Fugit.do_parse_cronish(pattern)
occurs_at_utc = cron.next_time(_reference = Time.current).utc
DeliverNewsletterJob.set(wait_until: occurs_at_utc).perform_later(account:)
end
Detour: You need some record
There is a caveat though. For example, imagine we did find the next_delivery_at this way, and we enqueue a delivery for account 1 to happen at 03:40 tomorrow. We do it like so:
DeliverNewsletterJob.set(wait_until: next_delivery_row.fetch("next_delivery_at")).perform_later(account: Account.find(next_delivery_row.fetch("id")))
We now have a job in our queue, but - crucially - we can’t make sure we have just one! ActiveJobs do not have a good concept of identity so if the user goes into their settings and changes their delivery time to 8:00, and our “bulk-enqueue newsletters” task runs in 30 minutes, it will enqueue another DeliverNewsletterJob which will run earlier. This will mean that the user will receive 2 newsletters, with 1 hour and 10 minutes between them. Clearly not what we want.
Same if the user changes their delivery time to be later. Our job is already on the queue, so another one is going to be enqueued - and they, again, are going to receive two newsletters.
We could say “let’s just add a newsletter_last_delivered_at column onto users and record when we deliver, and skip if we did so recently”. This can work:
WITH accounts AS (
SELECT * FROM (
VALUES
(1, 'Asia/Kolkata', '9:10', (NOW() - '2 hours'::interval) AT TIME ZONE 'UTC'),
(2, 'America/New_York', '9:00', NULL),
(3, 'Europe/Amsterdam', '9:15', NULL)
) AS a (id, time_zone, delivery_time, last_delivered_at)
),
next_delivery_times AS (
SELECT
id,
time_zone,
delivery_time,
last_delivered_at,
-- Create today's date with the delivery time in the user's timezone, then convert to UTC
(CURRENT_DATE + delivery_time::time) AT TIME ZONE time_zone AT TIME ZONE 'UTC' AS today_utc,
-- Create tomorrow's date with the delivery time in the user's timezone, then convert to UTC
(CURRENT_DATE + INTERVAL '1 day' + delivery_time::time) AT TIME ZONE time_zone AT TIME ZONE 'UTC' AS tomorrow_utc,
-- Current UTC time
NOW() AT TIME ZONE 'UTC' AS current_utc
FROM accounts
), next_delivery_per_account AS (
SELECT
id,
time_zone,
delivery_time,
last_delivered_at,
CASE
WHEN today_utc > current_utc THEN today_utc
ELSE tomorrow_utc
END AS next_delivery_at
FROM next_delivery_times
) SELECT * FROM next_delivery_per_account AS a1
INNER JOIN accounts AS a2 ON a2.id = a1.id AND ((a1.next_delivery_at - a2.last_delivered_at > '24 hours'::interval) OR a2.last_delivered_at IS NULL)
ORDER BY next_delivery_at
and you will see that our user in Kolkata is now excluded from the selection. But we will also need to do this check inside of our delivery job, because our next_delivery_at gets calculated at a point in time. So:
class DeliverNewsletterJob < ApplicationJob
def perform(account:)
return if account.last_delivered_at && account.last_delivered_at > 24.hours.ago
#.. do the delivery
account.touch(:last_delivered_at)
end
end
This is somewhat safe.
Approach 3: Modeling it
We see that using the last_delivered_at creates annoying gymnastics of all sorts. And while showing Postgres prowess is good fun - and Postgres is a great tool - I tend to prefer SQLite lately, for a “nimbler” setup. But that’s not all.
We actually have 2 entities in our setup, even though the second entity does not have its own data model just yet. In addition to an Account we also have a Newsletter. A user is receiving multiple newsletters, in succession. If a user changes their preferred delivery time, they are changing it on their current “pending” newsletter and all the newsletters going forward. Instead of trying to record timestamps all over and do arithmetic, why don’t we actually represent the Newsletter as an actual model?
class Account < ApplicationRecord
has_many :newsletters, -> { order(created_at: :desc) }, dependent: :delete_all do
def current
# Create or find the current pending newsletter
create_or_find_by(account_id: association_owner.id, state: "pending")
end
end
after_save :update_current_newsletter_delivery_time
def deliver_next_newsletter_at
pattern = "#{delivery_time} in #{time_zone.name}"
newsletter_delivery_cron = Fugit.do_parse_cronish(pattern)
newsletter_delivery_cron.next_time(_reference = Time.current).utc
end
def update_current_newsletter_delivery_time
newsletters.current.update!(deliver_at: deliver_next_newsletter_at)
end
end
class Newsletter < ApplicationRecord
enum :state, pending: "pending", delivering: "delivering", delivered: "delivered"
belongs_to :account
before_save { it.idempotency_key = SecureRandom.uuid if it.pending? }
after_save { NewsletterDeliveryJob.perform_later(it, it.idempotency_key) if it.pending? }
def perform_delivery!(idempotency_key_at_enqueue)
# Check whether the idempotency key is the same. If it is not,
# the delivery time has been changed and the job calling us is stale
with_lock do
return unless pending?
return unless idempotency_key == idempotency_key_at_enqueue
# Mark this newsletter as delivering
update!(state: "delivering")
# Immediately create the _next_ newsletter
next_at = account.deliver_next_newsletter_at
account.newsletters.create!(deliver_at: next_at, state: "pending")
end
# ...do the delivery
#
# and mark as delivered
with_lock { it.update!(state: "delivered") }
end
end
class NewsletterDeliveryJob < ApplicationJob
def perform(newsletter, idempotency_key_at_enqueue)
newsletter.perform_delivery!(idempotency_key_at_enqueue)
end
end
There are a number of things to explain here. The idempotency key is needed because while there is just one current newsletter, a job may still be in the queue to process that newsletter with its previous deliver_at value. This will prevent the same newsletter delivering twice or delivering too early.
Another nice aspect of this setup is that a Newsletter may, in itself, have linked Articles - and you will be able to deliver a newsletter just with articles which have been added to it so far.
class Newsletter < ApplicationRecord
# ...
has_and_belongs_to_many :articles, -> { order(created_by: :desc) }
end
class Article < ApplicationRecord
after_create do
self.class.connection.execute <<~SQL
INSERT INTO articles_newsletters (article_id, newsletter_id)
SELECT #{it.id}, newsletters.id
FROM newsletters
WHERE newsletters.state = 'pending'
SQL
end
end
Yet another: you will now have actual records of when your users have received their newsletters, which will be traceable back in time.
And last: there usually are subtle race conditions in systems like this (especially when ActiveRecord callbacks are involved), so tread lightly.
To summarize
“Figuring out when” is just one part of the equation - actually doing them is another. While it does move more into the “durable executions” territory it is not rocket science, just try not to do the same thing twice. And please, please, please configure your server, DB and application to use UTC.
But try to model things well.