Back to articles list
- 1 minutes read

Date Arithmetic in PostgreSQL

If your default programming language, like mine, is Java, you most likely wince at the very thought of date arithmetic. (It’s changed for the better with Java 8 but Vertabelo is not there yet.)

The date arithmetic API in PostgreSQL is like a breath of fresh air.

Interval Arithmetic

You can represent a time interval:

select interval '2 days';
'2 days'

select interval '3 hours';

select interval '5 weeks';
'35 days'

Add an interval to a date or subtract an interval from a date:

select current_timestamp;
2014-05-12 12:44:45.97619

select current_timestamp – interval '2 hours';
2014-05-12 10:44:45.97619

select current_timestamp + interval '5 days';
2014-05-17 12:44:45.97619

Truncating Dates

You can truncate dates to the specified precision.

select date_trunc('hour', current_timestamp);
2014-05-12 12:00:00.0

select date_trunc('day', current_timestamp);
2014-05-12 00:00:00.0

... including fancy precisions like week (returns the beginning of Monday)

select date_trunc('week', current_timestamp);
2014-05-12 00:00:00.0

... or month precision (midnight on the first day of the month):

select date_trunc('month', current_timestamp);
2014-05-01 00:00:00.0

How cool is that?

go to top