Issue Summary

Different CQL functions that return time values are working in inconsistent timezones. CQL function now() returns a value in my own timezone (UTC+2), but startOfDay() always returns a value in UTC

Steps to Reproduce

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?! 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.

1. 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.

Expected Results

created > startOfDay("+440m")

Expected: it should return all pages created after 07:20AM, i.e the page I’ve just created at 09:00AM in UTC+2 timezone

Actual Results

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

created > startOfDay("+415m")

Workaround

The workaround is that when users create the CQL query, they have to assume startOfDay() always returns value in UTC.