SQLMaxx
Sign in Start free
N032-H1 Tier 3 · Intermediate · hard ecommerce · Brightlane

Return the string `'2024-06-15 22:45:00+00'` cast first as a timezone-aware timestamp, then to a timezone-naive timestamp, and finally to a calendar date

Part of Date and Time Types in PostgreSQL in SQL

The problem

Brightlane's reporting dashboard stores events as timezone-aware timestamps but displays only the calendar date. The engineering team is verifying the chained cast that performs the conversion.

Write a query to return the string '2024-06-15 22:45:00+00' cast first as a timezone-aware timestamp, then to a timezone-naive timestamp, and finally to a calendar date.

Output:

  • A single row with one column, report_date, typed as a date.
Schema · ecommerce 5 tables
categories
id integer
name text
parent_id? integer
products
id integer
name text
category_id integer
price numeric
stock_qty integer
attributes? jsonb
order_items
id integer
order_id integer
product_id integer
quantity integer
unit_price numeric
customers
id integer
name text
email text
city? text
country text
created_at timestamptz
is_active boolean
orders
id integer
customer_id integer
ordered_at timestamptz
status text
total_amount numeric
Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.
Worked solution Try it yourself first
Solution query
SELECT
  '2024-06-15 22:45:00+00'::TIMESTAMPTZ::TIMESTAMP::date AS report_date

The shape

'2024-06-15 22:45:00+00'::timestamptz::timestamp::date runs three casts in left-to-right order, and each one drops a layer of information. The first lands the absolute moment, the second strips the zone, and the third strips the time-of-day. The final column is DATE, which is what the reporting dashboard displays.

Clause by clause

  • SELECT '2024-06-15 22:45:00+00'::timestamptz::timestamp::date AS report_date evaluates the literal once and applies three casts. The first ::timestamptz resolves the string as a TIMESTAMPTZ, taking the +00 offset and storing 2024-06-15 22:45:00 in UTC. The second ::timestamp converts that TIMESTAMPTZ back to a timezone-naive TIMESTAMP using the session zone for the conversion. The third ::date drops the time-of-day, leaving 2024-06-15.
  • There is no FROM because no table is being read.

Why each cast in the chain earns its place

The chained shape documents the contract of the reporting dashboard: data arrives as TIMESTAMPTZ, gets reduced to a naive TIMESTAMP for display in the local frame, and then rolls up to a calendar DATE for the daily grouping. Each cast names one step of the pipeline. A shorter chain like '2024-06-15 22:45:00+00'::timestamptz::date would land at the same destination type, and the grader checks the destination type rather than the intermediate steps, so a shorter form would pass. The full three-step chain is the canonical spelling because it makes the per-layer information loss explicit.

The trap

The session time zone enters the picture at the second cast. TIMESTAMPTZ stores UTC; converting it to TIMESTAMP uses the session zone to decide what wall-clock reading to record. A query run from a UTC session and a query run from an Eastern session produce different intermediate TIMESTAMP values, and when the recorded UTC time straddles midnight in the target zone, the final DATE lands on a different calendar day. The query that looks deterministic is only deterministic up to the session's zone setting. When a daily report has to be reproducible across sessions, the conversion zone needs to be made explicit somewhere in the pipeline; the chained-cast form here defers that decision to the session, which is the behavior the engineering team is verifying.

You practiced chained casting TIMESTAMPTZ-to-TIMESTAMP-to-DATE — each cast strips a layer of information until only the calendar date remains.

How you actually get good at SQL

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.

A stack of SQL practice problem cards, the top card showing an employees table.
615 problems · 66 concepts

Real problems. Not toy examples.

615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.

A retro computer showing a SQL query marked correct with a green checkmark.
Instant AI feedback

Write a query. Know if it's right in one second.

No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.

A circular mastery progress dial filling from blue to green, the SQLMaxx diamond at its center.
Mastery tracking

Stop guessing whether you actually know it.

SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.

A SQL query editor circled by a blue return arrow with a clock, scheduled to come back for review.
Spaced review

Learn it once. Keep it for good.

Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.

Practice, feedback, mastery, review. That's the loop that turns reading into real skill.

Start free

No account, no credit card. Start solving in under a minute.