Date and Time Types in PostgreSQL in SQL
PostgreSQL provides four primary types for representing points in time and durations: `DATE`, `TIME`, `TIMESTAMP`, and `TIMESTAMPTZ`. The choice between them determines what gets stored, how PostgreSQL interprets the value, and how comparisons between values behave.
Before this Literal Values, Data Types, and Type Casting
Builds toward Date Truncation and Extraction, Date Arithmetic and Intervals
PostgreSQL has four date/time types. The most important choice is between TIMESTAMP and TIMESTAMPTZ — and it determines whether your timestamps know what time zone they were recorded in.
Both store a date and a time of day. The difference is what the value means. TIMESTAMP records a clock reading with no time zone attached. PostgreSQL stores exactly what you give it and makes no adjustment. TIMESTAMPTZ records a moment in absolute time. When you write a TIMESTAMPTZ value, PostgreSQL converts it from your session's time zone to UTC for storage, then converts it back to your session's time zone when you read it.
You write date/time values as string literals and tell PostgreSQL which type you need with an explicit cast:
The third literal includes a time zone offset (+05:30). PostgreSQL converts it to UTC at parse time and stores the absolute moment.
Why does the distinction matter? Consider orders arriving from Tokyo at 9pm local time and from New York at 9pm local time. Those are different moments — about 14 hours apart. Stored as TIMESTAMPTZ, the UTC conversion captures that difference and the timestamps are directly comparable. Stored as TIMESTAMP, the time zone is discarded. Both orders record as 21:00:00, indistinguishable from each other and wrong.
For event data collected across multiple time zones, TIMESTAMPTZ is almost always the right choice. The time zone is captured at write time. It cannot be recovered afterward — once data is stored as TIMESTAMP, the zone information is gone.
DATE stores only a calendar date — year, month, and day, with no time of day and no time zone. A DATE value of 2024-03-15 is the same everywhere, regardless of where the server is or what session is running. Use DATE for values that represent a calendar concept rather than a specific moment: a birth date, a contract effective date, a reporting period.
TIME stores a time of day without a date. It appears occasionally in schemas but rarely in analytical work — a time without a date is seldom meaningful on its own.
The one thing that trips people up: comparing a TIMESTAMP column to a TIMESTAMPTZ value.
When one side of a comparison is TIMESTAMP and the other is TIMESTAMPTZ, PostgreSQL casts the TIMESTAMP to TIMESTAMPTZ using the session's current time zone. The same query can return different rows depending on the session's time zone setting. Keep types consistent on both sides of a comparison. If your table stores TIMESTAMP, compare it to a TIMESTAMP literal:
WHERE created_at = '2024-03-15 09:00:00'::timestampYour orders table stores created_at as TIMESTAMP (no time zone). Orders arrive from users in multiple time zones. What problem does this create?
9 Date and Time Types in PostgreSQL practice problems
Write a query to return the string `'1992-04-18'` cast as a calendar date.
Write a query to return the string `'2024-06-15 14:30:00'` cast as a timezone-naive timestamp.
Write a query to return the string `'2024-06-15 14:30:00+00'` cast as a timezone-aware timestamp.
Write a query to return three columns in a single row: the string `'2024-06-15'` cast as a calendar date; `'2024-06-15 09:00:00'` cast as a timezone-naive timestamp; and `'2024-06-15 09:00:00+05:30'` cast as a timezone-aware timestamp.
Write a query to return the string `'2024-06-15 23:45:00'` first cast as a timezone-naive timestamp and then cast as a calendar date.
Write a query to return the string `'2024-06-15 09:30:00+05:30'` cast as a timezone-aware timestamp.
Write a query to return both literals cast as timezone-aware timestamps in a single row.
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.
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.
These problems are part of the Date and Time Types in PostgreSQL lesson in SQLMaxx, with instant grading and a worked solution on each.
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.
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.
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.
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.
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 freeNo account, no credit card. Start solving in under a minute.