Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Date and Time Functions

SQL TIME() function

Reference for SQL TIME(): extract the time of day from a date-time, add hours and minutes with modifiers, accepted formats and the TIME() forms in other databases.

TIME(value, modifier, ...) returns the time-of-day part of a time value as text in the form HH:MM:SS. It is the counterpart of DATE(): use it to look at the time of an event, to group by time of day, or to add and subtract hours and minutes.

Like all SQLite date functions it works on plain text. If the input has no time part, the result is 00:00:00; if it has fractional seconds, they are cut off.

SQL
SELECT TIME('2025-02-14 20:57:33');   -- 20:57:33

Try it

SQL The time of day of each order ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Formats, fractions and wrap-around

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
TIME(time_value [, modifier, ...])
Name Description
time_value A time or date-time as ISO text (HH:MM, HH:MM:SS, HH:MM:SS.SSS, or YYYY-MM-DD HH:MM:SS; T also works as separator; an optional offset such as +02:00), 'now', or a number.
modifier Optional, repeatable. For example '+2 hours', '-15 minutes', 'start of day', 'localtime'. See date modifiers.

Returns: text HH:MM:SS, or NULL if the value cannot be understood or is NULL.

Dialect differences

Database Behavior
SQLite TIME(value, modifiers...); returns text. A time without a date counts as 2000-01-01, so adding hours past midnight wraps around.
PostgreSQL No TIME() function; use ts::time or CAST(ts AS time). Arithmetic uses intervals: time '23:30' + interval '45 minutes' gives 00:15:00.
MySQL TIME(expr) extracts the time of a datetime (and keeps fractions if present). TIME values may exceed 24 hours, for example '100:00:00'.
SQL Server CAST(x AS time) or CONVERT(time, x); DATEADD(minute, 45, t) for arithmetic.
Oracle No time-only type; use TO_CHAR(d, 'HH24:MI:SS') to get the time as text.
SQL
-- PostgreSQL
SELECT ordered_at::time FROM orders;

-- MySQL
SELECT TIME(ordered_at) FROM orders;

-- SQL Server
SELECT CAST(ordered_at AS time(0)) FROM orders;

-- Oracle
SELECT TO_CHAR(ordered_at, 'HH24:MI:SS') FROM orders;

Usage notes

  • Seconds always appear. TIME('20:57') is 20:57:00. Use STRFTIME('%H:%M', ...) for a shorter display.
  • Wrap-around. Adding minutes or hours to a bare time rolls over midnight, and the date is discarded, so TIME('23:30', '+45 minutes') is 00:15:00.
  • Fractional seconds are dropped; use STRFTIME('%f', ...) (seconds with milliseconds) or the 'subsec' modifier when you need them.
  • Zone offsets in the input are converted to UTC before the time is read.
  • Time-of-day reports. Group by STRFTIME('%H', ordered_at) to count activity per hour.
  • Time math across days needs the full date-time. Keep the date, add the modifier, then call TIME() on the result.