The activity feed shows raw timestamps where a reader expects a quick sense of recency. Time ago labels turn a UTC timestamp into a readable phrase. Capture one as-of instant, handle future values, and keep the original timestamp available for exact details.
Choose the Clock and Boundary Rules
Store event instants in UTC and compare them with SYSUTCDATETIME. Capture that value once for the statement or request. DATEDIFF_BIG in seconds avoids int overflow across long intervals, while the display still rounds to its chosen units. I handle future timestamps explicitly because clock skew should not become a negative minutes-ago phrase. Which calendar defines yesterday for the screen? The example uses UTC and says so. A local-user display needs an intentional time-zone conversion. The first block builds an event five minutes before the as-of instant, and on my run it reported a gap of 300 seconds. The label is friendly shorthand. It should not start an argument with the exact timestamp shown beside it.
DECLARE @asof datetime2=SYSUTCDATETIME(),@event datetime2=DATEADD(minute,-5,SYSUTCDATETIME());
SELECT @event AS EventUtc,@asof AS AsOfUtc,DATEDIFF_BIG(second,@event,@asof) AS GapSeconds;Centralize the Label in an Inline Function
The inline function accepts both event and as-of values. That makes the test reproducible and keeps one clock value across a result set. CASE chooses the label, with singular forms handled separately. Called with the same five-minute gap, it returned “5 minutes ago” on my SQL Server 2025 test instance. After a week, the sample returns an ISO-style date rather than an ever-growing relative phrase. NULL remains NULL, while a future value becomes a clear future label. I retain the typed timestamp in the caller’s result. Sorting and filtering should use that timestamp, not the display string. Time ago labels should make a feed easier to scan without changing the underlying temporal data.
CREATE FUNCTION dbo.TimeAgoLabel(@event datetime2,@asof datetime2) RETURNS TABLE AS RETURN
WITH g AS(SELECT DATEDIFF_BIG(second,@event,@asof) AS s)
SELECT CASE WHEN @event IS NULL OR @asof IS NULL THEN NULL
WHEN s<0 THEN N'in the future'
WHEN s<60 THEN N'just now'
WHEN s<3600 THEN CONCAT(s/60,N' minute',CASE WHEN s/60=1 THEN N'' ELSE N's' END,N' ago')
WHEN s<86400 THEN CONCAT(s/3600,N' hour',CASE WHEN s/3600=1 THEN N'' ELSE N's' END,N' ago')
WHEN DATEDIFF(day,@event,@asof)=1 THEN N'yesterday'
WHEN s<604800 THEN CONCAT(s/86400,N' day',CASE WHEN s/86400=1 THEN N'' ELSE N's' END,N' ago')
ELSE CONVERT(nvarchar(10),@event,23) END AS TimeLabel FROM g;
GO
DECLARE @asof datetime2=SYSUTCDATETIME();
SELECT TimeLabel FROM dbo.TimeAgoLabel(DATEADD(minute,-5,@asof),@asof);Testing Time Ago Labels at Each Threshold
Use fixed endpoints just below, at, and above each threshold. With a fixed as-of value, 59 seconds returned just now and 60 seconds returned 1 minute ago. 3,599 seconds gave 59 minutes ago, and 3,600 seconds gave 1 hour ago. A future value returned in the future, NULL stayed NULL, and seven days returned the plain date. That same test caught a bug in my first draft. A 26-hour gap that crossed two calendar dates came back as “1 days ago,” so the day branch now handles the singular too. The example’s yesterday rule applies after the elapsed-hour branch, so recent events can remain hours ago even across a UTC date change. If the product requires every previous-calendar-day event to read yesterday, move that branch earlier and test the changed precedence. A CASE expression has an ordered contract. The first matching branch decides the phrase, so branch order is part of the user experience.
Time Ago Labels and the Meaning of Yesterday
A label such as yesterday needs a calendar and a time zone. The sample uses the UTC calendar. With an as-of time of 00:30 UTC, an event two hours earlier fell on the previous UTC date. The function still returned 2 hours ago, because the hour branch comes first. For a reader in another zone, even the UTC date is the wrong calendar. Convert both instants to the reader’s zone with AT TIME ZONE before comparing dates, or leave yesterday to the application. Pick one owner for that rule and write it down.
One Event, Three Calendars
One pair of instants shows why the zone matters. I took an event at 23:30 UTC and an as-of time one hour later, at 00:30 UTC the next day. DATEDIFF by day in UTC returned 1, so a pure calendar rule would call the event yesterday. Converted with AT TIME ZONE to India Standard Time, both instants fell on the same local date, 05:00 and 06:00. Converted to Pacific Standard Time, they also fell on one date, the previous evening. So two readers on two continents both saw the event as today, while the UTC calendar said yesterday. A one-hour gap should read as 1 hour ago anyway, which is why the hour branch comes first.
Keep Display Work Out of Large Aggregations
Generate the labels only for rows actually returned to the screen or report. Do not compute friendly text for every historical row before filtering and paging. Keep WHERE and ORDER BY on the original datetime2 column so indexes remain useful. The inline function makes reuse convenient, but it does not turn display work into a search key. For frequently refreshed feeds, a cached label becomes stale as time passes. Refresh according to the screen’s requirements, or compute in the application using the same documented rules. The timestamp is durable. The relative phrase has an expiration of its own.
Use Exact Time for Diagnosis
Logs, audits, and operational investigations need the original UTC instant and the relevant time-zone context. A screenshot saying just now cannot identify an event precisely. Provide an exact timestamp through the approved detail view or export. Keep server and application clocks synchronized, and review ingestion delays when event time differs from receipt time. A friendly phrase is useful for reading, while the original value remains the authority for sorting, comparison, and evidence. The small display function works well when those roles stay clear. The reader gets quick context without losing access to the exact instant.
One Clock Value for All Time Ago Labels
Capture the as-of instant once per page and pass it to every call. If the page runs several queries and each reads the clock, two events from the same second can land on different sides of a threshold. The feed then shows 59 minutes ago next to 1 hour ago for events that happened together. Keep the function as the single definition, and let the web page and the email digest both call it. Save the threshold test above as a script. Run it whenever someone edits the CASE, because branch order is where this function breaks.
Related reading on this blog: Query Without Join Showing Query Plan With Join and MySQL: When to Use TIMESTAMP or DATETIME: Difference Between TIMESTAMP or DATETIME.
A relative label is not a replacement timestamp, it is a display derived from one.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
