Разделение временной метки на две строки в postgresql

Question

Разделение временной метки на две строки в postgresql

У меня есть две метки времени в таблице:

     usage_from      |     usage_till     
---------------------+--------------------
 2013-10-09 23:08:17 | 2013-10-09 23:16:00
 2013-10-09 23:08:17 | 2013-10-09 23:08:19
 2013-10-09 23:08:17 | 2013-10-10 18:58:22
 2013-10-09 23:08:17 | 2013-10-09 23:15:05
 2013-10-09 23:08:17 | 2013-10-09 23:09:00
 2013-10-09 23:08:17 | 2013-10-09 23:08:20
 2013-10-09 23:08:17 | 2013-10-09 23:32:04
 2013-10-09 23:08:17 | 2013-10-10 02:02:03
 2013-10-09 23:08:17 | 2013-10-10 07:31:00
 2013-10-09 23:08:17 | 2013-10-10 22:41:04

Это мне нужно разделить, как показано ниже:

 usage_from      |     usage_till        
---------------------+-----------------------
 2013-10-09 23:08:17 |  2013-10-09 23:16:00 
 2013-10-09 23:08:17 |  2013-10-09 23:08:19 
 2013-10-09 23:08:17 |  2013-10-10 02:00:00 
 2013-10-10 02:00:00 |  2013-10-10 18:58:22 -- splitted
 2013-10-09 23:08:17 |  2013-10-09 23:15:05 
 2013-10-09 23:08:17 |  2013-10-09 23:09:00 
 2013-10-09 23:08:17 |  2013-10-09 23:08:20 
 2013-10-09 23:08:17 |  2013-10-09 23:32:04 
 2013-10-09 23:08:17 |  2013-10-10 02:00:00 
 2013-10-10 02:00:00 |  2013-10-10 02:02:03 -- splitted
 2013-10-09 23:08:17 |  2013-10-10 02:00:00 
 2013-10-10 02:00:00 |  2013-10-10 07:31:00 -- splitted
 2013-10-09 23:08:17 |  2013-10-10 02:00:00 
 2013-10-10 02:00:00 |  2013-10-10 22:41:04 -- splitted

Здесь, в приведенном выше примере, я разделил отметку времени в 02:00:00.

После многих испытаний я мог разделить его, как показано ниже, но не смог разделить как другой ряд.

     usage_from      |     usage_till      |     end_time_1      |     end_time_2      
---------------------+---------------------+---------------------+---------------------
 2013-10-09 23:08:17 | 2013-10-09 23:16:00 | 2013-10-09 23:16:00 | 2013-10-11 02:00:00 
 2013-10-09 23:08:17 | 2013-10-09 23:08:19 | 2013-10-09 23:08:19 | 2013-10-11 02:00:00 
 2013-10-09 23:08:17 | 2013-10-10 18:58:22 | 2013-10-10 02:00:00 | 2013-10-10 18:58:22 
 2013-10-09 23:08:17 | 2013-10-09 23:15:05 | 2013-10-09 23:15:05 | 2013-10-11 02:00:00 
 2013-10-09 23:08:17 | 2013-10-09 23:09:00 | 2013-10-09 23:09:00 | 2013-10-11 02:00:00 
 2013-10-09 23:08:17 | 2013-10-09 23:08:20 | 2013-10-09 23:08:20 | 2013-10-11 02:00:00 
 2013-10-09 23:08:17 | 2013-10-09 23:32:04 | 2013-10-09 23:32:04 | 2013-10-11 02:00:00 
 2013-10-09 23:08:17 | 2013-10-10 02:02:03 | 2013-10-10 02:00:00 | 2013-10-10 02:02:03 
 2013-10-09 23:08:17 | 2013-10-10 07:31:00 | 2013-10-10 02:00:00 | 2013-10-10 07:31:00 
 2013-10-09 23:08:17 | 2013-10-10 22:41:04 | 2013-10-10 02:00:00 | 2013-10-10 22:41:04

есть идеи как это сделать? Я боролся в течение последних нескольких дней.
Я использую Redshift 1.0.757 (на основе PostgreSQL 8.02).

-1

sql postgresql datetime amazon-redshift generate-series

Источник

user1939568 25 мар '14 в 09:36

1 ответ

Решение

Другие вопросы по тегам sql postgresql datetime amazon-redshift generate-series

user939860 25 мар '14 в 13:57 2014-03-25 13:57 · Accepted Answer · 2014-03-25 13:57

Если ¹ Redshift будет поддерживать базовую форму generate_series(), это может сработать. По крайней мере, это работает в Postgres 8.3:

SELECT CASE WHEN split > 0 AND g = 0 THEN usage_from
            WHEN split > 0 AND g = 1 THEN usage_till::date + '2:0'::time
            ELSE usage_from END
     , CASE WHEN split > 0 AND g = 0 THEN usage_till::date + '2:0'::time
            WHEN split > 0 AND g = 1 THEN usage_till
            ELSE usage_till END
FROM  (
   SELECT * , generate_series(0, split) AS g
   FROM  (
      SELECT *
            , (usage_till - '2:0'::time)::date
            - (usage_from - '2:0'::time)::date AS split  -- results in integer
      FROM   t
      ) sub1
   ) sub2

Как?

Во внутреннем подзапросе sub1 Я нахожу, пересекает ли временной интервал 2 часа ночи, и сохраняю это в столбце split, Я предполагаю, что временной интервал никогда не пересекает два часа ночи дважды, но запрос можно легко адаптировать к этому. generate_series() автоматически генерирует 1 строку за обтекание.
В следующем подзапросе sub2generate_series() генерирует две строки, где требуется разделение.
Во внешнем операторе SELECT оператор CASE соответствующим образом корректирует временные метки.
Обычно я бы использовал interval '2 hours' вместо '2:0'::time, но я помню, что Redshift не поддерживает interval тип.

SQL Fiddle для Postgres 8.3.

Не в Redshift?

Если Redshift позволяет только generate_series() в FROM список, а не в SELECT список, вам не повезло. Это уже древняя форма. В современном Postgres вы бы использовали LATERAL JOIN, Вы можете попытать счастья с помощью regexp_split_to_table (), но этого также нет в Postgres 8.0.

¹ Но в руководстве сказано: generate_series() не поддерживается.

За исключением этого, я могу думать только о процедурном решении с PL/pgSQL. Но Redshift тоже может быть ограничен...