AndrewMontana AndrewMontana - 1 year ago 123
SQL Question

Convert double precision to date format

I have a date and I need add to it some days. I have a double variable with value for example 27, how can I add it to my current date?

Answer Source

Use datemath with the interval type...

test=# SELECT now() + interval '27 days';
 2017-05-10 03:44:44.807499-05
(1 row)

If the 27 is as an int, numeric, or double precision, use make_interval (cast to int if need be)

SELECT now() + make_interval(0,0,0,27);
SELECT now() + make_interval(0,0,0,yourcol::int);

Here is the signature

make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0)

@Pozs raises a valid point

Casting like that may cause serious precision loss (i.e. the value is 27.9) -- Multiplying on the other hand may suit OP's needs better (f.ex. 27.9 * interval '1 day'). There is some rounding error here too, but the impact is much smaller. – pozs

I assumed there were no fractional days. If there were you can use this method he mentions to do the job.

SELECT now() + yourcol * interval '1 day';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download