How to Add Minutes, Hours & Months to Timestamp in PostgreSQL

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.

How to Add Minutes, Hours & Months to Timestamp in PostgreSQL

How to Add Minutes, Hours & Months to Timestamp in PostgreSQL

Related posts:

Leave a Reply Cancel reply