Postgres is an awesome database for a lot of reasons, put simply it's a really feature rich, powerful, and reliable database. One of those rich areas

Fun With Postgres Functions

submited by
Style Pass
2022-09-21 21:30:12

Postgres is an awesome database for a lot of reasons, put simply it's a really feature rich, powerful, and reliable database. One of those rich areas is the many built-in functions in Postgres.

Now let's look at one that is a little bit more interesting. If you want to do a rollup by month, by week, by day, really any time interval you could do comparison of greater than or less than various months, or you could truncate the date. A simple example of this could be calculating your signups per week, where day is the data you have per row.

Shifting a little bit, let’s take a look at something even more complicated. Earlier this week I wanted to calculate what percentage of time remained in the month. For example if it was the very start of the day on the 15th of the month, in February we'd be 50% of the way through the month, but in January we'd only be 45% of the way through the month.

My table events has a column period which is a range type (a single datatype that has a from and a to with two timestamp values). On the query below we do a few things:

Leave a Comment