MySQL TIME() Function

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

The TIME() function in MySQL is used to extract the time part from a given time or datetime expression. This function is useful when you want to discard the date portion of a datetime value and only focus on the time.

Definition and Usage

  • Purpose: Extracts the time part (HH:MM:SS) from a datetime or time expression.
  • Syntax: TIME(expression)

Syntax

TIME(expression)
  • expression: The time or datetime from which you want to extract the time portion.

Technical Details

  • Works in: From MySQL 4.0 onwards
  • Return Type:
    • If the expression is a valid datetime or time, the function will return only the time part in the format HH:MM:SS.
    • If the expression is not a valid time or datetime, the function will return 00:00:00.
    • If the expression is NULL, the function will return NULL.

Examples

Example 1: Extract the time part from a datetime expression

SELECT TIME("2017-08-15 19:30:10");

Result:

19:30:10

Explanation: This extracts the time part from the given datetime value, discarding the date portion.

Example 2: Extract the time part from a datetime expression with microseconds

SELECT TIME("2017-08-15 19:30:10.000001");

Result:

19:30:10

Explanation: The microseconds part is discarded, and only the time 19:30:10 is returned.

Example 3: Extract the time part from a NULL value

SELECT TIME(NULL);

Result:

NULL

Explanation: Since the input is NULL, the function returns NULL.

Use Cases

  • Time Extraction: Useful when you want to extract just the time portion from a datetime value.
  • Data Cleaning: Helps when you need to clean up datetime values by removing the date part.
  • Log and Event Tracking: When working with logs that include timestamps, you may only need the time portion for analysis.

Key Notes

  • The TIME() function can be applied to both TIME and DATETIME data types.
  • If the expression passed to the function is a valid time or datetime, only the time portion will be returned.
  • If the expression is NULL, the function will return NULL, which is useful for handling missing or incomplete data.
Was this article helpful?