Postgresql – Calculating season timelines for a farm

postgresqlquery

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!

Best Answer

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

Related Solutions

Mongodb – How to store and query products the price of which depends on a complex set of conditions

Check out chapter 3 of

The Data Model Resource Book Volume 1 by Silverston

It has a pricing model that is very flexible

A very brief summary:

It's kinda complicated...

(*) means this column references another table

PriceComponent

id
fromDate
thruDate null
price null
percent null
geographicBoundary* [pizza costs more outside of city]
partyType* [surcharge to seniors]
productCategory* [discount for CRT monitors]
quantityBreak* [discount for high volume]
orderValue* [discount for big order]
saleType* [surcharge for retail]
currency* [discount for Canadians]
productFeature* 
product*

BasePrice : PriceComponent

DiscountComponent : PriceComponent

SurchargeComponent : PriceComponnent

ManufacturersSuggestedRetailPrice : PriceComponent

Charge : PriceComponent

OneTimeCharge : Charge

RecurringCharge : Charge

UtilizationCharge : Charge

So, the price of something is a set of PriceComponents, which can be either an actual price amount or a percentage. You can have a component of the final price based on geographical location, the type of sale (retail vs wholesale), the value of the order and so on.

Postgresql – timelines and history files PostgreSQL 9.1

When the slave is promoted to a new master it creates a new timeline to avoid WAL names overlapping. The .history file contains information about database timeline branches. Recovery process uses this information to determine the timeline it is working with. By default it uses the same timeline that was when base backup was made. So, for example, if you promote one of your slaves to a new master other slaves will not be able to continue recovery from the new master as they use previous timeline.

To make them able to use current timeline, in easy words to switch them to the new master, you need them to know about the new history. Just delete everything in the pg_xlog directory on slaves and copy the history file from the new master. And then set recovery_target_timeline to 'latest' to force the database using the latest found timeline.

Note that the slave pretending to be a new master must be the most catch up one. Otherwise it might lead to data corruption on other slaves after you switch them to the new master.

Related Question