Add/Subtract minutes from timestamp based on arithmetic sign mentioned in another table

I have a table "offset_table" which have only 1 column "offset_in_minutes" (nvarchar(5)).

I will store only 1 value in offset_table.

Example 1 : " 300" - This means that query should add 300 minutes to timestamp.

Example 2 : "-30" - This means that query should subtract 30 minutes from timestamp.

Thus the arthimetic sign have the importance.

I want to add or subtract the minutes by taking this sign along with value from offset_table.

I have tried the following query, but is adding 300 seconds not minutes.

select
   start_time as original_timestamp,
   (
      start_time   (
      SELECT
         offset_in_minutes 
      from
         offset_table)
   )
   as updated_timestamp 
FROM
   students_table;

Current Result:

Expected Results:

This updated_timestamp value should be after adding 300 minutes -> '2022-10-11 06:57:52.851'

I also don't want to use ( ) sign in query. This should get populated from the sign mentioned in offset_table.

CodePudding user response：

DECLARE @Op string
Select @Op = operation from students_table where id = 1
if(@Op == " ")
 BEGIN
  --this code
 END
ELSE
 BEGIN
  --this code
 END

CodePudding user response：

You should rather use TIMESTAMPADD, properly used to add parts of timestamps to timestamps in MySQL. Also, converting your NVARCHAR value to integer will ensure that you're sum/subtraction will be automatic as long as the sign is kept during the casting operation.

SELECT start_time AS original_timestamp,
       TIMESTAMPADD(MINUTE, 
                    CAST(offset_in_minutes AS UNSIGNED),
                    start_time) AS updated_timestamp
FROM       students_table
INNER JOIN offset_table ON 1 = 1;

Check the demo here.

Note: This solution assumes your offset table contains exactly one value, as specified in the problem statement.