SQL Add Integer Column to Date column

Question

This works:

SELECT "date", "value"::Int, "date" + interval '1' day AS "computed" 
FROM "tab1"

but what if I want to add a variable from the table to a timestamp?

SELECT "date", "value"::Int, "date" + interval "value" day AS "computed" 
FROM "tab1"

This fails.

date is a date value then you can simply use "date" + "value" — user330315
– user330315, Commented Dec 2, 2021 at 23:04

Caius Jard · Accepted Answer · 2021-12-02 20:56:27Z

1

An interval is like a number, and can be multiplied

SELECT "date", "value"::Int, "date" + interval '1' day * "value" AS "computed" 
FROM "tab1"

Not sure if your cast to int was indicative of it being e.g. some decimal like 1.5, but if it is then 1.5 days would be added. If you want just the integral part of value to matter, then consider something like "date" + interval '1' day * "value"::int

edited Dec 2, 2021 at 20:56

answered Dec 2, 2021 at 20:48

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Organek Over a year ago

FYI there is also a make_interval(days => value) function. postgresql.org/docs/current/functions-datetime.html

Caius Jard Over a year ago

There is, though not in oracle (but this works there too) - too much to keep in my brain about the differences between db vendors; I'll take any common ground I can remember! :)

Collectives™ on Stack Overflow

SQL Add Integer Column to Date column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related