I have a table representing files and the time spans they cover like so:

filename  |     start_time      |      end_time       | format |
file1.tar | 2016-11-01 19:50:02 | 2016-11-01 20:50:02 | raw    |
file2.nc  | 2016-11-01 19:50:02 | 2016-11-01 20:50:02 | nc     |
file3.raw | 2017-07-19 06:00:36 | 2017-07-19 06:01:44 | raw    |
file4.tar | 2018-08-20 00:00:01 | 2018-08-20 23:59:59 | raw    |
file5.cdf | 2018-08-20 00:00:01 | 2018-08-20 11:59:59 | cdf    |

I'm trying to find the timespans for which we have files in raw format, but no file in a non-raw format (basically, any format value except "raw").


Given the example data above, the result would look like this:

     start_time     |       end_time      |
2017-07-19 06:00:36 | 2017-07-19 06:01:44 |
2018-08-20 12:00:00 | 2018-08-20 23:59:59 |

Here is the logic behind this:

  • The timeframe from raw file1 is entirely covered by non-raw file2, so do not include it
  • The timeframe from raw file3 has no non-raw files, so include it entirely
  • The timeframe from raw file4 is partially covered by non-raw file 5, so include the uncovered portion

This is a small part of a large hairy query (involving versions, soft-delete status, location, and etc) that I'm trying to narrow down to just the part I'm having trouble with so please let me know if anything else seems strange or unclear.

From the comments:

  • There are ~29 million rows in the source table
  • I've removed to version information to make it more clear what the problem is
  • The format column will be either 'raw' or some other string. For the purposes of the question, we only care about raw or not-raw
  • The filename will not always contain 'raw' but the format column will for raw files
  • A result of tsranges is also fine, maybe even preferable


This is pretty close, and produces the two "not-fully-covered" rows, but produces the whole time span from 2018-08-20 when I want only the 12 uncovered hours.

with base as ( --create tsranges and collapse the formats
    select filename,
           case when format = 'raw' then 'raw' else 'not_raw' end as format,
           tsrange(start_time, end_time, '[)') as time_span
    from datafiles
), a as ( --compute the running max of the upper bound
    select time_span,
           coalesce(lower(time_span), '-infinity') as range_start,
           max(coalesce(upper(time_span), 'infinity')) over (partition by format order by time_span) as range_end
    from base
), b as ( --if the previous range_end is earlier than the range_start, there is a gap, start a new range
    select *, lag(range_end) over (partition by format order by time_span) < range_start or null as next_range_start from a
), c as ( --tag group numbers by counting how many next_range_starts have passed
    select *, count(next_range_start) over (partition by format order by time_span) as grp from b
), d as ( --group by group number, make the final range from the highest and lowest in each range
    select format,
        tsrange(min(range_start), max(range_end)) as time_span
    from c
    group by format, grp
select * from d raw
where format = 'raw'
and not exists (
    select 1 from d not_raw
    where format = 'not_raw'
    and raw.time_span <@ not_raw.time_span
  • 3
    please provide some textual logic on how you came up with just the one row in your desired output, eg, is this based solely on the first component of the file name (eg, file1, file2, file3, ...)? does the 'v#' come into play (eg, could there be a file5.raw.v6, and if so, is it treated separately from file5.raw.v0)? is the format ('raw' or 'nc') always the second component of the filename, or should we only rely on the contents of the format column? – markp Oct 8 '18 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.