How do I add a dynamic (column based) number of days to NOW?
SELECT NOW() + INTERVAL a.number_of_days "DAYS" AS "The Future Date"
FROM a;
Where a.number_of_days is an integer?
Add a comment
|
7 Answers
I usually multiply the number by interval '1 day' or similar, e.g.:
select now() + interval '1 day' * a.number_of_days from a;
3 Comments
DeniseMeander
Nice trick, but it seems to me the better solutions are the ones below where they cast the concatenated field(s) to a interval, either using CAST or ::interval.
Jezor
Why do you think it is a better solution @DeniseMeander?
mickmackusa
Our team has agreed to implement this technique throughout a project because it is more human readable to declare the unit of the interval (
1 day) then multiply that interval by our (sometimes convoluted) SQL arithmetic. In some ugly cases with have a calculated interval inside of a calculated interval -- not nice. With this implementation, I don't need to write ::INTERVAL after appending ' DAYS' to a calculated integer.I know this is a year old, but if you need to use a column to specify the actual interval (e.g. 'days', 'months', then it is worth knowing that you can also CAST your string to an Interval, giving:
SELECT now()+ CAST(the_duration||' '||the_interval AS Interval)
So the the original question would become:
SELECT now() + CAST(a.number_of_days||" DAYS" AS Interval) as "The Future Date" FROM a;
3 Comments
araqnid
if you want more control over what the values in the units column would be, you can decode it yourself:
select *, num * case unit when 'W' then '1 week'::interval when 'D' then '1 day'::interval when 'H' then '1 hour'::interval end from (values(1,'W'),(2,'D'),(3,'H')) x(num, unit)
leole
thanks, that helped me a lot. But don't forget to change the quotation marks for postgres:
SELECT now() + CAST(a.number_of_days||' DAYS' AS Interval) as "The Future Date" FROM a;
Ali Taheri
i tried to long to find out solution for this problem and in conclude i read this answer and after try this, it worked correctly. thanks man
I prefer this way. I think its pretty easy and clean.
In Postgres you need interval to use + operator with timestamp
select (3||' seconds')::interval;
select now()+ (10||' seconds')::interval,now();
where you can use seconds, minutes, days, months... and you can replace the numbers to your column.
select now()+ (column_name||' seconds')::interval,now()
from your_table;
Comments
Use make_interval()
SELECT NOW() + make_interval(days => a.number_of_days) AS "The Future Date"
FROM a;
But in general it might be a better idea to use a column defined as interval, then you can use any unit you want when you store a value in there.
2 Comments
VLRoyrenn
days => a.number_of_days appears to be Postgres' keyword syntax, and make_interval only has parameters for years, months, weeks, days, hours, mins and secs. That means it wouldn't work for all units like interval '1 millisecond' * msTime does, but it's still a fair alternative for most cases.To creating intervals those based on column values, I recommend to add two columns in your table. For example, column "period_value"::INT4 and column "period_name"::VARCHAR. Column "period_name" can store the following values:
- microsecond
- milliseconds
- second
- minute
- hour
- day
- week
- month
- quarter
- year
- decade
- century
- millennium
+--------------+-------------+ | period_value | period_name | +--------------+-------------+ | 2 | minute | +--------------+-------------+
Now you can write:
SELECT NOW() - (period_value::TEXT || ' ' || period_name::TEXT)::INTERVAL FROM table;
If we have field with interval string value such as '41 years 11 mons 4 days' and want to convert it to date of birth use this query :
UPDATE "february14" set dob = date '2014/02/01' - (patient_age::INTERVAL)
dob is date field to convert '41 years 11 mons 4 days' to '1972/10/14' for example
patient_age is varchar field that have string like '41 years 11 mons 4 days'
And this is query to convert age back to date of birth
SELECT now() - INTERVAL '41 years 10 mons 10 days';
Comments
Updating based on a column ID was a useful way to create some randomised test data for me.
update study_histories set last_seen_at = now() - interval '3 minutes' * id;
