时间戳

首页 > 解决方案 > 使用过滤器从范围内生成日期和时间

sql - 使用过滤器从范围内生成日期和时间

问题描述

源表如下：

ID   Date_Start   Date_End     DayOfWeek   Time_Start  Time_End   field1
1    01/02/2018   15/02/2018   4           08:00:00    10:00:00   text1
2    01/06/2018   15/06/2018   2           10:00:00    13:00:00   text1

第 4 天代表星期四，所以我只想生成 DayOfWeek 的日期和时间（源表中的 (4)Thursday 和 (2)Tuesday）。

我想得到这个输出：

Date        hour     field1
01/02/2018  08       text1
01/02/2018  09       text1
01/02/2018  10       text1
08/02/2018  08       text1
08/02/2018  09       text1
08/02/2018  10       text1
15/02/2018  08       text1
15/02/2018  09       text1
15/02/2018  10       text1
05/06/2018  10       text1
05/06/2018  11       text1
05/06/2018  12       text1
05/06/2018  13       text1
12/06/2018  10       text1
12/06/2018  11       text1
12/06/2018  12       text1
12/06/2018  13       text1

我试图编辑这个好的答案，使用 *24 来获得时间但没办法！

谢谢你。

标签： sqloracleoracle11g

解决方案

这是一个示例，说明如何执行链接文章答案显示的内容。请注意，Oracle 没有time类型，只有datetimes，所以我不确定您是如何存储时间的。出于此答案的目的，我只是将它们附加到开始/结束日期。

WITH test_data AS (
    SELECT 1 AS ID, 
           to_date('01/02/2018','dd/mm/yyyy') AS date_start,
           to_date('15/02/2018','dd/mm/yyyy') AS date_end,
           4 AS dayOfWeek,
           to_date('08:00:00','hh24:mi:ss') AS time_start,
           to_date('10:00:00','hh24:mi:ss') AS time_end,
           'text1' AS field1
    FROM dual

    UNION

    SELECT 2 AS ID, 
           to_date('01/06/2018','dd/mm/yyyy') AS date_start,
           to_date('15/06/2018','dd/mm/yyyy') AS date_end,
           2 AS dayOfWeek,
           to_date('10:00:00','hh24:mi:ss') AS time_start,
           to_date('13:00:00','hh24:mi:ss') AS time_end,
           'text1' AS field1
    FROM dual
), all_hours_in_range AS (
    SELECT d.id, 
           LEVEL, 
           d.date_start, 
           d.date_end, 
           d.date_start + (LEVEL - 1) / 24 AS new_date, 
           d.dayOfWeek, 
           d.time_start,
           d.time_end,
           d.field1
    FROM test_data d
    CONNECT BY LEVEL <= (date_end - date_start) * 24 + 1
      AND PRIOR ID = ID
      AND PRIOR sys_guid() IS NOT NULL
)

SELECT to_char(a.new_date, 'dd/mm/yyyy') AS new_date, 
       to_char(a.new_date, 'hh24') AS new_hour, 
       a.field1
FROM all_hours_in_range a
WHERE to_char(a.new_date, 'hh24') >= to_char(a.time_start, 'hh24') /* Filter to results that are within specified hours */
  AND to_char(a.new_date, 'hh24') <= to_char(a.time_end, 'hh24') 
  AND 1 + TRUNC (a.new_date) - TRUNC (a.new_date, 'IW') = a.dayOfWeek /* Filter to results that are on specified day of week */

推荐阅读