Brightlane's engineering team is documenting how the TIMESTAMP type preserves microsecond precision and how casting that value to DATE discards the time component entirely.
Write a query to return two columns in a single row: the string '2024-06-15 09:00:00.987654' cast as a timezone-naive timestamp (preserving the microseconds); and the same string cast first to a timezone-naive timestamp and then to a calendar date.
Output:
- A single row with columns
with_microsecondsanddate_only.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
'2024-06-15 09:00:00.987654'::TIMESTAMP AS with_microseconds,
'2024-06-15 09:00:00.987654'::TIMESTAMP::date AS date_onlyThe shape
Two casts of the same literal, side by side, show how TIMESTAMP and DATE differ on the same input. The first column keeps the microsecond-precision time component; the second drops the time entirely and keeps only the calendar day. The .987654 suffix in the literal is the load-bearing detail — it survives one cast and is gone after the other.
Clause by clause
SELECT '2024-06-15 09:00:00.987654'::timestamp AS with_microsecondsresolves the literal as aTIMESTAMP. PostgreSQL parses the fractional seconds as microseconds and stores them in the value's time component. The output displays the full microsecond reading.'2024-06-15 09:00:00.987654'::timestamp::date AS date_onlychains two casts on the same literal. The first::timestamplands it as aTIMESTAMP(microseconds preserved). The second::datestrips the entire time-of-day, including the microseconds, returning2024-06-15.- There is no
FROMbecause no table is being read.
Why the chained ::timestamp::date and not just ::date directly
'2024-06-15 09:00:00.987654'::date would also return 2024-06-15, because PostgreSQL can read the date portion of the string and discard the rest when casting directly to DATE. The chained form documents the two-stage reduction explicitly: first establish the full timestamp with all its precision, then declare that the consumer only needs the calendar day. The grader checks the destination type, not the intermediate steps, so either form passes — but the chained spelling makes the information loss visible in the SQL itself.
The trap
DATE does not store a zero time-of-day. It does not store any time-of-day at all. A reader who treats the date_only column as a TIMESTAMP set to midnight will eventually hit a comparison against a real TIMESTAMP value and discover that PostgreSQL implicitly casts the DATE up to a TIMESTAMP using 00:00:00.000000. The cast works, but it does so by inventing precision that the stored value never had. The microsecond reading from the original literal is gone the moment the ::date cast runs, and no downstream operation can recover it. When the time component matters, the column has to stay as TIMESTAMP; casting to DATE is a one-way operation.
You practiced two casts of the same literal — TIMESTAMP preserves microsecond-precision time, DATE keeps only the calendar component.
