CODEWITHSUNDEEP
mysqlsql
tru7
tru7

Reputation: 7222

SQL repeating long formula in a select clause

In

SELECT TIMESTAMPDIFF(SECOND,since,until) dif,
       DATE_ADD(since,INTERVAL TIMESTAMPDIFF(SECOND,since,until)/2 SECOND)
FROM....

That TIMESTAMPDIFF(...) is repeated through the select. Is there a way to simplify/reuse without repeating the whole formula? The alias "dif" is not valid in the SELECT arguments

Upvotes: 1

Views: 424

Answers (2)

zambonee
zambonee

Reputation: 1647

I like to subquery whenever I have a cumbersome calculation in the select:

SELECT DATE_ADD(since, INTERVAL dif/2 SECOND) FROM (
    SELECT TIMESTAMPDIFF(SECOND,since,until) dif
    FROM....
) AS A

As a note, @clinomaniac answer is great for MySQL but is not recognized by all RDBMSs.

Upvotes: 3

clinomaniac
clinomaniac

Reputation: 2218

You can use SELECT to use the alias in the query:

SELECT TIMESTAMPDIFF(SECOND,since,until) dif,
       DATE_ADD(since,INTERVAL (SELECT dif)/2 SECOND)
FROM....

Upvotes: 3

Related Questions