Question 113216 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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:

QueryResult

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.

Tags:

2

Answers


  1. 0 
    DECLARE @Op string
Select @Op = operation from students_table where id = 1
if(@Op == "+")
 BEGIN
  --this code
 END
ELSE
 BEGIN
  --this code
 END
    Login or Signup to reply.
  2. 0

    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 your 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search