0

I have a table which has 2 columns ( JobDate , RecordTime)

--------------------------------------
JobDate       | RecordTime |   SumCol
--------------------------------------
2019-07-20    | 2019-07-21 |
2019-07-19    | 2019-07-20 |

I need SumCol to have a default value as UNIX_TIMESTAMP(JobDate) + UNIX_TIMESTAMP(RecordTime)

I've tried creating a virtual column but it gives me this error :

Expression of generated column 'Test2' contains a disallowed function.

that's what I tried:

ALTER TABLE `jobsTemp` 
ADD  `SumCol` TIMESTAMP 
AS (UNIx_timestamp(`JobDate`) + UNIx_timestamp(`RecordTime`));
  • 2
    What is meaning of ((Christmas Day) + (New Year's Day))?? – Rick James Jul 20 at 19:37
  • @RickJames i know that it has no meaning but iam sorting with the two columns , instead of doing this i want to add sum in the third column and order by it only to optimize the query performance – Amr Ahmed Jul 21 at 8:24
0

If the goal is to order by two columns, then simply do that.

SELECT ...
    ORDER BY JobDate, RecordTime

This will sort the output rows by JobDate, but in the case of duplicates for JobDate, it will use RecordTime.

Performance? Not a problem, especially if can manage to get the right 'composite' index. But we need to see the actual query to discuss that.

Note that your initial thought of adding the columns could lead to a different sort order. Consider simple numbers:

id  JD  RT  JD+RT
 1  14  15    29
 2  14  13    27
 3   6  11    17
 4   7   5    12

SELECT id FROM .. ORDER BY JD+RT   -- 4, 3, 2, 1
SELECT id FROM .. ORDER BY JD,RT   -- 3, 4, 2, 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.