将日期拆分为一刻钟

我有一个要求，在那里，我需要转换两个记录，将有一个完整的一天（24小时周期）的时间跨度。

Eg : Record 1 - Time from 3:00 Am to 12:59:59 PM
     Record 2 - Time from 00:00 AM to 2:59:59 AM

表结构是

start_time         | end_time            |local_start_time     |local_endtime |
 2015-01-01 3:00:00 | 2015-01-01 23:59:59 |  2015-01-01 4:00:00 | 2015-02-01 01:59:59
 2015-02-01 12:00:00| 2015-02-01 2:59:59  | 2015-02-01 13:00:00 |2015-02-01 3:59:59

从这两个记录中，我需要在24小时内创建96个记录。每一张唱片都应该是一刻钟的一张唱片。

Eg:
 start_time         |     end_time      |local_start_time   |local_endtime    | Quarter_hour
 2015-01-01 3:00:00 | 2015-01-01 3:14:59|2015-01-01 4:00:00 | 2015-01-01 4:14:59 | 1
 2015-01-01 3:15:00 | 2015-01-01 3:29:59|2015-01-01 4:15:00 | 2015-01-01 4:29:59 | 2

以此类推，直到每1天一刻钟变成96。
我不知道如何实施这一点。任何帮助都是非常有用的。谢谢

诚恳地说，我从来没有和 Impala 一起工作过，但是如果我只想用sql语句。。我的方法可以是：
我需要每小时25美分：

select '00:00' qstart, '14:59' qend union
select '15:00' qstart, '29:59' qend union
select '30:00' qstart, '44:59' qend union
select '45:00' qstart, '59:59' qend

我需要几个小时(除了oralce connect条款，您还有其他选择吗？）：

select '00' hhour, 1 ordinal union
select '01' hhour, 2 ordinal union
select '02' hhour, 3 ordinal union
select '03' hhour, 4 ordinal union
select '04' hhour, 5 ordinal union
select '05' hhour, 6 ordinal union
select '06' hhour, 7 ordinal union
select '07' hhour, 8 ordinal union
select '08' hhour, 9 ordinal union
select '09' hhour, 10 ordinal union
select '10' hhour, 11 ordinal union
select '11' hhour, 12 ordinal union
select '12' hhour, 13 ordinal union
select '13' hhour, 14 ordinal union
select '14' hhour, 15 ordinal union
select '15' hhour, 16 ordinal union
select '16' hhour, 17 ordinal union
select '17' hhour, 18 ordinal union
select '18' hhour, 19 ordinal union
select '19' hhour, 20 ordinal union
select '20' hhour, 21 ordinal union
select '21' hhour, 22 ordinal union
select '22' hhour, 23 ordinal union
select '23' hhour, 24 ordinal

如果你用这两句话连接起来。。。

select hhour || ':' || qstart as hqstart, hhour || ':' || qend as hqend, ordinal
from
(select '00' hhour, 1 ordinal union
select '01' hhour, 2 ordinal union
select '02' hhour, 3 ordinal union
select '03' hhour, 4 ordinal union
select '04' hhour, 5 ordinal union
select '05' hhour, 6 ordinal union
select '06' hhour, 7 ordinal union
select '07' hhour, 8 ordinal union
select '08' hhour, 9 ordinal union
select '09' hhour, 10 ordinal union
select '10' hhour, 11 ordinal union
select '11' hhour, 12 ordinal union
select '12' hhour, 13 ordinal union
select '13' hhour, 14 ordinal union
select '14' hhour, 15 ordinal union
select '15' hhour, 16 ordinal union
select '16' hhour, 17 ordinal union
select '17' hhour, 18 ordinal union
select '18' hhour, 19 ordinal union
select '19' hhour, 20 ordinal union
select '20' hhour, 21 ordinal union
select '21' hhour, 22 ordinal union
select '22' hhour, 23 ordinal union
select '23' hhour, 24 ordinal) Hours,
(select '00:00' qstart, '14:59' qend union
select '15:00' qstart, '29:59' qend union
select '30:00' qstart, '44:59' qend union
select '45:00' qstart, '59:59' qend ) Quarters order by ordinal, qstart

你有96张你想要的唱片！

hqstart     hqend       ordinal
00:00:00    00:14:59    1
00:15:00    00:29:59    1
00:30:00    00:44:59    1
00:45:00    00:59:59    1
01:00:00    01:14:59    2
01:15:00    01:29:59    2
01:30:00    01:44:59    2
01:45:00    01:59:59    2
...         ...         .

如果你想在两次之间选择四分之一。。。比如说。。14:09:00至15:06:30的宿舍：

select * from 
(
select hhour || ':' || qstart as hqstart, hhour || ':' || qend as hqend, ordinal
from
   (select '00' hhour, 1 ordinal union
    select '01' hhour, 2 ordinal union
    select '02' hhour, 3 ordinal union
    select '03' hhour, 4 ordinal union
    select '04' hhour, 5 ordinal union
    select '05' hhour, 6 ordinal union
    select '06' hhour, 7 ordinal union
    select '07' hhour, 8 ordinal union
    select '08' hhour, 9 ordinal union
    select '09' hhour, 10 ordinal union
    select '10' hhour, 11 ordinal union
    select '11' hhour, 12 ordinal union
    select '12' hhour, 13 ordinal union
    select '13' hhour, 14 ordinal union
    select '14' hhour, 15 ordinal union
    select '15' hhour, 16 ordinal union
    select '16' hhour, 17 ordinal union
    select '17' hhour, 18 ordinal union
    select '18' hhour, 19 ordinal union
    select '19' hhour, 20 ordinal union
    select '20' hhour, 21 ordinal union
    select '21' hhour, 22 ordinal union
    select '22' hhour, 23 ordinal union
    select '23' hhour, 24 ordinal) Hours,
   (select '00:00' qstart, '14:59' qend union
    select '15:00' qstart, '29:59' qend union
    select '30:00' qstart, '44:59' qend union
    select '45:00' qstart, '59:59' qend ) Quarters order by ordinal
) AllQuarters
where (hqstart >= '14:09:00' and hqstart < '15:06:30') or
      (hqend  >=  '14:09:00' and hqend   < '15:06:30')
order by ordinal, hqstart, hqend;

输出：

hqstart     hqend       ordinal
14:00:00    14:14:59    15
14:15:00    14:29:59    15
14:30:00    14:44:59    15
14:45:00    14:59:59    15
15:00:00    15:14:59    16

您可以在这里找到一个SQLFiddle示例
小心 from dual 子句中，它仅适用于oracle dbms。

将日期拆分为一刻钟

1条答案

相关问题

热门标签

最新问答