Marco Portillo Marco Portillo -4 years ago 104
SQL Question

How to add variable minutes to a given date?

I have the next query (I'm using postgresql):

SELECT TIMESTAMP fecha_cita + cast((select tiempo_intervencion from cita_intervencion) as interval)
from cita;

What I'm doing here is basically taking a date like this '2001-09-28 01:00' from the 'cita' table (that's what fecha_cita is) and I want to add more time to this complete date, in this case 'tiempo_intervencion' is something like '120 minutes' but this information is in a different table called 'cita_intervencion', the problem is that since these are variables dates and times and not a fixed date, things like
SELECT TIMESTAMP '2010-11-19 01:11:22' + INTERVAL '120 minutes
doesn't work for me, I get errors like:

ERROR: syntax error at or near "fecha_cita"
LINE 1: SELECT TIMESTAMP fecha_cita + cast((select tiempo_intervenci...

ERROR: cannot cast type d_entero_p to interval
LINE 1: ...ct tiempo_intervencion from cita_intervencion) as interval) ...

I've looked up on google for some information on this and I was trying to follow this, but I can't find anything that can solve my problem.

Answer Source

You need a join and then cast the string to an interval:

SELECT c.fecha_cita, fecha_cita + ci.tiempo_intervencion::interval
from cita c
  join cita_intervencion ci on = ci.cita_id;

This assumes that there is some column in cita_intervencion that links that back to the cita table. If you really do not have that you can do something like this:

SELECT c.fecha_cita, fecha_cita + (select tiempo_intervencion::interval from cita_intervencion)
from cita c

But that will only work if cita_intervencion contains exactly one row.

The casting to an interval will only work if the values in tiempo_intervencion follow the rules for an interval.

You do not need the timestamp keyword for columns already defined as a timestamp that is only needed to introduce a timestamp literal (constant) value.

SELECT TIMESTAMP '2010-11-19 01:11:22' + INTERVAL '120 minutes doesn't work for me

That works if you add the missing ' for the interval literal:

SELECT TIMESTAMP '2010-11-19 01:11:22' + INTERVAL '120 minutes'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download