Rodolfo Bandeira

Rodolfo Bandeira

software, electronics, security, devops, thoughts

Working with date intervals in PostgreSQL

Hi everyone!

Some tips to work with date/time intervals in PostgreSQL.

The first tip is how to know what day was yesterday:

SELECT (date_trunc('DAY', now()) + INTERVAL '- 1 day')::date;

We can show the function date_trunc() and the function now(). So, the result can be formatted using the “::date” cast operator. We can use cast to convert timestamp to date also using:

cast(date_trunc('DAY', now()) + INTERVAL '- 1 day') as date

If you want to know what day is tomorrow, you can use something like this:

SELECT (date_trunc('DAY', now()) + INTERVAL '1 day')::date;

If you want to get the last day of this month, you can use something like this:

SELECT (date_trunc('MONTH', now()) + INTERVAL '1 MONTH - 1 day')::date;

You should already saw “days” in INTERVAL parameter. You can use “days” or “day”. Both will works well

Last modified:

LinkedIn
WhatsApp