Back to articles list
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'; 03:00:00
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?
