Сравнение отметок времени в функции

Question

Чтобы старая версия не перезаписывала новую, в этой простой функции:

      create function myupdate(paramts timestamp without time zone, ...)
  language plpgsql AS
$$
begin
-- step 1 compare current record timestamp vs. supplied timestamp
if exists (select 1 from record where ts <> paramts and ...) then
    raise exception 'A newer version exists, cannot update.';
end if;
...
end
$$;

определение то же самое timestamp without time zone.

paramts значение предоставляется функцией:

      create function myfetch(...)
  language plpgsql AS
$$
begin
    return query select ts, ... from record where ...;
end
$$;

Пользовательский интерфейс клиента получает 2021-04-16T21: 37: 35.878Z, как и значение, отправленное в. Однако на одном из наших серверов Западного побережья во время выполнения внутри myupdate(), tsавтоматически преобразуется в PST 2021-04-16 14:37:35.878694 и имеет 3 дополнительные цифры справа.

Как сравнить как в формате UTC, так и с одинаковой точностью?

Answer 1

Вы должны использовать timestamptz ( timestamp with time zone) вместо timestamp ( timestamp without time zone), чтобы избежать путаницы с часовыми поясами. В таблице, в функции, во всей пищевой цепочке. Значения всегда хранятся внутри как время UTC, и сравнения работают правильно автоматически (сравнение моментов времени независимо от часовых поясов).

Видеть:

• Полное игнорирование часовых поясов в Rails и PostgreSQL

В любом случае оба типа имеют разрешение микросекунды , то есть 6 дробных десятичных цифр. Ваш первый пример был каким-то образом усечен, возможно, вашим клиентом на дисплее.

Answer 2

Вот полный ответ:

1. Use = Timestamp часовой пояс для определения столбца.
2. tz всегда снабжает клиентов значениями UTC, например, я использую node / pg-prom, интерфейс - Angular / TS, все они получают значение вроде 2021-04-17T22:42:57.610Z.
3. UTC выходит, UTC отправляется обратно (при условии, что вы не трогаете временную метку), достаточно ли это хорошо? Неа. Потому что внутри функции plpgsql получает 2021-04-17 15:42:57.610516-07, уже автоматически преобразованное в локальное значение, то же самое в pgAdmin и SQL Shell, это ключ!
4. Решение состоит в том, чтобы преобразовать их (кроме отправленных параметров) обратно в UTC, а затем сравнить

if (select (tableTimestamp at time zone 'UTC')::timestamp(2) = parameter::timestamp(2) from mytable where ...) then Update mytable ...; else raise exception ...; end if;

Почему timestamp(2)? В то время как postgresql поддерживает 6-значные десятичные секунды, JavaScript поддерживает только 3-значные числа, и он обрезается без округления.

PS, with а также without time zone сбивает с толку.