In the last post, I layed out what I think are the basic rules for dealing with time zones in a Rails app:
- Always deal with times and zones in pairs.
- Do any time calculations in the user’s time zone.
- Interpret times from the user in the user’s time zone.
Those are the basics, but they don’t cover every situation. In particular, we could add a 4th principle:
- Watch out for the edges of your system.
Every time you exchange time information with an outside system, you must either 1) send time zone info to the external system, or 2) once you receive times back from the system, convert them into the correct zone, or 3) both. We’ll look at a common example of an external system in web applications: the database. In our case, it’s Postgres. We’ll have to use strategies 1 and 2.
Writing time zones to Postgres
The last post showed that Rails will convert times to the
Time.zone when retrieving them from the database. What about writing them to the database? Take this example from the reminder app, again for the user in Hawaii:
1 2 3 4 5 6 7 8 9 10 11
Notice that when the time is written to the database, it is stored in UTC, but when we reload the reminder and return
remind_at, Rails converts to HST again.
update_column also works:
1 2 3 4 5
Querying times in Postgres
When possible, use ActiveRecord’s safe interpolation to build time queries. It will correctly handle the time zones for you.
Let’s try to retrieve that reminder that we just updated:
Safe interpolation works:
1 2 3
We query for reminders before 12 noon on August 21. Our reminder is for 10:30am on that day, so it is returned, as we expect.
Remember that times are stored in UTC in the database, so Rails converts your time from HST to UTC when building the SQL query.
Standard Ruby Interpolation
What happens if we try to interpolate with standard Ruby string interpolation?
1 2 3
Standard interpolation does not convert the time to UTC, and Postgres doesn’t know to interpret it as HST. Postgres runs a query for reminders before 12 Noon UTC, which is 10 hours before 12 Noon HST, the time that we meant.
You should be using safe interpolation anyway. If for some reason you need to use regular string interpolation, you can tell Postgres to interpret the string as a time and zone with the Postgres type
TIMESTAMP WITH TIME ZONE.
1 2 3
Postgres correctly interprets
'2014-08-21 12:00:00 -1000' as 12 Noon HST (or 10pm UTC), and returns the correct reminder.
Using Postgres' DATE_TRUNC function with Rails
What if we do time calculations inside the database? We have to pass the time to the database so that it makes the right calculation.
DATE_TRUNC is an example that requires us to do this.
Say you want to do a calender view for your reminder app. You want to return a list of days, and a list of the names of the reminders for each day. You want a speedy query, so you will group by day in SQL using Postgres'
DATE_TRUNC function. How will you group by days in the user’s time zone, rather than the database’s time zone?
The user in Hawaii has a reminder for 5pm HST, August 21st. You try to your DATE_TRUNC query for reminders that week, and this is what you get:
1 2 3 4 5 6 7 8 9 10 11 12
The reminder is showing up for the 22nd, not the 21st. What happened? In Postgres, the time is stored in 2014-08-22 03:00:00 UTC. When
DATE_TRUNC('Day') truncates the time values, what we get is ‘2014-08-22 00:00:00’. We need to convert the time to HST before we truncate the time values. For this purpose we can use Postgres'
TIMESTAMPTZ AT TIME ZONE
1 2 3 4 5 6 7 8 9 10
Now the reminder is showing up on the 21st, which is correct for our user in Hawaii. What’s going on?
Digression: TIMESTAMP WITHOUT TIME ZONE
Rails actually uses
TIMESTAMP WITHOUT TIME ZONE columns in Postgres to store datetimes.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Normally, this would not make sense, because a timestamp without a time zone does not make sense.
'2014-05-29 12:00:00' (without time zone info) could be one of dozens of different times in all the time zones across the globe. There’s no way to know what time is meant without the time zone. Rails gets around this by storing everything in UTC. The database column doesn’t include time zone information, but the time zone is implicitly UTC.
Mashing time zone info into Postgres
Back to the example: in order to do our
DATE_TRUNC calculation, we have to convert the time-zone-less (implicitly UTC) time values to the local time first. We can do this with the Postgres function
TIMESTAMPTZ AT TIME ZONE.
TIMESTAMPTZ is the Postgres type for a timestamp with a time zone included.
TIMESTAMPTZ AT TIME ZONE requires that we tell it which time zone to use:
1 2 3 4 5 6 7 8 9 10 11 12 13
Fortunately, you can pass the time zone as a Postgres
INTERVAL, which can easily be created in from a string:
'-10:00'::INTERVAL. Even more luckily, Rails offers
Time.zone.now.formatted_offset to return the current user’s time zone in exactly that format.
(Potential gotcha: using
Time.zone.formatted_offset, without the
now. That always returns the standard offset, even when it is Daylight Savings Time! My tests were off by 1 hour until I figured that one out.)
We interpolate the
formatted_offset into the SQL query for the solution:
1 2 3 4 5 6 7
Oops. Rails 4 casts values returned in custom
SELECT clauses, but it doesn’t handle timezones in this case. We can get around this inconsistency by getting the raw value, and parsing it ourselves.
1 2 3 4 5 6
We break out
Time.zone.parse, and Ruby parses it correctly, as a Hawaii time. Does this work with our
DATE_TRUNC example problem?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Yup! We get the 21st in HST, which is exactly what we want. We would then call
to_date and get
Thu, 21 Aug 2014 to indicate that we mean the whole day, and not any specific time. We can now display reminders for the correct day.
That’s one example of working with time zones in Postgres. Hopefully it gives you a good idea of the problems that arise when using time zones in conjunction with an external system. We got to try both 1) passing time zone info to the external system so that it could do time zone calculations and 2) converting times into the correct zones after they were returned from the external system. The specifics were particular to Postgres, but the types of issues you deal with should be similar for any external system, be that a reporting sytem, queuing, email, whatever.
If you have any questions about Postgres and time zones, or external services and time zones, feel free to email me at the address in the footer.