CODEWITHSUNDEEP
postgresql
khajlk
khajlk

Reputation: 861

How to partition timestamp duration in multiple rows using PostgreSQL 9.5?

I have a table 'ad' in my postgres DB containing 'start' and 'end' columns like:

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

How could I break this duration (from 2003 to 2004) in two parts so that start and end dates (timestamps) should remain in the same year like this?

ID        Start                   End
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

I was wondering whether window function would be helpful in achieving this? I am using PostgreSQL version 9.5 (x64).

Upvotes: 0

Views: 314

Answers (1)

klin
klin

Reputation: 121764

Example data:

create table ad (id text, start_t timestamp, end_t timestamp);
insert into ad values
(1, '2003-06-07 00:00:00', '2004-09-30 23:59:59'),
(2, '2002-02-02 00:00:00', '2004-04-04 23:59:59');

Query: 

select 
    concat(id, '_', y) id, 
    case 
        when extract(year from start_t)::int = y then start_t 
        else make_timestamp(y, 1, 1, 0, 0, 0) end as "start",
    case 
        when extract(year from end_t)::int = y then end_t 
        else make_timestamp(y, 12, 31, 23, 59, 59) end as "end"
from 
    ad, 
    generate_series(extract(year from start_t)::int, extract(year from end_t)::int) y;

   id   |        start        |         end         
--------+---------------------+---------------------
 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
 2_2002 | 2002-02-02 00:00:00 | 2002-12-31 23:59:59
 2_2003 | 2003-01-01 00:00:00 | 2003-12-31 23:59:59
 2_2004 | 2004-01-01 00:00:00 | 2004-04-04 23:59:59
(5 rows)

Upvotes: 1

Related Questions