Countdown the days and months within Postgres-CodePudding

I know of date_part('days', age(release_date)), which will show you days of the age of the release_date col (timestamp). For example, if the release date is 1994-05-30, the date_part calculation would yield 5 days.

However, how can I find out from a countdown perspective, ie, for another release. There are 2 days left till the anniversary of this release hits? Is it just a matter of 365 - date_part('days', age(release_date)), for example? Or is there a better way?

CodePudding user response：

Transpose the release date to the current year and subtract the current date from it. Assuming release_date is an actual date:

SELECT *
     , (release_date   (date_trunc('year', LOCALTIMESTAMP)
                      - date_trunc('year', release_date)))::date
     - CURRENT_DATE AS days_till_aniversary
FROM   release;

db<>fiddle here

There is a reason for LOCALTIMESTAMP in one spot and CURRENT_DATE in the other. This way, the calculation is done without involving time zones. (Except that either depends on the time zone setting of your session to begin with.) And subtracting dates yields an integer, signifying the difference in days.

Produces negative numbers past anniversaries this year.