PostgreSQL Получить случайное значение между двумя временными метками с ограничением часов

Question

Использование: PostgreSQL 10.5

Этот вопрос чем-то похож на:

PostgreSQL Получить случайную дату / время между двумя датами / временем

Ответ, данный @pozs, решает проблему, но не позволяет мне ограничивать часы в случайной возвращенной метке времени. Я бы сказал, что это расширение этой проблемы.

задача

Разница в том, что мне нужно получить случайную временную метку между двумя временными метками, но час в выходном значении должен быть между 10:00:00 а также 18:00:00,

Моя попытка

Я пытался сделать это время эффективно, но сейчас пришла только идея сохранить различные части: дату, время и миллисекунды, а затем объединить их с 3 выборами, используя ORDER BY random() LIMIT 1, Это, однако, далеко не быстрое решение.

tmp_data держит даты, tmp_time держит время и tmp_ms содержит миллисекунды, которые я складываю, используя функцию для получения правильного вывода:

  (SELECT data FROM tmp_data ORDER BY random() LIMIT 1) 
+ (SELECT czas FROM tmp_time WHERE czas BETWEEN '10:00:00' AND '18:00:00' ORDER BY random() LIMIT 1) 
+ (SELECT ms FROM tmp_ms ORDER BY random() LIMIT 1)

Эта работа сделана, но требует некоторого времени из-за 3 выбора для предварительно вычисленных таблиц с сортировкой (и это нужно будет вычислять для каждой строки).

Пример данных / Объяснение

Учитывая временные ограничения:

• start_timestamp => 2016-01-01 10:00:00
• end_timestamp => 2017-12-31 18:00:00

Давайте сгенерируем случайную временную метку с точки зрения каждой части, кроме часа (час должен быть между 10 и 18).

Пример вывода - генерируется случайным образом

 2016-09-12 11:54:59.4919
 2016-01-10 10:39:03.626985
 2016-01-03 15:58:19.599016
 2016-04-11 10:05:07.527829
 2016-07-04 12:57:33.125333
 2017-12-15 14:17:46.975731
 2016-10-04 16:55:01.701048
 2016-09-26 13:36:59.71145
 2017-09-06 17:25:09.426963
 2016-09-08 17:08:00.917743

Каждый час здесь находится между 10 и 18, но каждая другая часть временной метки является случайной.

Answer 1

Преобразование вывода из этого ответа в date введите, затем добавив 10:00:00 время (ограничение нижнего часа) и random Интервал до 8 часов (ограничение верхнего часа) делает это довольно быстро:

select 
  date (timestamp '2016-01-01' + 
        random() * (timestamp '2017-12-31' - timestamp '2016-01-01'))
 + time '10:00:00' 
 + random() * INTERVAL '8 hours';

Answer 2

Если вы конвертируете метки времени в секунды с EXTRACT(epoch FROM <YOUR TIMESTAMP>) вы можете сделать это:

1. random() дает значение от 0 до 1
2. если вы умножите случайное значение на разницу временных отметок, вы получите возможный диапазон (для 10–18 часов диапазон будет от 0 до 8 часов)
3. добавление начальной точки в диапазон сдвигает вычисленное значение до значения между начальной и конечной точкой. Теперь у вас есть ожидаемая случайная отметка времени в секундах
4. преобразовать секунды обратно в метку времени Postgres с помощью to_timestamp
5. сделать это 2 раза: один раз, чтобы получить случайную дату, и один раз, чтобы получить случайное время.
6. добавьте обе метки времени (одна приведена в дату, другая во время).

Запрос

WITH timestamps AS (

    SELECT 
        EXTRACT(epoch FROM start::time) start_time,
        EXTRACT(epoch FROM "end"::time) end_time,
        EXTRACT(epoch FROM start::date) start_date,
        EXTRACT(epoch FROM "end"::date) end_date
    FROM (
        SELECT
            '2016-01-01 10:00:00'::timestamp as start,
            '2017-12-31 18:00:00'::timestamp as end
    ) s
)

SELECT 
    to_timestamp(
        random() * (ts.end_date - ts.start_date) + ts.start_date 
    )::date + 
    to_timestamp(
        random() * (ts.end_time - ts.start_time) + ts.start_time 
    )::time 
FROM timestamps ts

демо: дб<> скрипка