We can use the INTERVAL function to add or subtract an interval of time to or from a date:
Code: Select all
dbadmin=> SELECT SYSDATE "Right Now!",
dbadmin-> SYSDATE + INTERVAL '1 DAY 2 HOURS' "Right Now! + 26 hours",
dbadmin-> SYSDATE - INTERVAL '1 DECADE' "One decade ago",
dbadmin-> SYSDATE + INTERVAL '3 YEARS 4 MONTHS 3 HOURS 2 MINUTES 33 SECONDS' "When I'll be starting that new diet";
Right Now! | Right Now! + 26 hours | One decade ago | When I'll be starting that new diet
----------------------------+----------------------------+----------------------------+-------------------------------------
2012-07-18 15:09:50.754269 | 2012-07-19 17:09:50.754269 | 2002-07-21 15:09:50.754269 | 2015-11-15 18:12:23.754269
(1 row)
Code: Select all
dbadmin=> \x
Expanded display is on.
dbadmin=> SELECT INTERVAL '1 DAY' HOUR "Hours in a Day",
dbadmin-> INTERVAL '1 DAY' MINUTE "Minutes in a Day",
dbadmin-> INTERVAL '1 DAY' SECOND "Seconds in a Day",
dbadmin-> INTERVAL '1 MILLENNIUM' HOUR "Hours in a Millennium",
dbadmin-> INTERVAL '1 MILLENNIUM' MINUTE "Minutes in a Millennium",
dbadmin-> INTERVAL '1 MILLENNIUM' SECOND "Seconds in a Millennium (< current U.S. National Debt)";
-[ RECORD 1 ]------------------------------------------+------------
Hours in a Day | 24
Minutes in a Day | 1440
Seconds in a Day | 86400
Hours in a Millennium | 8760000
Minutes in a Millennium | 525600000
Seconds in a Millennium (< current U.S. National Debt) | 31536000000