3

Being a novice Postgres user, I have a table ad in my PostgreSQL 9.5 (x64) database with 87 rows. In addition to other columns, it has two columns 'start' and 'end' having date-time duration range like this:

ID      Start                   End
1       2003-06-07 00:00:00     2004-09-30 23:59:59

I need to split the range into one year windows in separate rows (from start year to the last year of interval) stored in database like this:

ID       Start
1_2003   2003-06-07 00:00:00     2003-12-31 23:59:59
1_2004   2004-01-01 00:00:00     2004-09-30 23:59:59

Using the operator ||'_'|| and Extract() function, I am able to concatenate ID with year. Also, this question addresses how to split up in interval in weeks and this one shows how to do the same for days but none of them addresses how to split up an interval in years specifically.

I avoided this question because I don't want to go for a stored procedure based approach. I am aware that generate_series() returns a series from start and stop parameters but actually I am struggling to break the interval at last day of the year and then re-starting from first day of the year in next row. I would highly appreciate if someone could guide me to this?

  • Look at the example. Read about CTE here, about generate_series function here and about range types here and here. Good luck. – Abelisto Aug 5 '16 at 15:18
  • It looks like start and end do not need a time component, since you always work with whole days? Correct? And please always provide your version of Postgres and the table definition showing data types and constraints. – Erwin Brandstetter Aug 6 '16 at 13:20
  • @ErwinBrandstetter: That's true. I need a timestamp (date-time with year) and not specifically a time component only. I am using PostgresSQL version 9.5. Table "ad" contains 87 rows. Start and End columns are in data type 'timestamp without time zone' and ID column is of type 'text'. – khajlk Aug 7 '16 at 19:06
  • BTW, you link to my answer twice, but I suspect the second time is by mistake since you mention "stored procedures" which are not involved. – Erwin Brandstetter Aug 8 '16 at 1:06
  • @ErwinBrandstetter: You are so right. Mentioning your answer twice was unintentional because I saw stored procedure based approach as well and I did not want to use that specifically. – khajlk Aug 8 '16 at 8:36
3

A couple of suggestions:

  • Use legal, lower-case, unquoted identifiers to save yourself a lot of confusion. end (and, to a lesser degree start) are reserved words.

  • Your id column seems to be numeric. Should be integer or something, not text.

  • Since you don't need a time component in start_day and end_day, the appropriate data type is date, not timestamp.

  • Why concatenate id and the year for a new "ID"? Add a second column year if you need it for a PK. Or don't add an additional column at all. It can cheaply be extracted from the new start_day on the fly. (Generally, don't store data redundantly unless you positively need to.)

  • Typically, timestamp ranges use an inclusive lower bound and an exclusive upper bound. Since timestamps can have fractional digits (up to 6 in Postgres) that is much cleaner. Your input 2003-12-31 23:59:59 would fail for 2003-12-31 23:59:59.123.

So, your table should look like this:

CREATE TABLE ad (
  id        int PRIMARY KEY
, start_day date NOT NULL    -- *inclusive* lower bound
, end_day   date NOT NULL    -- *exclusive* upper bound
CHECK (end_day > start_day)  -- enforce legal input
);

Proper test values:

INSERT INTO ad(id, start_day, end_day)
VALUES
  (1, '2003-06-07', '2004-10-01')   -- span 2 years (your example)
, (2, '2003-06-07', '2003-06-08')   -- 1 day in same year
, (3, '2003-06-07', '2003-10-01')   -- span 1 year
, (4, '2003-06-07', '2006-10-01');  -- span many years

Solution

Use generate_series() in a LATERAL join, based on start and end day, truncated to the year with date_trunc(). This produces one row per year with the new start date. Add a year and you have the new end date. Except for first and last row per id, where you substitute the proper start / end with GREATEST and LEAST respectively. Voilá.

-- CREATE TABLE ad_year AS
SELECT ad.id
     , extract('year' FROM y)::int AS year
     , GREATEST(y                    , ad.start_day) AS start_day
     , LEAST   (y + interval '1 year', ad.end_day)   AS end_day
FROM   ad
     , generate_series(date_trunc('year', start_day::timestamp)  -- cast to ts here!
                     , date_trunc('year', end_day::timestamp)
                     , interval '1 year') y;

Note that date_trunc() returns timestamptz for timestamptz input and timestamp for timestamp input. For date input it defaults to timestamptz. Since you seem to be ignoring time zones, cast the date to timestamp explicitly (start_day::timestamp).

Result:

 id | year |      start_day      |       end_day
----+------+---------------------+---------------------
  1 | 2003 | 2003-06-07 00:00:00 | 2004-01-01 00:00:00
  1 | 2004 | 2004-01-01 00:00:00 | 2004-10-01 00:00:00
  2 | 2003 | 2003-06-07 00:00:00 | 2003-06-08 00:00:00
  3 | 2003 | 2003-06-07 00:00:00 | 2003-10-01 00:00:00
  4 | 2003 | 2003-06-07 00:00:00 | 2004-01-01 00:00:00
  4 | 2004 | 2004-01-01 00:00:00 | 2005-01-01 00:00:00
  4 | 2005 | 2005-01-01 00:00:00 | 2006-01-01 00:00:00
  4 | 2006 | 2006-01-01 00:00:00 | 2006-10-01 00:00:00

If you create a new table fro the result, I suggest (id, year) as primary key.

Aside: This is not an operator: ||'_'||, it's 2 concatenation operators || and a string literal '_'.

  • Thanks for your help and useful suggestions. Much helpful. – khajlk Aug 8 '16 at 8:42
0

Here's my attempt. It's a bit messy but you can probably figure out how to take it from here.

select 
    x.id||'_'||extract('year' from x.series) as id,
    case 
            when extract('year' from x.start) = extract('year' from x.series) then x.series 
            else date_trunc('year', x.series) 
    end as start,
    case 
            when extract('year' from x.end::timestamp) = extract('year' from x.series) then x.end
            else date_trunc('year', x.series) + interval '1 year' - interval '1 second' 
    end as finish 
from
    (select 
            'id' as id, 
            '2003-06-07 00:00:00'::timestamp as start,
            '2006-09-30 23:59:59'::timestamp as end,
            '2003-06-07 00:00:00'::timestamp 
                    + generate_series(0,
                            datediff('year', 
                                    date_trunc('year','2003-06-07 00:00:00'::timestamp), 
                                    date_trunc('year', '2006-09-30 23:59:59'::timestamp)
                                    )
                            )                                                
                    *interval '1 year' as series
    ) x;
  • Just one note: day have no the inclusive upper bound for the timestamp(tz) type, mention the value like 2006-09-30 23:59:59.123456789 for example. – Abelisto Aug 6 '16 at 6:41
  • @peach: Thanks for your feedback. It was indeed helpful. – khajlk Aug 8 '16 at 8:31

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.