Midnight Bug With PostgreSQL
Working with time and time zones, as programmers, can be a huge frustration. Storing everything as UTC timestamps doesn't always solve every problem, but it's a good general rule nonetheless. Scheduling things to run at localized times is one scenario where you need a time and timezone. I'm going to walk through a bug that I encountered scheduling reports involving the time 00:00:00.
Time is Tricky
In Progress Plum, there's an Elixir process that checks the database in one-minute internals for reports that should be run within the next minute. Report are saved with localized times and also include the timezone of the report creator. This ensures that the report runs at the same time every day relative to the creator's time zone and can account for Daylight Savings quirks.
Here's how the query used to look.
select r.* from reports r where r.local_time > (now() at time zone r.timezone)::time and r.local_time <= (now() at time zone r.timezone + '1 min')::time
This query is straightforward and easy to reason about. For example, for a report that is supposed to run at 9 am in Los Angeles is expected to run at 15:00:00 UTC whenever Daylight Savings isn't observed. That would mean I can expect that report to be scheduled any time between 14:59:00 to 14:59:59.
However, a report that should run at midnight (00:00:00) for any timezone would never run. I would expect a midnight report to be scheduled anywhere between 23:59:00 and 00:00:00. This is what the query looks like with time values.
select r.* from reports r where '00:00:00'::time > '23:59:00'::time and '00:00:00'::time <= '00:00:00'::time
The first where clause would always fail because times can never be less than 00:00:00.
The Incorrect Solution
My attempt to fix the query to be correct was to do a case expression whenever the time is 00:00:00 because the upper bound of
TIME is 24:00:00.
select r.* from reports r where (case r.local_time when '00:00:00'::time then '24:00:00'::time else r.local_time end) > (now() at time zone r.timezone)::time and r.local_time <= (now() at time zone r.timezone + '1 min')::time
The problem with this query is that a midnight report is always returned regardless of the time.
Another solution I came up with was to generate a series of 1-second intervals between 23:59:00 and 00:00:00. Even though this solution was correct, it seemed overly complex and likely performed poorly.
select r.* from reports r where r.local_time = any(array(generate_series( '2018-12-01T23:59:00Z'::timestamptz at time zone 'America/Los_Angeles', '2018-12-01T23:59:00Z'::timestamptz at time zone 'America/Los_Angeles' + '1 min', '1 sec' )))::time
After all of these trial and errors, I finally arrived at a much simpler solution that works great.
select r.* from reports r where r.local_time = date_trunc( 'minute', now() at time zone 'Los_Angeles' + '1 min' )::time
This query works by adding 1 minute to the current timestamp and truncates the timestamp down to a whole second. This accounts for when the day switches over because the time is properly set to 00:00:00. For example, a timestamp like
2018-12-01T23:59:07 with 1 minute added is
2018-12-02T00:00:07.Once trancated and casted to time, it's 00:00:00; exactly what I was looking for.
Time is still complex problem to deal with as a progammer. It's important to think through the problem you're trying to solve and the sort of guarantees you want when dealing with time. Also, unit tests are your friend.