SQL AT

What is the SQL AT keyword?

AT is a reserved keyword used in clauses like AT TIME ZONE, AT LOCAL, and EXECUTE ... AT to specify a time-zone context or a remote execution location.

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 AT:

SQL AT Full Explanation

AT appears in multiple parts of the SQL standard and vendor extensions.1. Time-zone context - AT TIME ZONE converts a timestamp without time zone into TIMESTAMP WITH TIME ZONE using the supplied zone. - AT LOCAL converts TIMESTAMP WITH TIME ZONE to the session time zone.2. Remote execution (T-SQL) - EXECUTE ( ... ) AT runs a command on a remote data source.Because AT is overloaded, its exact behavior depends on the clause in which it is used and the SQL dialect. Always verify your database documentation for supported forms and limitations.

SQL AT Syntax

-- Standard SQL time-zone syntax
<timestamp_value> AT TIME ZONE <zone_string>

<timestamp_with_tz_value> AT LOCAL

-- SQL Server remote execution
EXEC ( <string_command> ) AT <linked_server_name>;

SQL AT Parameters

TIMESTAMP or DATETIME - The date-time value to convert.
IDENTIFIER - Registered linked server. If using time-zone forms only the first two parameters apply; for EXECUTE AT only the last parameter applies.

Example Queries Using SQL AT

-- PostgreSQL: convert naive timestamp to EST
SELECT TIMESTAMP '2024-05-18 10:00' AT TIME ZONE 'America/New_York';

-- Oracle: convert session timestamp with time zone to local zone
SELECT CURRENT_TIMESTAMP AT LOCAL FROM dual;

-- SQL Server: get current time in UTC
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'UTC';

-- SQL Server: run a query on a linked server
EXEC ('SELECT TOP 10 * FROM Sales.Orders') AT SalesServerLinked;

Expected Output Using SQL AT

Each query returns a TIMESTAMP WITH TIME ZONE value adjusted to the specified zone, or runs the remote command and returns its result set

Use Cases with SQL AT

Converting application timestamps from UTC to user-local time.
Storing all times in UTC then displaying them in regional dashboards.
Executing administrative or analytics queries on a linked server without opening a second connection.

Common Mistakes with SQL AT

Supplying an unsupported or misspelled time-zone identifier.
Forgetting to cast string literals to TIMESTAMP before applying AT TIME ZONE.
Attempting to use AT TIME ZONE in MySQL or SQLite, which do not support it.
Using EXECUTE AT without configuring the linked server first.

First Introduced In

SQL:1999 (AT TIME ZONE / AT LOCAL)

Frequently Asked Questions

How do I convert a timestamp to a specific time zone?

Use AT TIME ZONE:```SELECT TIMESTAMP '2024-05-18 10:00' AT TIME ZONE 'America/New_York';```

Does PostgreSQL support AT LOCAL?

Yes. PostgreSQL lets you write `SELECT CURRENT_TIMESTAMP AT LOCAL;` to convert the current timestamp with time zone into the session's zone.

Can I use AT to run queries on another server?

Only in SQL Server. Wrap the command string in EXEC and append AT .

Why does my query fail with "invalid time zone"?

The zone string must match the database's list. Check spelling and availability, and ensure your database has the latest tzdata files.