RaqForum 41 No.
22 View  • 
     ...

From SQL to SPL: Count date ranges per year

 

The x field of the database table example is ID, and the ts field is the time interval.

x

ts

1

["2024-12-27 00:00:00","2025-02-01 00:00:00"]

2

["2025-05-01 00:00:00","2025-05-05 00:00:00"]

3

["2025-05-08 00:00:00","2025-05-20 00:00:00"]

Now we need to count which years are included in the time interval of each ID, and how many days are included in each year.

x

extract

count

1

2024

5

1

2025

32

2

2025

5

3

2025

13

SQL：

WITH RECURSIVE days as (
  SELECT x, LOWER(ts) as t FROM example 
    UNION ALL
  SELECT x, t+'1 day' FROM days 
  where t < (SELECT UPPER(ts) FROM example where x=days.x)
  )
SELECT x, extract(year from t), count(*)
FROM days
GROUP BY x,extract(year from t)
ORDER BY x,extract(year from t)

Common databases do not have data types related to time intervals, making it difficult to break down data. PostgreSQL has tsrange and daterange types, making the code relatively easy to write. However, it also requires recursive subqueries to generate date sequences, which have complex structures and are not easy to understand. SPL can directly generate date sequences: https://try.esproc.com/splx?3uS

　A

1

$select * from example.txt

2

=A1.news(periods(date(ts(1)),date(ts(2))); x,~:t)

3

=A2.groups(x, year(t):extract; count(1):count)

A1： Load data. [...] will be parsed as a sequence.

A2： Generate a date sequence using the ts field of each record, and then expand the members of the sequence to form a new two-dimensional table with the x field of this record. The function 'periods' generates a sequence based on the start and end dates, and (1) represents the first member of the sequence.

Question source：https://stackoverflow.com/questions/78171203/is-it-possible-with-sql-to-count-all-dateranges-per-year


 
 •  22 View  •