SQL SECOND

What is the SQL SECOND keyword?

SECOND is a date-time unit keyword that represents one second in interval literals, date arithmetic, and date-part extraction.

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 SECOND: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, Google BigQuery, Amazon Redshift, Snowflake, SQLite (via strftime functions)

SQL SECOND Full Explanation

SECOND is not an independent statement but a reserved word that identifies the seconds component of a temporal value. SQL engines use it in three primary contexts:1. Interval literals – SECOND declares the unit in an INTERVAL literal such as INTERVAL '30' SECOND. The engine stores the literal as a duration of 30 seconds.2. Date arithmetic – Functions like DATEADD/DATE_SUB (SQL Server, MySQL) or the + operator with INTERVAL in PostgreSQL use SECOND to add or subtract seconds from DATE, TIME, or TIMESTAMP columns.3. Date-part extraction – EXTRACT(SECOND FROM timestamp_expr) or DATEPART(second, timestamp_expr) pulls the seconds field (0-59, including fractional seconds if the data type supports them) from a temporal expression.Because SECOND is a standard date-time unit, the keyword appears across most major SQL dialects, although the exact syntax varies. Fractional seconds are supported when the underlying TIMESTAMP/TIME column stores them; otherwise results are integers. Attempting to use SECOND outside a temporal context or omitting required quotes around the numeric literal will raise a syntax error.

SQL SECOND Syntax

-- Interval literal (Standard SQL / PostgreSQL)
INTERVAL '<n>' SECOND;

-- Add seconds (PostgreSQL style)
<timestamp_expression> + INTERVAL '<n>' SECOND

-- Add seconds (SQL Server)
DATEADD(SECOND, <n>, <timestamp_expression>);

-- Extract seconds (Standard SQL)
EXTRACT(SECOND FROM <timestamp_expression>);

-- MySQL date arithmetic
DATE_ADD(<timestamp_expression>, INTERVAL <n> SECOND);

SQL SECOND Parameters

n (INTEGER or DECIMAL) - Number of seconds to add, subtract, or specify in an interval literal.

Example Queries Using SQL SECOND

-- 1. Add 45 seconds to the current timestamp (PostgreSQL)
SELECT NOW() + INTERVAL '45' SECOND;

-- 2. Extract seconds from a literal timestamp (Standard SQL)
SELECT EXTRACT(SECOND FROM TIMESTAMP '2024-03-05 14:33:22.789');

-- 3. Subtract 10 seconds in MySQL
SELECT DATE_SUB('2024-03-05 14:33:22', INTERVAL 10 SECOND);

-- 4. Add 120 seconds (2 minutes) in SQL Server
SELECT DATEADD(SECOND, 120, SYSDATETIME());

Expected Output Using SQL SECOND

Returns a TIMESTAMP value exactly 45 seconds later than NOW().
Returns 22.789 (if fractional seconds supported) or 22.
Returns the datetime 10 seconds earlier than the given literal.
Returns the system datetime plus two minutes.

Use Cases with SQL SECOND

Build rolling windows or look-back periods measured in seconds.
Capture fine-grained time differences in telemetry or IoT data.
Schedule or throttle events by adding or subtracting seconds.
Extract seconds for formatting or analytics (e.g., identify events that occurred in the final second of a minute).

Common Mistakes with SQL SECOND

Forgetting to quote the numeric literal in INTERVAL '10' SECOND (required in Standard SQL and PostgreSQL).
Swapping parameter order in DATEADD/DATE_SUB (unit must come first).
Assuming SECOND supports negative numbers inside the literal; instead subtract with the - operator or DATE_SUB.
Expecting fractional seconds when the column type lacks fractional precision.

First Introduced In

SQL-92 (temporal INTERVAL feature)

Frequently Asked Questions

What’s the simplest way to add seconds in PostgreSQL?

Use the + operator with an INTERVAL literal:SELECT now() + INTERVAL '15' SECOND;

Does EXTRACT(SECOND) return integers or decimals?

It returns decimals when the source column stores fractional seconds. Otherwise it returns an integer 0-59.

How do I add seconds in SQL Server?

Use DATEADD: SELECT DATEADD(SECOND, 10, GETDATE());

Why do some examples quote the numeric value?

The SQL standard requires the numeric part of an INTERVAL literal to be inside a quoted string. Some dialects (e.g., PostgreSQL) enforce this strictly.