PostgreSQL is a powerful database management system that allows you to perform various operations on your data. It has plenty of functions and operators to work with date & time columns, as well as literal strings. Sometimes you may need to add a specific amount of minutes, hours & months to your date/time value. In this article, we will learn how to add minutes, hours & months to timestamp in PostgreSQL.
How to Add Minutes, Hours & Months to Timestamp in PostgreSQL
There are many ways to add minutes, hours, days and months to your date/time value in PostgreSQL. We will use ‘interval’ operator for our article, as it is very easy to remember and use.
Let us say you have table sales(order_id, order_date, amount, product_id) in PostgreSQL. Here is the query to add 1 minute to order_date column of sales table.
psql> select order_date + interval '1 minute' from sales;
Please note, in the above SQL query, we need to put 1 minute within quotes. We need to basically add +/- sign after date/time column, depending on whether you want to add or subtract time value. After that, add the interval keyword, followed by the amount of days/time that you want to add/subtract.
Here is the query to subtract 1 minute from order_date column.
psql> select order_date - interval '1 minute' from sales;
Similarly, here is the command to add/subtract hours from datetime values in PostgreSQL. In this case, you need to use keyword ‘hour’ when you specify time interval in PostgreSQL
# add/subtract 1 hour from date/time column psql> select order_date + interval '1 hour' from sales; psql> select order_date - interval '1 hour' from sales;
Similarly, you need to use keyword day in order to add/subtract days from date/time values.
# add/subtract 1 day from date/time column psql> select order_date + interval '1 day' from sales; psql> select order_date - interval '1 day' from sales;
You need to use the keyword ‘month’ to add/subtract month from date/time values.
# add/subtract 1 month from date/time column psql> select order_date + interval '1 month' from sales; psql> select order_date - interval '1 month' from sales;
Please note, interval operator works with date/time/datetime columns in PostgreSQL.
You can also use interval operator with system datetime values, not just columns. Here are some queries to add/subtract minutes, hours, days, months to current datetime in PostgreSQL. now() function returns the current datetime in PostgreSQL
# add/subtract 1 minute from present date/time value psql> select now() + interval '1 minute' from sales; psql> select now() - interval '1 minute' from sales; # add/subtract 1 hour from present date/time value psql> select now() + interval '1 hour' from sales; psql> select now() - interval '1 hour' from sales; # add/subtract 1 day from present date/time value psql> select now() + interval '1 day' from sales; psql> select now() - interval '1 day' from sales; # add/subtract 1 month from present date/time value psql> select now() + interval '1 month' from sales; psql> select now() - interval '1 month' from sales;
You can also use interval operator inside WHERE clause, to select rows within a certain interval. Here is an example to select rows within last 1 minute, 1 hour, 1 day, 1 month.
# get records in the last 1 minute psql> select * from sales where order_date > now() - interval '1 minute'; # get records in the last 1 hour psql> select * from sales where order_date > now() - interval '1 hour'; # get records in the last 1 day psql> select * from sales where order_date > now() - interval '1 day'; # get records in the last 1 month psql> select * from sales where order_date > now() - interval '1 month';
In this article, we have seen how to add minutes, hours, months to date/time values in PostgreSQL.
Also read:
Find Files with Special Characters in Filename in Linux
How to Find Last Occurrence of Character in String in JS
How to Find Last Occurrence of Character in String in Python
How to Split Python List to N Sublists
How to Insert Text At Certain Line in Linux