Invalid query for the current day

[martinnovak]

Past Issues Searched

• I have searched open and closed issues to make sure that the bug has not yet been reported

Issue is a Bug Report

• This is a bug report and not a feature request, nor asking for self-hosted support

Using official Plausible Cloud hosting or self-hosting?

Self-hosting

Describe the bug

Hello, I was testing Plausible to be used to track my mac application. I switched to today view, and I didn't see any of the new events to be added.

So, I wondered what was wrong. I saw that the UI generated the following API call:

https://plausible.xxx.com/api/stats/website.com/pages/?period=day&date=2024-10-24&with_imported=true&limit=9

Which generated the following query (in docker compose logs).

SELECT toUInt64(round(uniq(e0."user_id") * any(_sample_factor))) AS "visitors",e0."pathname" AS "dim0" FROM "events_v2" AS e0 WHERE (((e0."site_id" = 2) AND (e0."timestamp" >= '2024-10-23 22:00:00')) AND (e0."timestamp" <= '2024-10-24 21:59:59')) GROUP BY "dim0" ORDER BY "visitors" DESC,"dim0" LIMIT 9 OFFSET 0

The problem is that the website time zone set as +2 Prague.

When I looked at the events_2 table, the timestamp is set to 2024-10-24 23:09:42 which is correct in Prague timezone. But the today query asks database for all timestamps until 21:59:59 which is correct when you save your timestamps as UTC. But apparently, all timestamps are saves as Prague timezeone`. So either events_2 timezone is invalid when creating a new event, or the query - depending how you see it. Personally, I would suggest to save everything in UTC, and just modify the query accordingly.

Expected behavior

I would expect to show all today's events.

Screenshots

No response

Environment

- OS:
- Browser:
- Browser Version:

[ruslandoga]

👋 @martinnovak

Personally, I would suggest to save everything in UTC, and just modify the query accordingly.

Event and session timestamps in ClickHouse are already stored as unix timestamps:

• event.timestamp gets current UTC datetime here:

  analytics/lib/plausible/ingestion/request.ex

  Line 60 in 0404522

  def build(%Plug.Conn{} = conn, now \\ NaiveDateTime.utc_now()) do

• it gets encoded to a unix timestamp in RowBinary here: https://github.com/plausible/ch/blob/ca21639a46d9b2c6a42a37eb2bf600eeaeea3e05/lib/ch/row_binary.ex#L302-L304
• and it gets stored as a unix timestamp too: https://clickhouse.com/docs/en/sql-reference/data-types/datetime#usage-remarks

[ruslandoga]

There might be some configuration issue with your instance ... Could you please provide more information on your setup? And the query you ran to find the 2024-10-24 23:09:42 event. Note that ClickHouse might render DateTime as text in the server timezone, but the underlying data written on disk is still a timezone-agnostic unix timestamp: https://clickhouse.com/docs/en/sql-reference/data-types/datetime#usage-remarks