CQL function now() returns a value in my own timezone (UTC+2), but startOfDay() always returns a value in UTC?!

Confluence Development Confluence Cloud
,
1

Yesterday I was investigating a customer problem, and it seems to me that different CQL functions that return time values are working in inconsistent timezones.

Note that the timezone being used is not documented at all: https://developer.atlassian.com/cloud/confluence/cql-functions/#startofday--

Can someone from the Confluence Cloud team check this?

My experiments

I am in the UTC+2 timezone. There is no new page created on my Confluence Cloud site in the last hour.
I create a page a few seconds after 09:00AM in my timezone. (In UTC, it is 07:00AM.)

Testing now()

Wait for 5 minutes, until 09:05AM.
Run this CQL query:

created > now("-3m")

Expected: it should return all pages created after 09:02AM, i.e zero pages.
Actual: it doesn’t find any page.

Then run:

created > now("-10m")

Expected: it should return all pages created after 08:55AM, i.e only that page I’ve just created.
Actual: it finds the page I’ve just created.

It proves that now() returns the time value in my timezone (UTC+2). This is what I expected, great.

Testing startOfDay()

Run this query to get the pages created 440 minutes (= 7 hours plus 20 minutes) after the start of day:

created > startOfDay("+440m")

Expected: it should return all pages created after 07:20AM, i.e the page I’ve just created.
Actual: it returns nothing?!! This is not expected.

Re-run the same query and decrease the time offset:

created > startOfDay("+435m")
created > startOfDay("+430m")
created > startOfDay("+425m")
created > startOfDay("+420m")

…and it still finds nothing?!

The first time it will find the newly created page is when using this offset:

created > startOfDay("+415m")

The only explanation is that startOfDay() works in the UTC timezone?! :thinking:
If so, then it is bug. Or, at least, not consistent with now().

In my interpretation, the arithmetic is this.

  1. In the negative test, it gets 00:00AM in UTC and it adds the 440 minutes offset. The result is 07:20AM in UTC, which is 09:20AM in my timezone. That’s why it doesn’t find anything.
  2. It only starts to find the page when the offset is 415 minutes, because then result of the function is 06:55AM in UTC, which is 08:55AM in my timezone.
1 Like
3

Hi @aron.gombas,

Based on the documentation for the Created field (where startOfDay is a supported function), the time zone used is the user’s configured time zone; this was also confirmed by the Confluence Cloud team.

To cover all the bases, kindly check the user time zone found in the profile if it is UTC or UTC+2.
image

If the behavior remains consistent even after verifying the configured time zone in the profile, please raise an issue in the Developer and Marketplace support portal.

Cheers,
Ian

1 Like
4

My custom timezone is indeed UTC+2, I can see this in the profile screen:

image

5

I have a related question. If it is a Forge app that executes the CQL query, what is the timezone?

Because the Forge app is represented by a user account provisioned for it, I would think that it is the app-user’s timezone that matters. But then, what is the timezone of a Forge app-user? It doesn’t have a real user profile that I can view and edit (or at least I don’t know how)…

2 Likes