MySQL SUBTIME() Function

  1. Overview
  2. MYSQL
  3. Date Functions
  4. MySQL SUBTIME() Function

The SUBTIME() function in MySQL is used to subtract a specified time interval from a given time or datetime value. It returns the resulting time or datetime after the subtraction.

Definition and Usage

  • Purpose: Subtracts a time interval (like seconds, minutes, or hours) from a given time or datetime expression and returns the updated result.
  • Syntax: You can specify both positive and negative values for time intervals.

Syntax

SUBTIME(datetime, time_interval)

Parameters

  1. datetime: Required. The original time or datetime that will be modified.
  2. time_interval: Required. The time interval to subtract from the datetime. This can be in the format of hours, minutes, and seconds (e.g., 3:2:5 or 5.000001 for seconds).

Technical Details

  • Works in: MySQL 4.0 and later
  • Return Type: TIME or DATETIME, depending on the input type.
  • Behavior: The function subtracts the specified time interval from the given datetime value.

Examples

Example 1: Subtract 5.000001 seconds from a datetime

SELECT SUBTIME("2017-06-15 10:24:21.000004", "5.000001");

Result: 2017-06-15 10:24:15.000003
Explanation: This subtracts 5.000001 seconds from 2017-06-15 10:24:21.000004, resulting in 2017-06-15 10:24:15.000003.

Example 2: Subtract 3 hours, 2 minutes, and 5.000001 seconds from a datetime

SELECT SUBTIME("2017-06-15 10:24:21.000004", "3:2:5.000001");

Result: 2017-06-15 07:22:15.000003
Explanation: This subtracts 3 hours, 2 minutes, and 5.000001 seconds from 2017-06-15 10:24:21.000004, resulting in 2017-06-15 07:22:15.000003.

Example 3: Subtract 5 seconds from a time value

SELECT SUBTIME("10:24:21", "5");

Result: 10:24:16
Explanation: This subtracts 5 seconds from 10:24:21, resulting in 10:24:16.

Example 4: Subtract 3 minutes from a time value (300 seconds)

SELECT SUBTIME("10:24:21", "300");

Result: 10:21:21
Explanation: This subtracts 300 seconds (which is equivalent to 5 minutes) from 10:24:21, resulting in 10:21:21.

Example 5: Add 3 hours, 2 minutes, and 5 seconds to a time value (using a negative interval)

SELECT SUBTIME("10:24:21", "-3:2:5");

Result: 13:26:26
Explanation: This adds 3 hours, 2 minutes, and 5 seconds to 10:24:21 because the interval is negative, resulting in 13:26:26.

Use Cases

  • Time Adjustments: Useful for subtracting specific intervals from a time or datetime, such as adjusting timestamps in event logging.
  • Real-Time Calculations: Often used in scenarios where you need to adjust times for deadlines, scheduling, or calculating time differences.
  • Time Manipulation: Works with various time units like seconds, minutes, and hours, providing flexibility for time calculations.

Key Notes

  • The time_interval parameter can accept values in various formats like HH:MM:SS or SECONDS, allowing for flexible usage.
  • Negative time intervals will add the specified time instead of subtracting it, making the function versatile for both subtraction and addition operations.
Was this article helpful?