Mysql – Column with Default value as Sum of TIMESTAMP

dateMySQLsumtimestampvirtual-columns

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`));

Best Answer

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
Related Question