Calculating season timelines for a farm Long time lurker, first-time poster. I have been looking for a solution to this problem for a while and can't quite think of what to search for anymore. 

I work for a farm and we have this farm management system that allows people to put in activities (such as planting/irrigating/etc...). I'm ultimately trying to calculate every activity day from planting so each field is comparable to each other. The current problem I'm running into is calculating the 'season' timeline as we might have multiple crop plantings in a field within the year. 

BTW Postgres used as my DB

Essentially my table looks like this: 


Ideally, I would like to somehow query this to create a date range seasonality table so I could cross-reference all other activities to see what "season" the activity falls under and as such can grab the correct planting date. 

I'm thinking the table would look something like: 



I assume some kind of log transform query for user activities on websites might work similarly to categorize time ranges, but I haven't found anything helpful yet. 

Let me know if you have any thoughts/ideas!

Thank you!
It seems you need in simple

SELECT Paddock, 
       Date_Time_Completed Season_Start,
       COALESCE(LEAD(Date_Time_Completed) OVER (PARTITION BY Paddock
                                                ORDER BY Date_Time_Completed),
                CURRENT_DATE) Season_End,
       Crop,
       YEAR(Date_Time_Completed) 
           || '_' 
           || ROW_NUMBER() OVER (PARTITION BY Paddock
                                 ORDER BY Date_Time_Completed) Season_Name
FROM source_table