This is a feature request for a function similar to current_date(), but that is timezone-aware. I need this to run queries from different timezones where I count events that occured “today”, but where the definition of “today” is relative to the timezone where the client is located.
Here’s my problem: I’m located in the America/Toronto timezone (-5 hours from UTC). I query for all events that occurred today, which is done by filtering records to those >= the timestamp representing the beginning of the current date:
SELECT time, event
FROM meas
WHERE "time" >= current_date()
To adjust for timezone, I’ve been using the following modification to the above query:
WHERE "time" >= current_date()::TIMESTAMP AT TIME ZONE 'America/Toronto'
This works fine if I execute that query during the day; it resolves that timestamp threshold correctly to 2025-02-13T05:00:00.000Z.
The problem is what happens if I execute my query at 19:01 local time. Over on the Influx servers where the query executes, it’s already “tomorrow” from my perspective (and from the perspective of my locally-produced event data), so when current_date() executes, it resolves to 2025-02-14T00:00:00.000Z. When the AT TIME ZONE 'America/Toronto' factors in, it merely adds the offset to this semantically-incorrect “tomorrow” date, becoming 2025-02-14T05:00:00.000Z.
The end result is that if our customer is working late and wants to view event counts, they will notice at around 7pm that today’s counts go to zero and shift over to yesterday’s.
Please provide a solution for this.
Thanks
