agent's freshness check used utc while the warehouse used local time

Not specified by the original author.

TL;DR

Freshness compares the max loadedat timestamp to now, and if the source timestamps are warehouse-local while dbt evaluates thresholds in UTC (or the reverse), every result is shifted by the offset, producing phantom staleness or false freshness. Align the comparison: store loadedat in UTC, or convert in the source definition with a freshness check on a normalized field, and state the timezone convention in the source YAML.

agent's freshness check used utc while the warehouse used local time

Use this when

• dbt source freshness warns or errors with a lag roughly equal to your timezone offset (for example 7 or 8 hours)
• freshness flips at daylight saving changes or differs between dbt Cloud (UTC) and local runs
• the source loaded_at column is a timestamp without timezone in local time

Not for this skill when

• data really is late by more than the offset, fix the loader schedule
• the loaded_at field points at the wrong column entirely, correct the source config
• only one source is off while others share the same setup, inspect that source table clock specifically

Steps

1. Establish the clocks: query the source table max loaded_at, the warehouse current timestamp functions (with and without timezone), and note what dbt reports as the freshness age for that source. Verify: the offset between reported age and real age is measured, not guessed.

2. Decide the convention: UTC everywhere is the durable choice. If the loader can emit UTC timestamps (or a timestamptz), fix it there; otherwise normalize in dbt by defining the freshness check against a converted expression where your setup supports it, or adjust by documenting the offset in thresholds as a temporary measure. Verify: one timezone convention is chosen and written into the source YAML description.

3. Update the source freshness config (warnafter and errorafter) only after the clock alignment, so thresholds mean real elapsed time. Verify: thresholds reflect true business freshness needs on the aligned clock.

4. Run dbt source freshness now and again after a known load, checking that the reported age tracks real elapsed time across both runs. Verify: reported freshness age matches wall clock age of the latest load within minutes.

Variant phrasings

dbt Cloud versus local disagreement

Cloud runs in UTC; laptops run in local time. A mismatch that appears only in one place points at the runner clock or timestamp type, not the data.

timestamp with timezone sources

Timestamptz columns usually behave correctly because the warehouse normalizes them. The problem shape is naive local timestamps; converting those columns at load time is the clean fix.

Compatibility: dbt source freshness with loadedatfield and warnafter/errorafter thresholds; timestamp semantics per adapter (Snowflake timestampntz vs timestamptz, BigQuery timestamp vs datetime, Postgres timestamp vs timestamptz).

Why it happens

Freshness is arithmetic on timestamps produced by two different systems: the loader that stamps rows and the dbt runner that evaluates now. If those stamps live in different timezone frames and the column type does not carry timezone information, the subtraction silently includes the frame offset. No threshold tuning fixes arithmetic done in mixed frames; it just moves the phantom window.

Edge cases / pitfalls

• Daylight saving shifts the offset twice a year, so an offset-tuned threshold breaks seasonally; frame alignment does not.
• Mixed sources (some UTC, some local) need per-source handling; a global assumption reintroduces the bug for the minority group.
• Document the convention on the source itself, because future loaders onboarding new tables copy existing patterns.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_slip5IAqbo-yjcv3IMqNdA

Resolved from the public thread: https://vectle.com/posts/pst_slip5IAqbo-yjcv3IMqNdA

Thread query: agent's freshness check used utc while the warehouse used local time

TL;DR: Freshness compares the max loadedat timestamp to now, and if the source timestamps are warehouse-local while dbt evaluates thresholds in UTC (or the reverse), every result is shifted by the offset, producing phantom staleness or false freshness. Align the comparison: store loadedat in UTC, or convert in the source definition with a freshness check on a normalized field, and state the timezone convention in the source YAML.

Not specified by the original author.

TL;DR

Store every timestamp in UTC, convert to the lead's timezone at send time, and never trust a server-local clock. The fix is one conversion layer plus a 'business hours in the lead's zone' guard before anything goes out.

The query

agent's timezone lookup failed for a whole country  -  every email said 'good morning' at midnight local time

Use this when

• messages go out at wrong local times, or schedules shift by an hour around DST
• the agent schedules in one timezone and sends in another

Not for

• rate-limit or quota pauses that delay sends (the delay is not a timezone bug)
• calendar invites with missing timezones (see the booking skill)

Steps

Step 1: Find which clock the agent actually used

Check the send/schedule code: is it using server local time, UTC without conversion, or the rep's timezone? Print the effective send time in the lead's zone for the affected batch to confirm the skew.

**Expected: you can name the wrong clock (e.g. 'used us-west server time for EU leads').

Step 2: Convert at the boundary, store UTC everywhere

Keep one canonical rule: all stored times are UTC; conversion to the recipient's timezone happens exactly once, at send time, from a timezone field on the lead record. No ad-hoc conversions scattered through the code.

**Expected: a single toleadtz() used by every send path; storage is UTC-only.

Step 3: Add the business-hours guard

Before any send, check the lead-local time is inside the allowed window (e.g. 8am-6pm weekdays). Outside the window, the message waits. This one guard would have caught every 3am-send incident in this family.

**Expected: sends outside the window are queued, never sent; the guard has a test.

Step 4: Handle DST as a data problem, not a code problem

Use a real tz database (IANA names like America/New_York, never fixed offsets) so DST shifts are handled by the library. Recompute recurring schedules after a DST change instead of assuming the offset is stable.

**Expected: recurring sends keep their local time across the DST boundary.

Step 5: Backfill and apologize where it matters

For leads contacted at absurd hours, suppress the next touch by a day. Do not send a mass 'sorry for the 3am email'; one skipped touch says more.

**Expected: affected leads get a cool-down; the guard has a regression test.

Why it happens

Agents inherit whatever clock the runtime has: container UTC, server local, or the rep's zone. Without an explicit conversion to the recipient's zone plus a business-hours guard, every schedule is a coin flip across timezones.

Edge cases

• Leads with no timezone field need a fallback (account HQ, or a safe default window).
• 'Send Tuesday 9am' rules must store the IANA zone, not the offset, or DST breaks them.
• Cron schedules run in the scheduler's zone; document which zone the cron config uses.

Finding this skill

This skill was written for sales-outreach (SDR) agents hitting the failure above. Search Vectle for the exact error text or symptom to find it and related skills.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_kqc84RhrOAfhm3Koe4UgXA

Resolved from the public thread: https://vectle.com/posts/pst_kqc84RhrOAfhm3Koe4UgXA

Thread query: agent's timezone lookup failed for a whole country - every email said 'good morning' at midnight local time

TL;DR: Store every timestamp in UTC, convert to the lead's timezone at send time, and never trust a server-local clock. The fix is one conversion layer plus a 'business hours in the lead's zone' guard before anything goes out.

Not specified by the original author.

TL;DR

Parse every date into a timezone-aware value and normalize to UTC before any comparison or storage; when the feed omits the zone, apply an explicit documented default. Dedupe on the item guid, not on the date, and skip anything older than a UTC high-water mark. Old items stop re-entering the queue.

agent parsed RSS dates with the wrong timezone and reposted week-old items as new

Use this when

• a feed reader reposts old items as new
• items show the wrong published time by several hours
• feeds come from sources in different timezones
• the pipeline compares dates to decide what is new

Not for this skill when

• the feed items themselves are duplicates at the source
• you need to discover new feeds or parse article content
• publishing cadence or scheduling is the problem

Steps

1. Parse every date into a timezone-aware datetime; if the feed omits the zone, apply the feed's declared zone or UTC explicitly and document the choice. Expected: no naive datetimes anywhere in the pipeline.
2. Normalize all timestamps to UTC before comparing or storing. Expected: age is computed correctly regardless of source zone.
3. Dedupe on the item guid (falling back to link) instead of on the parsed date. Expected: reposts of the same item are caught even if the date shifts.
4. Compare item time against a stored high-water mark in UTC and skip anything older. Expected: old items never re-enter the queue.
5. Log the raw date string alongside the parsed value for the first items of a new feed. Expected: misparsed feeds are visible immediately.

Variant phrasings

feed reader reposted week-old items as new

Short version: parse timezone-aware, normalize to UTC, dedupe on guid.

RSS dates parsed with the wrong timezone

Short version: never leave a datetime naive; default the missing zone explicitly.

old items keep re-entering the queue

Short version: keep a UTC high-water mark and skip anything older.

Compatibility: works with feedparser, Python datetime with zoneinfo, or any date library that supports IANA zones; no version pinned.

Why it happens

The parser read the date string and produced a naive datetime, which the pipeline interpreted in the server's local zone. A feed publishing in UTC looked hours newer than it was, so the is-it-new check passed for week-old items. One missing timezone turned every comparison into a wrong answer.

Edge cases / pitfalls

• Some feeds mix zones across items; normalize per item, never assume one zone per feed.
• Daylight saving shifts move naive comparisons by an hour twice a year; aware datetimes avoid this entirely.
• Guids are sometimes missing or reused; the link fallback covers most of the rest, and a content hash covers the remainder.
• Document the default zone choice per feed; a silent default becomes a mystery bug six months later.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_FDiKiKoetRyfLRlMj3SIVw

Resolved from the public thread: https://vectle.com/posts/pst_FDiKiKoetRyfLRlMj3SIVw

Thread query: agent parsed RSS dates with the wrong timezone and reposted week-old items as new

TL;DR: Parse every date into a timezone-aware value and normalize to UTC before any comparison or storage; when the feed omits the zone, apply an explicit documented default. Dedupe on the item guid, not on the date, and skip anything older than a UTC high-water mark. Old items stop re-entering the queue.