Time zone handling and `datepart()` in Seq 2021.2

Seq internally stores all event timestamps in UTC.

When you browse logs in the Seq UI, these get converted into your local time zone for display, so an error that happens while you're out enjoying an afternoon cup of coffee in Seattle will show up in search results with a timestamp at 1 pm, even if behind the scenes, the data in the event store is stamped with 3 am the next day, in UTC.

Time zones can be awkward to work with, so it's just as well that applications like Seq convert between them automatically for us, behind the scenes.

Server-side, where there's no "current user" running a browser that's time zone aware, it's a different story. If you want to write a query that determines whether an event happened on a Wednesday, then the query will have to be specific about whose Wednesday.

This comes up in all kinds of places, including in alerting where it may only be necessary to trigger an alert on specific times or days.

As a simple example, imagine that during the week we don't mind chasing down minor issues, but on Sundays we only want to drop everything and investigate
🚨 if the error rate is really high.

The alert condition over one hour slices will usually look like:

count > 10

But on Sundays we bump that up to:

count > 50

In Seq, we can combine these and write our alert condition as:

-- Day numbers begin with Sunday at zero
if datepart(now(), 'weekday', offsetin('America/Los_Angeles', now())) = 0
    then count > 50
    else count > 10

Two functions, both new to Seq 2021.2, make this work - datepart() and offsetin(). Let's take a closer look at them both!

datepart(datetime, part, offset)

This function is Seq's version of an SQL classic: datepart() takes its first argument, a moment in time, and picks this apart into its simple numeric consitutents.

-- Print the day part of the first 10 events in the selected time window
select datepart(@Timestamp, 'day', 10h) from stream limit 10

The second argument specifies which part of the date we're interested in. On the twenty-third of March 2021, then the day part retrieved by the query above will be 23, the month part would be 3, and the year part, 2021.

The part needs to be specified as a 'string'. Supported values are:

Whether a particular event occurred on the 23rd or the 24th depends on where in the world you are. This is specified for datepart() in the third parameter, the offset of our time zone from UTC. We can write 10h for ten hours ahead, -7h30m for seven and a half hours behind, and so on.

Our sunny little office is in the sunny CBD of sunny Brisbane, on the east coast of sunny Australia. It's never really anything but sunny, so we don't use daylight savings time (DST) here. Times and dates in Brisbane are always exactly ten hours ahead of UTC, and so the expression above will correctly return 23 if @Timestamp was on our 23rd, and 24 if it was the 24th.

offsetin(timezone, instant)

Just a few hundred kilometers to the south of here, or in rainy Seattle where this story began 😄 things aren't so simple. The time zone offset in those places will depend on when in the year the event occurs. Sydney is currently eleven hours ahead, but will soon drop back to ten hours ahead in line with us.

This is where the offsetin() function enters the picture. It accepts a time zone and an instant in time, and returns the offset in that time zone at that instant.

To determine the offset of a time zone, we first need to name it. The closest thing to a standard for time zone names is the IANA time zone database. It generally uses a Country/City style naming scheme.

Most cities aren't allocated their own time zone name. In the case of Seattle, the IANA time zone name is America/Los_Angeles, as we saw way back at the start of this post. Wikipedia helpfully publishes a complete list of IANA time zone names, and this is the list to search if you're unsure what time zone name to use.

The offset in Seattle as I'm writing this is:

-- Prints '-07:00:00' right now :-)
select ToTimeString(offsetin('America/Los_Angeles', now()))

You'll most often see offsetin() together with datepart(), but we're out to build comprehensive date and time functionality into Seq, so we've chosen to expose it as a separate function that's useful in its own right.

Learning more

Since datepart() and offsetin() aren't functions you're likely to reach for day-to-day with Seq, we've created a new summary page in the documentation that will get you back up to speed quickly any time you need to dust them off.

Happy logging! 🌞