Date and Time Types in PostgreSQL in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Literal Values, Data Types, and Type Casting
Builds toward Date Truncation and Extraction, Date Arithmetic and Intervals
What are Date and Time Types in SQL?
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.
How do you write a date or timestamp literal in PostgreSQL?
You write date/time values as string literals and tell PostgreSQL which type you need with an explicit cast:
SELECT '2024-03-15'::date AS just_a_date, '2024-03-15 09:00:00'::timestamp AS local_time, '2024-03-15 09:00:00+05:30'::timestamptz AS absolute_moment
The third literal includes a time zone offset (+05:30). PostgreSQL converts it to UTC at parse time and stores the absolute moment.
Should you use TIMESTAMP or TIMESTAMPTZ?
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.
When should you use DATE instead of a timestamp?
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.
What happens when you compare TIMESTAMP to TIMESTAMPTZ?
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?
Practice Date and Time Types in SQL
Brightlane's data engineering team is verifying that plain date strings parse cleanly to the calendar-date type during import.
Write a query to return the string '1992-04-18' cast as a calendar date.
Output:
- A single row with one column,
birth_date, typed as a date.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 Date and Time Types 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.
Start learning to practice all 9 Date and Time Types problems, with instant grading and mastery tracking.
Deeper guides on Date and Time Types
- why subtracting two dates gives a plain integer
Subtract the dates, and read why PostgreSQL answers DATEDIFF with a missing column. Hours, months and years too.
- why adding an interval turns a date into a timestamp
Add an integer or an INTERVAL, and see which one leaves you holding a timestamp.
- getting the current date with now() and current_date
now() and current_date, why now() stands still for a whole transaction, and the filter that drops today’s rows.
- turning a text value into a date
::date for ISO strings, to_date() for everything else, and the day-first date Postgres reads as a month.
Common questions about Date and Time Types
What is the difference between DATE and TIMESTAMP in PostgreSQL?
DATE stores a calendar day with no time of day. TIMESTAMP stores a day and a time. When you compare the two, PostgreSQL treats the date as midnight at the start of that day, so a DATE equals a TIMESTAMP only when the timestamp is exactly 00:00.
Does TIMESTAMPTZ store the time zone?
No, despite the name. TIMESTAMPTZ converts whatever you give it to a single instant in UTC and stores that. The offset you typed is not kept. On the way out, PostgreSQL converts the instant into the session time zone, so the same stored value displays as 12:00 in one session and 07:00 in another.
What does the TIME type store?
A time of day with no date attached, such as 09:30. It suits things that repeat, like opening hours. It cannot represent a specific moment, because without a date there is no way to say which 09:30 it was.
What is an INTERVAL in PostgreSQL?
A length of time rather than a point in time: three days, two hours, one month. Subtracting one timestamp from another produces an INTERVAL, and adding an INTERVAL to a timestamp moves it forward.
