jake bowkett

< Back To Blog

StoryDevs Events: Timezone Hell

Re­cent­ly I im­ple­ment­ed an event data­base on StoryDevs. It al­lows users to cre­ate an event whose date and time is au­to­mat­i­cal­ly trans­lat­ed into the view­er's local time­zone. It fea­tures quite so­phis­ti­cat­ed cre­ation and search tools:

• Events can have a du­ra­tion (6pm - 9pm) or be in­stan­ta­neous (for ex­am­ple the launch of a prod­uct at 1pm).
• Events can be occur at a sin­gu­lar mo­ment in time or in local time. New Year's would be a local time event as it hap­pens at dif­fer­ent points in time around the world but the same local time for every­one.
• Events can be one-off or they can be re­cur­ring. The lat­ter au­to­mat­i­cal­ly reset their count­down and up­date their dis­played date with­out the cre­ator hav­ing to edit things.
• Search­es can be strict and only re­turn re­sults with­in the spec­i­fied time range or they can be per­mis­sive and show events that at least over­lap the range se­lect­ed.
• Search­es allow you to spec­i­fy days of the week that in­ter­est you and times there­in. For ex­am­ple Mon­days through Fri­days, 6pm - 9pm.
• Search­es can auto-de­tect your time­zone so that you don't have to even know what it is but there is a man­u­al over­ride if you hap­pen to be using a proxy.

Today I'm going to ex­plain the ba­sics of how this was achieved.

How Dates Are Stored

Story­Devs uses a SQL data­base, Post­gres. All dates are stored with­in it as Unix time­stamps along with the time­zone in a sep­a­rate field. This might seem an odd choice since Post­gres sup­ports date types and al­lows you to do a num­ber of use­ful things with them.

CREATE TABLE IF NOT EXISTS event (
    -- Elide other fields...
    timezone   text     NOT NULL,
    start      bigint   NOT NULL,
    finish     bigint,
    weekly     bool
)

How­ev­er I think it's eas­i­er to use a time­stamp than a date due to the things we're doing in the below ex­am­ples, par­tic­u­lar­ly when it comes to cal­cu­lat­ing the next oc­cur­rence of re­cur­ring events. Per­haps I'm mis­tak­en about that.

Instant Vs. Not

We have events that last some pe­ri­od of time: an hour, a day, a month such as con­fer­ences, game jams, etc. Then we have events that have no du­ra­tion at all like prod­uct launch­es. Based on the table de­f­i­n­i­tion above this means finish may or may not be NULL — we al­ways need to check:

SELECT
    *
FROM
    event
WHERE
    start >= $1 AND 
    (
        (finish IS NOT NULL AND finish < $2) OR
        (finish IS NULL     AND start  < $2)
    )

This is a sim­ple check to gath­er all the events that fall com­plete­ly with­in the clien­t's be­gin­ning and end range (rep­re­sent­ed by $1 and $2 re­spec­tive­ly). It cap­tures both events with a du­ra­tion and in­stant events. This is not a tricky query but it be­comes com­plex as we begin to add to the num­ber of si­mul­ta­ne­ous con­sid­er­a­tions.

Overlapping vs. Not

Next up con­sid­er that our search al­lows strict queries (like the ex­am­ple above) as well as more per­mis­sive queries that can tol­er­ate par­tial over­laps. If the client search­es for events be­tween 6pm and 9pm, tog­gling on the over­lap set­ting shows events that at least begin or end with­in that range:

SELECT
    *
FROM
    event
WHERE
    (start  >= $1 AND start  < $2) OR
    (finish IS NOT NULL AND
     finish >= $1 AND finish < $2)

Again, quite sim­ple. But we need to write both be­cause we don't know which it is until the client sub­mits the search. So these two pre­vi­ous ex­am­ples exist in two dif­fer­ent branch­es of an if state­ment.

Local Vs. Not

Here's where the time­zone col­umn comes in. We need to show both local and non-local time events in our re­sults. But we check whether they're in range dif­fer­ent­ly. The first OR paran­thet­i­cal below shows us sub­tract­ing $3 (rep­re­sent­ing the clien­t's time­zone off­set) from the dates to en­sure they're the right local time.

SELECT
    *
FROM
    event
WHERE
    (
        timezone = 'local' AND
        (start - $3) >= $1 AND 
        (
            (finish IS NOT NULL AND (finish - $3) < $2) OR
            (finish IS NULL     AND (start  - $3) < $2)
        )
    )
    OR
    (
        timezone != 'local' AND
        start >= $1 AND 
        (
            (finish IS NOT NULL AND finish < $2) OR
            (finish IS NULL     AND start  < $2)
        )
    )

Now we're start­ing to see how multi-di­men­sion­al this is get­ting. The above query is for strict ranges. A mir­ror ver­sion needs to exist for the over­lap-per­mit­ting ver­sion too, which I'll leave to your imag­i­na­tion.

Recurring Vs. Not

Last­ly, we need to con­sid­er that events can au­to­mat­i­cal­ly recur week after week. Solv­ing this re­sult­ed in sev­er­al false eu­re­ka mo­ments for me! I kept think­ing I'd fixed it only to re­alise I had­n't.

Orig­i­nal­ly I ad­dressed this by wait­ing for a user to per­form a search. Be­fore search­ing we check all re­cur­ring events have fin­ish dates (or start dates if they have no fin­ish date...) after the pre­sent mo­ment. If they'd "fin­ished" we up­dat­ed the date to the next oc­cur­rence.

This does­n't work. And that's be­cause a sin­gu­lar local event will up­date at dif­fer­ent mo­ments in time across the world. You can't up­date it with­out mak­ing it in­cor­rect for some­one else.

In­stead, I pre­serve the orig­i­nal the orig­i­nal date and do the cal­cu­la­tion dur­ing the query. This, it seemed from my cur­so­ry re­search (I may be wrong!), was bet­ter suit­ed to a Unix time­stamp. It's pos­si­ble to con­vert a Post­gres date to a time­stamp (I do it below) but hon­est­ly there's enough shit going on in this query with­out EXTRACT(bla bla bla) every­where. I think this is eas­i­er to read.

WITH v (now, wk) AS (
    VALUES(
        (SELECT EXTRACT(EPOCH FROM now()))::BIGINT,
        60 * 60 * 24 * 7
    )
)
SELECT
    *
FROM
    event
WHERE
    weekly AND
    now + (wk - ((now - start) % wk)) >= $1 AND
    (
        (finish IS NOT NULL AND
         now > finish AND
         now + (wk - ((now - finish) % wk)) < $2) OR

        (finish IS NULL AND
         now > start AND
         now + (wk - ((now - start) % wk)) < $2)
    )

I use the WITH clause to ef­fec­tive­ly cre­ate two vari­ables: now and wk (mean­ing week). Both now and wk are mea­sured in sec­onds like our start and fin­ish dates.

Let's just look at one sub­com­po­nent of the query:

now + (wk - ((now - start) % wk)) >= $1

If we start from the inner parathe­ses this isn't so bad. First we sub­tract the start date from now. The dif­fer­ence might be hours, a day, 3.7 weeks, etc. Then we use the mod­u­lo op­er­a­tor to get the re­main­der of that dif­fer­ence di­vid­ed by a week. So if the dif­fer­ence were 3.7 weeks we'd get what­ev­er 0.7 weeks is in sec­onds as our re­sult.

So now we have how much time in the week has passed since the last oc­cur­rence minus any whole weeks. Next we in­vert that by sub­tract­ing this frac­tion of a week from a whole week. The re­sult is how many sec­onds until the next oc­cur­rence minus any whole weeks. Then we sim­ply add this onto now to get an ab­solute date.

Non-SQL Code

Thus far I've sim­pli­fied the queries to give you an idea of what's hap­pen­ing which has given me a chance to ex­plain why each com­po­nent is the way it is. Soon we'll get to how I gen­er­ate the full query. It's a lit­tle lengthy though and re­quires some pre­am­ble.

Build­ing queries on the serv­er side in­volves a lot of cond­tion­als, string con­cate­na­tion of SQL code, and some string in­ter­po­la­tion. How­ev­er it's very im­por­tant not to naive­ly use client input with­out es­cap­ing it oth­er­wise we'll end up with a SQL in­jec­tion vul­ner­a­bil­i­ty. This is why we've been using pre­pared state­ments with $n so far.

In Post­gres you must num­ber your es­caped val­ues. This is fine for hand­writ­ten queries with a cou­ple of val­ues you wish to in­sert. But when it comes to 20 or 30 it gets hard to keep track of. So I have a sim­ple type I cre­at­ed to keep count and pro­duce the place­hold­er strings:

type argCount int

func (ac *argCount) Next() string {
    *ac++
    return "$" + strconv.Itoa(int(*ac))
}

When you see arg.Next() below know that it's yield­ing an in­cre­ment­ed string value like $5, $23, etc etc.

As we build this query we're not just con­cerned with date ranges. There's cat­e­gories, set­tings, and so on that are also being con­di­tion­al­ly added. So at the top scope of the gen­er­at­ing func­tion we have two slices, where and args. (If you're not fa­mil­iar with Go a slice is sim­i­lar to a Py­thon list or a Java­Script array but with type safe­ty.)

where = append(where, "category = " + arg.Next())
args = append(args, category)

Once we've fin­ished adding con­di­tions we'll join the where state­ments to­geth­er and con­cate­nate it to the rest of the query. Then it and the args slice will be sup­plied to a data­base dri­ver func­tion which es­capes our ar­gu­ments and per­forms the query for us.

q := `
    SELECT
        *
    FROM
        event`

/*
    Loop through search filter and
    append conditions...
*/

q += " WHERE " + strings.Join(where, " AND ")

var results []event
err := db.Select(&results, q, args...)
if err != nil {
    return err
}

return results

Combining Them All

Now we're ready to look at (al­most) every­thing com­bined. Below is how I gen­er­ate the over­lap­ping branch of the query. As men­tioned above there is a par­al­lel strict branch that I won't show here since it's very sim­i­lar to what's below.

qMaker := func(opening string, offset int) string {
    off := ""
    if offset > 0 {
        off = " - " + strconv.Itoa(offset)
    }
    s := "
/*
    Events set to local timezone have the
    user's timezone offset subtracted from
    event start/finish.
*/
    %s AND
(
    start %s >= %s AND
    start %s  < %s
)
OR
(
     finish IS NOT NULL AND
    (
        (finish %s >= %s AND
         finish %s  < %s) OR

        (start  %s  < %s AND
         finish %s >= %s)
    )
)
OR
(
    -- For recurring events.
    weekly AND
    (
        (finish IS NOT NULL AND now > finish %s) OR
        (finish IS NULL     AND now > start  %s)
    )
    AND
    (
        (now + (wk - ((now - (start  %s)) %% wk)) >= %s AND
         now + (wk - ((now - (start  %s)) %% wk))  < %s) OR

        (finish IS NOT NULL AND
         now + (wk - ((now - (finish %s)) %% wk)) >= %s AND
         now + (wk - ((now - (finish %s)) %% wk))  < %s)
    )
)
"
    return fmt.Sprintf("("+s+")",
        opening,
        off, arg.Next(), off, arg.Next(),
        off, arg.Next(), off, arg.Next(),
        off, arg.Next(), off, arg.Next(),
        off, off,
        off, arg.Next(), off, arg.Next(),
        off, arg.Next(), off, arg.Next())
}

var ss []string
ss = append(ss, qMaker(`timezone  = 'local'`, offset))
ss = append(ss, qMaker(`timezone != 'local'`, 0))
where = append(where, "("+strings.Join(ss, " OR ")+")")
args = append(args,
    s, f, s, f,
    s, f, s, f,
    s, f, s, f,
    s, f, s, f,
    s, f, s, f)

First I de­fine a nest­ed func­tion which is done for read­abil­i­ty pur­pos­es. It's eas­i­er to un­der­stand how the query is being built if it pro­cedes A, B, C rather than hav­ing to jump around the code. (I called it qMak­er as in "query mak­er" - hon­est­ly, I did­n't know what the fuck to call it.)

This func­tion al­lows us to cre­ate local and non-local ver­sions of the query. For local ver­sions we pass in the clien­t's cur­rent time­zone off­set and we cre­ate a lit­tle string for in­ter­po­la­tion that ends up being " - 10", " - -7", etc if a non-zero off­set is sup­plied or an empty string oth­er­wise.

The query is ba­si­cal­ly a com­bi­na­tion of the afore­men­tioned so I won't re­al­ly go over it. We're check­ing if the event pe­ri­od as en­tered in the data­base over­laps the clien­t's de­sired range or, if not, if the next cal­cu­lat­ed oc­curence of it over­laps. All while being mind­ful that finish may be NULL.

The only part that's new is this:

weekly AND
(
    (finish IS NOT NULL AND now > finish %s) OR
    (finish IS NULL     AND now > start  %s)
)

We're check­ing that any re­cur­ring even­t's orig­i­nal fin­ish date is in the past (keep­ing in mind that the start could also be the fin­ish date). This pre­vents re­cur­ring events being back­wards pro­ject­ed into the past where they never ac­tu­al­ly oc­curred.

Then we for­mat the string pass­ing in a whole bunch of val­ues to be in­ter­po­lat­ed, most­ly the client off­set and the place­hold­er $n.

We call the func­tion twice and ap­pend the re­sult­ing string to a tem­po­rary slice ss which is then joined to­geth­er on ei­ther side of an OR op­er­a­tor. This re­sults in some­thing like:

(
    (
        timezone = 'local' AND
        -- The rest of the local conditions...
    )
    OR
    (
        timezone != 'local' AND
        -- The rest of the non-local conditions...
    )
)

Which saves us the time and space of writ­ing ba­si­cal­ly the same thing twice. This in turn is ap­pend­ed to where and the ranges sup­plied by the client are ap­pend­ed to args. Since qMaker is called twice we need to sup­ply dou­ble the amount of arg.Next() calls shown.

Hon­est­ly, the way this works is waste­ful. Post­gres has num­bered place­hold­ers $1, $2, and on which allow re­peat­ed­ly re­fer­ring to one ar­gu­ment. The s and f ar­gu­ments re­al­ly only need to be sup­plied once each. Per­haps I'll alter it in the fu­ture to do this.

Conclusion

Well that's all I can be both­ered writ­ing for now. I hope this has been clear enough to give an idea of how things work. Per­haps in a fu­ture post I can de­scribe how search­es for spe­cif­ic days of the week work, though it's not too dis­sim­i­lar from cal­cu­lat­ing re­cur­ring events.

Thanks for read­ing.