сходу вспомнилось два кейса:
- високосная секунда
- переход региона из одного часового пояса в другой (на днях обсуждали такой пример как раз)

третий может быть?

Вы вызов clock_timestamp() видите внутри? Многократный, между прочим!
Представляете, что будет, если применить эту кашу ко многим записям, или на границе местных суток? ;)

В unixtime нет високосных секунд, так что на это можно забить (точнее, их всё равно никак нельзя учесть из-за отсутствия поддержки).
Учитывая, что в postgres лежыт типичный unix timestamp — в постгресе на это тожэ можно забить.

вспомнилась моя любимая штука

22:56:35 [gis] > select clock_timestamp()-current_timestamp;
┌─────────────────┐
│    ?column?     │
├─────────────────┤
│ 00:00:00.000192 │
└─────────────────┘

Вам 5 лет просто везло... или, как это обычно бывает, ошибок никто не замечал (а отчёты подавались / решения принимались... ну или что там от этого зависело). ;(
Корректно my eye.

Черт его знает. Кажется, что можно ведь получить текущую дату как-то так: (current_timestamp at time zone 'Europe/Moscow')::date. Должно быть не хуже. Но хочется ещё и чтобы быстрей :)

Мы тут все не то чтоб большие спецы по постгресу, а выкручиваться приходится

Послушайте... это код тупо ошибочен, так что "плясать" вокруг него смысла никакого нет, если корректность результатов кого-то интересует.

Текущую дату можно получить куда проще, но суть не в этом.

Вот возвращаясь к:

> DateOnly(targetdate) = AddDays(GetDate(), 15)

Откуда при раскрытии этого вообще взялась time zone?

Буду признателен за пример как получить дату ;)

Таймзону учётки знает клиент и может подставить куда надо. Клиент, если что, - толстое виндовое приложение. Он из юзеринпута сам генерирует sql.

Если нормально работать с датой/временем (так, как задумано в PostgreSQL), то это всего лишь:

SELECT current_date;

> Таймзону учётки знает клиент и может подставить куда надо.

И это уже — не так, как задумано. Т.е. в норме клиент делает SET timezone = 'Europe/Moscow'; в начале сессии, и работает с использованием обычных функций, а не "борется" против сервера, подставляя куда-то там свою таймзону.

Так это не клиент СУБД. Давайте сначала, может быть, отчасти я сам вас запутал.

Юзер работает в толстом клиенте и вводит запросы на некоем псевдо-диалекте. То есть юзер вводит DateOnly(targetdate). Толстый клиент присылает на сервер xml с такими "запросами" внутри, транслирует их в sql и выполняет уже клиентом СУБД. Функция DateOnly() создана в СУБД с двумя аргументами (timestamptz, timezone). Транслятор знает от какой учётки пришел документ и в какой тайм зоне учетка "живёт". Клиент СУБД на всех юзеров один и работает в utc сам по себе.

targetdate в данных отражает событие, регистрируемое с точностью до секунд. Но юзеру нужна точность до дней, то есть его всегда интересуют события в пределах календарной даты.

Поскольку есть юзеры в штатах и в Китае/Австралии, и они работают зачастую с одними и теми же данными, отказаться от timestamptz в пользу date на влете к нам в базу мы не можем. Важно, чтобы календарная дата юзеру отдавалась в его поясе

> транслирует их в sql и выполняет уже клиентом СУБД.

И что ему мешает на этом уровне "транслировать" нормально (так, как я написал выше)?

> Транслятор знает от какой учётки пришел документ и в какой тайм зоне учетка "живёт".

Вы сейчас описываете существующее неправильное решение, насколько я вижу.

> Клиент СУБД на всех юзеров один и работает в utc сам по себе.

И это тоже неправильно, если нет дополнительных существенных ограничений (например, если он "сваливает в кучу" запросы от разных своих клиентов, и отправляет их в одну сессию (connection) PostgreSQL, то такое может ещё иметь какой-то смысл).

> Поскольку есть юзеры в штатах и в Китае/Австралии

Это Вам не повезло — там у них (а особенно "вокруг" них) есть time zones с совершенно долбанутыми DST rules, особенно в прошлом. :(

> отказаться от timestamptz в пользу date на влете к нам в базу мы не можем.

И ни в коем случае не нужно — эта часть у Вас правильная, по крайней мере. ;)

> Важно, чтобы календарная дата юзеру отдавалась в его поясе

Так, всё же, есть какие-то непреодолимые препятствия для того, чтобы нормально работать?

И после  set time zone функцыя перестанет быть immutable.

Вы о чём-то совсем не о том, мне кажется.

Коннекшен один. А все остальные неправильные решения, к сожалению, - потому что это SaaS решение и поменять его мы не можем. Мы можем поменять структуру данных и индексов (тоже с некоторыми оговорками), написать свои "псевдо-функции", которые будут транслироваться в свои функции sql

Я о примере. Сейчас его функцыя DateOnly(timestamptz, varchar) по факту immutable, что позволяет наклепать на неё индэксов как-никак.  После того, как она начнёт брать таймзону из переменной — она перестанет быть immutable.

Печально.
В таком случае, если условия выглядят именно как DateOnly(targetdate) = AddDays(GetDate(), 15) ... подождите, а Вы, вроде, писали

> Функция DateOnly() создана в СУБД с двумя аргументами (timestamptz, timezone).

Так как на самом деле выглядят условия в получающихся SQL-запросах, какие функции используются?

Он в первом сообщении нормально написал, потом ужэ сокращал.

Потерялось по дороге. Вот:

DateOnly(L1.targetdate, 'Europe/Moscow') = AddDays(DateOnly(GetDate(), 'Europe/Moscow'), 15)

Это прямо то, что отправляется в базу.