CODEWITHSUNDEEP
postgresql
Anu Priya
Anu Priya

Reputation: 43

How to add number of days to a date field in postgresql

I have a date field in a table named product. I need to add some days to this field how to d this. I am new to PostgreSQL.

I tried this:

select manufacture_dt + weeks*7 from product;

It is giving me the error

operator does not exist: timestamp without time zone + integer.
Hint no operator matches the given name and argument types .you might need to add  explicit type cast

Upvotes: 1

Views: 4703

Answers (3)

Pravash Panigrahi
Pravash Panigrahi

Reputation: 720

Assuming 'weeks' is a column containing number of days in integer in that table 'product', If you want to multiply 'weeks' columns value with the 'manufacture_dt' column you can try below query :-

select
       manufacture_dt + interval '7 day' * weeks as new_date
from product

https://www.postgresql.org/docs/current/functions-datetime.html

Upvotes: 0

user330315
user330315

Reputation:

You don't have a date column, you have a timestamp column.

You can only add an interval to a timestamp value

If weeks is another column in your table, you can use make_interval to generated the interval

select manufacture_dt + make_interval(weeks => weeks::int)
from product;

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 248215

There is no + operator for timestamp and integer, but there is one for timestamp and interval. Moreover, intervals can be multiplied with numbers.

So you could write:

SELECT manufacture_dt + weeks * INTERVAL '7 days'
FROM product;

The syntax DATATYPE 'somevalue' is SQL syntax for a constant (literal) of a certain data type.

Upvotes: 4

Related Questions