SQL TIME

What is SQL TIME data type?

TIME is a SQL data type that stores a time-of-day value without a date component, optionally including a time-zone offset.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL TIME: Supported: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, Snowflake, Redshift (TIME without time zone). Partial/limited: SQLite (stores as TEXT/NUMERIC); BigQuery supports TIME but not WITH TIME ZONE.

SQL TIME Full Explanation

TIME represents the clock time of day (hours, minutes, seconds, fractional seconds) independent of any calendar date. In the SQL standard it can be declared with optional fractional-second precision and with or without an associated time-zone offset:• TIME[(p)] WITHOUT TIME ZONE – pure wall-clock time.• TIME[(p)] WITH TIME ZONE – wall-clock time plus offset from UTC.Precision p ranges from 0 to 9 digits depending on the database; p controls how many digits follow the decimal point in the seconds field. Storage size and range vary by vendor but the logical range is 00:00:00 through 23:59:59.[fraction].Because TIME has no date, arithmetic that crosses midnight can yield negative or wrapped results. For absolute time moments, use TIMESTAMP instead. TIME WITH TIME ZONE is not supported in some engines (e.g., MySQL, SQLite) even though the SQL standard defines it.

SQL TIME Syntax

-- Standard SQL
TIME[(p)] [WITHOUT TIME ZONE]
TIME[(p)] WITH TIME ZONE

-- MySQL / MariaDB
TIME[(p)]

-- SQL Server
TIME[(p)]

SQL TIME Parameters

precision (integer) - Optional number of fractional-second digits (0-9). Defaults to vendor-specific value, usually 6.
WITHOUT TIME ZONE (keyword) - Indicates the column stores local time only.
WITH TIME ZONE (keyword) - Indicates the column stores a time value plus an offset.

Example Queries Using SQL TIME

-- Create a table holding local and zoned times
CREATE TABLE events (
  start_local TIME(0) NOT NULL,
  start_zoned TIME WITH TIME ZONE
);

-- Insert values
INSERT INTO events (start_local, start_zoned)
VALUES ('14:30:00', '14:30:00+05:00');

-- Query
SELECT start_local, start_zoned FROM events;

Expected Output Using SQL TIME

The INSERT adds one row
The SELECT returns:start_local | start_zoned------------+-------------14:30:00 | 14:30:00+05

Use Cases with SQL TIME

Storing business opening and closing hours.
Scheduling recurring daily jobs that occur regardless of date.
Capturing user preference for a reminder time (e.g., 08:00 AM).
Recording event times where the calendar date is stored elsewhere.

Common Mistakes with SQL TIME

Assuming TIME carries a date and performing date arithmetic across midnight.
Expecting TIME WITH TIME ZONE to work in engines that only implement TIME.
Forgetting to specify enough fractional-second precision for sub-second comparisons.
Comparing TIME to TIMESTAMP without casting, leading to implicit conversions or errors.

First Introduced In

SQL-92 Standard

Frequently Asked Questions

What precision does TIME support?

Most engines let you specify 0 to 6 fractional-second digits. PostgreSQL goes to 6, SQL Server to 7, Oracle to 9. Omitting p uses the default (often 6).

Can TIME values exceed 24 hours?

No. Standard SQL limits TIME to 00:00:00 through 23:59:59.[fraction]. For durations longer than a day, use INTERVAL.

How do I compare TIME and TIMESTAMP?

Cast one side to the other's type. Example for PostgreSQL: `CAST(ts AS TIME) = t` or `CAST(t AS TIMESTAMP WITH TIME ZONE)`.

Is TIME WITH TIME ZONE portable?

It is defined by the SQL standard and implemented in PostgreSQL and Oracle but missing in MySQL and SQLite. Verify support before use.