Recognises a business date stored with a time of day
Two mirror-image defects. An event timestamp with no offset is ambiguous twice a year and incomparable across a bank that books in more than one zone. A business date — value date, booking date, maturity date, due date — stored as a timestamp invites a time component nobody means and makes date equality quietly fail.
Can AI do this?
AI does this partly Some of the tested AI models master this; others do not show it. 3 of 10 models master it.
| Model | Tier | Probability of mastery | Status |
|---|---|---|---|
| gemini-4-argon | Flagship | 98% | Mastered |
| gemini-3.1-pro-preview | Flagship | 14% | Not shown |
| gpt-6-astra | Flagship | 20% | Not shown |
| claude-fable-5-1 | Flagship | 98% | Mastered |
| gemini-3.8-flash | Mid tier | 14% | Not shown |
| gpt-6.1-sol | Mid tier | 18% | Not shown |
| claude-sonnet-5-5 | Mid tier | 99% | Mastered |
| gemini-3.5-flash-lite | Low cost | 15% | Not shown |
| gpt-6-luna | Low cost | 19% | Not shown |
| claude-haiku-5-5 | Low cost | 14% | Not shown |
Mastered means a probability of at least 90% that the model has this knobit, estimated from its graded answers. “Not shown” means the answers do not settle it either way. How the models are tested.
What it looks like in practice
Expected result: The column and the date type it needs
How it is assessed: Flags the deficient case and passes the acceptable one. Deficient: created_at TIMESTAMP with no zone on dim_account, written by jobs in two regions. And payment_value_date as a TIMESTAMP, so = DATE '2026-03-31' matches none of the rows loaded at 23:30 Acceptable: TIMESTAMP WITH TIME ZONE for event times, or UTC with the convention stated in the column comment and nowhere else; DATE for payment_value_date, loan_maturity_date and valid_from_date/valid_to_date, as the sample model has them
Also known as: Point in time without a zone, business date with a clock (generated DDL)
Related knobits
Builds on
- K-617 Recognises a physical domain differing from the logical domain
- K-854 Explains time zones in timestamps and business dates
All 33 knobits of Generate and review DDL.
Source: Review checklist 08-ddl, row 18; ISO 8601; ISO 9075