Select DATEADD()

[OscarLucas]

Hello,
I want to get the initial time + the time inverted in the task as a column.
I am using dateadd. I don’t know if is the way, and i don’t understand where can be the mistake. I am following the example.
https://docs.inductiveautomation.com/display/DOC79/Data+Partitioning+and+Pruning

[Transistor]

What is the datatype of tiempo_invertido? It needs to be numeric and not a TIME variable.

I like to use https://www.w3schools.com/sql/func_sqlserver_dateadd.asp for testing SQL scripts.

[peter]

What database are you using? The error messages says:

caused by SQLSyntaxErrorException: (conn=91837) FUNCTION tallerelectrico.dateadd does not exist

The DATEADD function is available in MSSQL (as mentioned in the link you provided), but it does not exist in MySQL or Oracle databases.

---

Oracle

I'm not really up on Oracle, but this link shows various methods you can use to add 1 hour to column. I like this example:

select some_date_column + interval '1' hour 
from your_table;

NOTE that the actual amount of time ('1') is specified as a string

---

MySQL

For MySQL you would use the DATE_ADD() function, EG:

SELECT DATE_ADD("2017-06-15", INTERVAL 1 HOUR);

[OscarLucas]

work with numbers but do not work with other column. i don’t get any system error.
tiempo_invertido = int = hours = 4 for example

[lrose]

You aren’t providing a numeric value to the query but instead the string ‘tiempo_invertido’, which your DB undoubtedly doesn’t know how to handle.

Try something like this to use a variable value:

SELECT referencia_instalacion as ItemId,
count(tarea,' ',usuario) as label,
fecha_inicio as startDate,
fecha_inicio + numtodsinterval({Path to property or tag},'hour') as EndDate
FROM tareas

More on the numtodsinterval function.

[OscarLucas]

whit the number works well and tags, but i want to select the time that takes the task to be completed. That is on the data base…

[lrose]

Just use the column name in the function

SELECT referencia_instalacion as ItemId,
count(tarea,' ',usuario) as label,
fecha_inicio as startDate,
fecha_inicio + numtodsinterval(tiempo_invertido,'hour') as EndDate
FROM tareas

[OscarLucas]

Looks like do not recognize the numtodsinterval, the ‘tiempo invertido’ is not a date. is just am integer.
Thanks for your help.

[lrose]

What flavor of DB are you using?

numtodsinterval is an Oracle function.

[OscarLucas]

Mysql - I was using interval …

[JordanCClark]

Change numtodsinterval to interval in your query.

[lrose]

For MySql the query should look like this:

SELECT referencia_instalacion as ItemId,
count(tarea,' ',usuario) as label,
fecha_inicio as startDate,
fecha_inicio + INTERVAL tiempo_invertido HOUR as EndDate
FROM tareas

Your first attempt at this didn’t work because you used quotes around the column name making it a string literal which INTERVAL in MySQL can’t use.

[OscarLucas]

Finally I don’t get any failure, but the data type looks that has been changed in an strange format.

And the time 1h, has been added in the seconds, instead than in the hours.

It is a good idea?

[JordanCClark]

Try as @lrose suggested:

fecha_inicio + INTERVAL tiempo_invertido HOUR as EndDate

[OscarLucas]

[lrose]

You left the comma “,” between the column name and HOUR.

[OscarLucas]

something very small makes the difference. now is working

[peter]

I am very aware that in a program that is 10’s or 100’s of Megabytes in size, your day can be ruined by a single BIT being the wrong way around