Add one day to Now() and return as default value in SQL query-CodePudding

I am attempting to add a day to NOW() and return as the values for a column. This works

SELECT NOW() as date

But this gives an error

SELECT DATE_ADD( NOW(), INTERVAL 1 DAY) as date

Is there a way to achieve this in a postgres query? Thanks

CodePudding user response：

I don't think there's a date_add() function in PostgreSQL:

ERROR:  function date_add(timestamp with time zone, interval) does not
exist
LINE 1: select date_add(now(), interval '1 day');
               ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

but you can use a regular operator to add an interval to timestamptz that's returned by now(). Demo:

select now()   '1 day'::interval;

You can define that function for convenience:

create function date_add(arg1 timestamptz, arg2 interval) 
returns timestamptz language sql as $$
select arg1 arg2
$$;

select date_add(now(), interval '1 day') as date;

--             date
---------------------------------
-- 2022-11-29 12:28:12.393508 00

But I don't think it's really more convenient than the operator. You'd also have to overload it to make sure how it deals with different types - you can see in the demo how by default PostgreSQL will try to guess and cast automatically.