How to calculate a column in a query using a function?

Question

question

Thijs Damsma asked Mar 7, '17 Thijs Damsma answered Mar 9, '17

How to calculate a column in a query using a function?

I can query a queue like below, which works as expected.

query("SELECT time, value, time + 10 AS time_2 FROM $1 Queue",
	node("MyQueue>stats/stats_content/history", model())
);
dumpquery(reftable("querydump"), 1);

This works as expected, and I can apply a formula to one of the outputs to calculate something on the fly.

Now I want to get the formatted time in the answer instead of the query, by applying the formula:

convert(<time>,MODEL_TIME,DATETIME_STR)

How can I do that?

I tried

query("SELECT convert(time,MODEL_TIME,DATETIME_STR) AS formatted_time, value FROM $1 Queue",
	node("MyQueue>stats/stats_content/history", model())
);

And:

query("SELECT $2 AS formatted_time, value FROM $1 Queue",
	node("MyQueue>stats/stats_content/history", model()),convert($iter(1),MODEL_TIME,DATETIME_STR));

But I can't get the correct reference to the time column.

Software Version:

Choose One

queue query

Answer 1 · 2017-03-08T09:21:13Z

Mischa Spelt answered Mar 8, '17

Hi Thijs,

Some functions are supported in SQL Statements but convert doesn't seem to be one of them. Even if it was, you cannot use #defined constants in SQL statements.

I tried getting around the use of constants with

query("SELECT time, value, convert(time, $2, $3) AS time_2 FROM $1 Queue",
	node("MyQueue>stats/stats_content/history", model()), 
	MODEL_TIME, DATETIME_STR
);

or even plugging in the constant values directly

query("SELECT time, value, convert(time, 3, 6) AS time_2 FROM $1 Queue",
	node("MyQueue>stats/stats_content/history", model())
);

but the problem really seems to be with convert.

Your $iter(1) solution doesn't really work here because you are iterating over a bundle, so instead of a reference to a node you get a memory address which you cannot do much with. If the content history were a table instead of a bundle, this would work.

What I ended up doing is defining a User Command ToDateTimeStr with just

return convert( param( 1 ), MODEL_TIME, DATETIME_STR );

and then you are able to use that in your query:

query("SELECT time, value, ToDateTimeStr( time ) AS time_2 FROM $1 Queue",
	node("MyQueue>stats/stats_content/history", model())
);

It has been a wish of mine for a while to be able to use global constants in query statements, but fortunately the User Command solution works quite well (I also use it a lot, for example, to encapsulate complex pull logic in list queries such as WHERE IsTransportAllowed(puller.transporter, value.order) ).

Answer 2 · 2017-03-09T12:43:57Z

Thijs Damsma answered Mar 9, '17

Thanks Mischa! I had a hard time grasping what the $iter refers to in this case. Can you point me to some in depth documentation on this? I find that every time I write a query that does something moderately advanced, I have to waste a few hours to get it right as I immediately hit some not or sparsely documented edge case.

question