Sample Header Ad - 728x90

Split up interval into year slices

5 votes
2 answers
4054 views
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?
Asked by khajlk (197 rep)
Aug 5, 2016, 12:22 PM
Last activity: Nov 21, 2024, 10:52 PM