Как скорректировать время в результате select?

Question

[Tibor128]

В таблице есть столбец с данными типа timestamp.
Время там хранится UTC
Необходимо его скорректировать до UTC+3 именно в результате запроса.
Я понимаю, что это легко сделать, перечислив в запросе имена столбцов, например так:

SELECT id, name, timestamp + interval '3 hour' as timestamp FROM tablename

Но если столбцов много то не красиво перечислять их все ради того, чтобы скорректировать один.
Как это сделать в запросе вида:

SELECT * FROM tablename

Comments

[Tibor128]

Коллеги, должен уточнить.
set timezone='Europe/Moscow' - перед запросом не даёт результата, потому как на сервере уже установлена эта зона.
AT TIME ZONE - соответственно тоже.

Время в эти поля прилетает от gps-трекеров. Конечно же можно, скорректировать время ещё до записи в БД, это не проблема.
Вопрос скорее теоретический чем практический. Спортивный интерес =)

Answer 1

[Melkij]

В таблице есть столбец с данными типа timestamp.
Время там хранится UTC

Мигрируете на timestamp with time zone
Затем проставляете желаемый set timezone to '...'; и получаете всю обработку для timestamp with time zone корректную в этом часовом поясе.

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

Красиво - это перечислить явно и только те поля, которые в результате вам нужны.
Если в этом дурацком засилье orm вы пишете запрос руками - значит вы явно должны знать, что именно и зачем вы хотите прочитать. Следовательно, select * показывает явный антипаттерн.

Comments

[Tibor128]

Здесь не зря хранится UTC. Потому как речь о gps. Трекер сегодня в одном часовом поясе, а завтра в другом.
Все поля необходимы в моём случае, по этому как мне кажется нет смысла перечислять их все в то время когда можно сделать "select *".

[Melkij]

И как это противоречит моему ответу?

[Tibor128]

Melkij, прошу прощения, возможно моя некомпетентность не позволяет мне адекватно оценить Ваш ответ, но предложенное Вами решение не совсем то, что мне нужно. Вероятно из-за не правильно поставленного вопроса.
Префразирую...
Как в составить запрос вида "select *" так, чтобы в результате его, значения какого-либо столбца были скорректированны в соответствиями с определёнными условиями без добавления дополнитеьного столбца. Т.е. если в таблице есть (например) столбец с именем "dt" то и в результате должен быть столбец с таким же именем и в том же порядке что и в исходной таблице, но с скорректированными по определённым условиям значениями (например инкремент, в случае с числовым типом данных или с "lower(something column)" в случае со строкой)?

[Melkij]

Никак.

Answer 2

[Vitaliy Orlov]

Попробуй поменять часовой пояс перед SELECT * FROM запросом, примерно так
SET timezone='America/Los_Angeles';

Ну, и +interval не надо делать, для этого есть AT TIME ZONE

Comments

[Tibor128]

set timezone='Europe/Moscow' - перед запросом не даёт результата, потому как на сервере уже установлена эта зона.
AT TIME ZONE - соответственно тоже, с той лишь разницей, что время дополняется значением "+3", а хотелось бы на выходе получить "YYYY-mm-dd HH:MM:SS"

Уточню. Данные прилетают с gps-трекеров. Прилетают сразу и много. В том числе и устаревшие (история).
Очень хочется хранить в БД сырые данные. Т.е. если трекер присылает UTC то пусть оно так и хранится, но на выходе - входе в управляющую программу (в результате SELECTа), хочу получить UTC+3

[Vitaliy Orlov]

Вот я тут поигрался: https://rextester.com/CVGMO29671, смотреть лучше по ссылке, но код так же прилагаю ниже.

"SET timezone" - не поможет, надо или в SELECT-е конвертировать, что от твоего решения не особо отличается или функцию писать. Моё мнение, лучше оставить конвертацию в SELECT-ах, если только пишешь не для своих нужд..

--PostgreSQL 9.6
--'\\' is a delimiter


CREATE TABLE tbl100500 (
 id serial PRIMARY KEY,
 ts TIMESTAMP
);

SET timezone TO 'UTC';
SHOW timezone;
INSERT INTO tbl100500 (id, ts) VALUES (1, NOW());
SELECT * FROM tbl100500;

SET timezone TO 'UTC+1';
SHOW timezone;
SELECT * FROM tbl100500;

SELECT id, ts AT time zone 'UTC+2' FROM tbl100500;
SELECT id, timezone('UTC+3', ts) FROM tbl100500;

CREATE OR REPLACE FUNCTION timezoned_ts()
RETURNS TABLE (_id INT, _ts TIMESTAMP) LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY SELECT id::int, timezone('UTC+8', ts)::timestamp FROM tbl100500;
END $$;

SELECT * FROM timezoned_ts()

[Tibor128]

Vitaliy Orlov, Спасибо за Ваше внимание к моему вопросу. Выше я в соседнем ответе, остваил комментарий, который уточняет мой вопрос)
Прошу прощения если я ввёл Вас в заблуждение, чем потратил Ваше время зря)
Надеюсь, что теперь Вы сможете подсказать мне ответ на вопрос)

[Vitaliy Orlov]

Tibor128,

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

Максимально близкое решение, это определить функцию, которая будет скрывать реализацию обработки, пример указан выше.