时间戳

首页 > 解决方案 > Oracle - 将时间戳与日期结合并删除重复项

sql - Oracle - 将时间戳与日期结合并删除重复项

问题描述

我有一个 Oracle DB，它在几列中为我提供了时间戳和日期。

“JDDate”为日期，“END”为结束时间，“START”为开始时间。

因此，日期以 CYYDDD 格式输出，时间以 HHMMSS 格式输出。通过以下说明，我获得了必要的格式：

SELECT
    DATE '1900-01-01' + FLOOR("JDDate" / 1000) * INTERVAL'1' YEAR+ (MOD("JDDate", 1000) -1) * INTERVAL'1' DAY AS "Date",
    LTRIM((SUBSTR("NR", -6)), '0') AS "Staff Nr",
    CASE WHEN "END" =240000 THEN '23:59:59' ELSE REPLACE(TO_CHAR("END", '00,00,00'),',',':') END AS "tEnd",
    REPLACE(TO_CHAR("START", '00,00,00'),',',':') AS "tStart",

FROM "POOLXX1"."CCD021"
WHERE   "JDDate" >118000

现在我想以某种方式获得差异。也许您必须将日期与时间戳结合起来并相应地计算差异。有一个条件。如果为一天中的每个人（“NR”列）分配相同的开始和结束标记，则这些标记是重复的。相应地，这些应该被忽略。也许以前使用它们是有意义的。

你需要更多的信息？

最好的祝福！约书亚

标签： sqloracle

解决方案

您可以将数字（显然）开始/结束时间表示转换为天数或间隔，并将它们添加到转换后的日期。假设您实际上希望 240000 是第二天的午夜，而不是在那之前的一秒，如果您正在计算的话。因此，例如，您可以这样做：

select
    date '1900-01-01'
      + floor("JDDate" / 1000) * interval '1' year
      + (mod("JDDate", 1000) -1) * interval '1' day
      + floor("START" / 10000) * interval '1' hour
      + floor(mod("START", 10000) / 100) * interval '1' minute
      + mod("START", 100) * interval '1' second
      as start_datetime,
    date '1900-01-01'
      + floor("JDDate" / 1000) * interval '1' year
      + (mod("JDDate", 1000) -1) * interval '1' day
      + case when "END" = 240000 then interval '1' day
             else floor("END" / 10000) * interval '1' hour
                + floor(mod("END", 10000) / 100) * interval '1' minute
                + mod("END", 100) * interval '1' second
        end
      as end_datetime
from ccd021
where "JDDate" > 118000;

计算基本相同，但END保留 case 表达式 - 它只是为 240000 添加一整天而不是调整时间。

如果您有想要抑制的重复项（并且由于某种原因无法从基础数据中删除），那么您可以使用distinct.

select distinct ltrim(substr(nr, -6), '0') as staff_nr,
    date '1900-01-01'
    + ...

我建议您也使用这些转换或类似的东西作为所有显示元素的基础 - 一旦您拥有这些完整的日期值，您就可以根据需要将它们格式化为字符串。例如：

with cte (staff_nr, start_datetime, end_datetime) as (
  select distinct ltrim(substr(nr, -6), '0'),
      date '1900-01-01'
        + floor("JDDate" / 1000) * interval '1' year
        + (mod("JDDate", 1000) -1) * interval '1' day
        + floor("START" / 10000) * interval '1' hour
        + floor(mod("START", 10000) / 100) * interval '1' minute
        + mod("START", 100) * interval '1' second,
      date '1900-01-01'
        + floor("JDDate" / 1000) * interval '1' year
        + (mod("JDDate", 1000) -1) * interval '1' day
        + case when "END" = 240000 then interval '1' day
               else floor("END" / 10000) * interval '1' hour
                  + floor(mod("END", 10000) / 100) * interval '1' minute
                  + mod("END", 100) * interval '1' second
          end
  from ccd021
  where "JDDate" > 118000
)
select staff_nr,
  to_char(start_datetime, 'YYYY-MM-DD HH24:MI:SS') as start_datetime,
  to_char(end_datetime, 'YYYY-MM-DD HH24:MI:SS') as end_datetime,
  end_datetime - start_datetime as diff_in_days,
  24 * (end_datetime - start_datetime) as diff_in_hours,
  cast(end_datetime as timestamp) - cast(start_datetime as timestamp) as diff_as_interval
from cte
where end_datetime != start_datetime;

...这也显示了一个过滤器来删除行，开始和结束是相同的。

db<>小提琴演示

（好吧，我真的建议您重新考虑数据模型，但这似乎是不可能的......即使您现在无法替换数据的存储方式，您也可以为转换后的值添加额外的虚拟列，因此您在查询时不必自己进行转换。）

推荐阅读