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 -
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
month part would be
3, and the
The part needs to be specified as a
'string'. Supported values are:
||The year component of
||The month component of
||The day component of
||The hour component of
||The minute component of
||The second component of
||The day of week, as an integer where Sunday =
||The date/time representing 0:00 (midnight AM) at
||The duration from midnight AM to the specified
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
@Timestamp was on our 23rd, and
24 if it was the 24th.
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.
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! 🌞