Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

The original question asks how to extract the yyyy/mm/dd date part from a PostgreSQL timestamp. The accepted answer shows a to_char/to_date round-trip:

This write-up is grounded in the original Stack Overflow question (434 upvotes, 968,809 views).

SELECT to_char(now(), 'YYYY/MM/DD');

SELECT to_date(to_char(now(), 'YYYY/MM/DD'), 'YYYY/MM/DD');

That works, but for a native DATE value you do not need the text round-trip. Use the cast operator :::

SELECT now()::date;

A single colon is not valid PostgreSQL. Write timestamp::date, not timestamp:date.

• Accepted answer: to_char(now(), 'YYYY/MM/DD') formats; to_date(to_char(...), 'YYYY/MM/DD') parses the text back to date.
• Direct fix: timestamp::date returns a native DATE without text conversion.
• Formatting: Use to_char(timestamp, 'YYYY/MM/DD') only when you need text output.
• Verification: Check the actual type with pg_typeof() and the column type with \d.

The cast returns the full date, not just the year

timestamp::date and date(timestamp) both return PostgreSQL's native DATE type. That type stores year, month, and day. The default text output is YYYY-MM-DD under the standard DateStyle setting. If another client displays only 2011, the client is not showing the full value. Verify in psql:

SELECT ts, ts::date AS event_date
FROM events
WHERE id = 1;

         ts          | event_date
---------------------+------------
 2011-05-26 09:00:00 | 2011-05-26

Use SELECT pg_typeof(ts::date); to confirm the result is date.

The accepted answer and the direct cast

The accepted answer on the original thread uses a text format/parse sequence:

SELECT to_char(now(), 'YYYY/MM/DD');

SELECT to_date(to_char(now(), 'YYYY/MM/DD'), 'YYYY/MM/DD');

The first statement returns text in the requested yyyy/mm/dd format. The second parses that text back into a date value. This works, but it is an unnecessary round-trip when you only need a native DATE.

Use the direct cast for a native DATE

SELECT ts::date AS event_date
FROM events
WHERE id = 1;

You can also use the SQL-standard function syntax:

SELECT date(ts) AS event_date
FROM events
WHERE id = 1;

Both return a DATE type, ready for insertion into a DATE column. They truncate the time component without rounding.

Use to_char only for display

If you need the exact yyyy/mm/dd string, use to_char directly on the timestamp:

SELECT to_char(ts, 'YYYY/MM/DD') AS formatted_date
FROM events
WHERE id = 1;

Keep the column as DATE for storage and sorting; format only when you display the result.

date_trunc is not a date extraction

date_trunc('day', ts) returns a timestamp truncated to midnight, not a DATE. It preserves the timestamp type and, for timestamptz, the time zone. Use it only when the target column expects a timestamp and you need to keep the time component at zero. For a plain DATE, use ::date.

For the full syntax and behavior, see the PostgreSQL date/time functions documentation.

Time-zone note: ::date on a timestamptz returns the date in the session’s time zone. If the server is UTC but the data represents New York events, the date can shift by one day. Set the time zone first: SET TIME ZONE 'America/New_York'; then run the cast.

Verify the extracted date

After applying the fix, confirmation is a three-step check: type, value, and insertion.

1. Confirm the column type

Use the \d command in psql to inspect the table. If you’re coming from a MySQL background, you may be used to DESCRIBE. PostgreSQL uses \d instead; we have a full guide on the \d equivalent if you need it.

psql -d yourdb -c "\d events"

 Column     | Type              | Modifiers
------------+-------------------+-----------
 id         | integer           | not null
 ts         | timestamp         |
 event_date | date              |

Look for the date type on the new column.

2. Check the actual value

Query the raw column and force the output with explicit formatting for a sanity check:

SELECT
  ts,
  event_date,
  to_char(event_date, 'YYYY/MM/DD') AS formatted_date
FROM events;

You should see a line like 2011-05-26 | 2011/05/26, proving both the type and the correct day.

3. Insert the value into a DATE column

Create a tiny scratch table to simulate the target environment:

CREATE TEMP TABLE test_insert (testd DATE);
INSERT INTO test_insert (testd)
  SELECT ts::date FROM events WHERE id = 1;
SELECT * FROM test_insert;

   testd
------------
 2011-05-26

If the insert succeeds, ::date produced a proper DATE value. When you’re done, you can clean up safely — the approach for dropping test tables without losing production data is covered in How to Drop All Tables in PostgreSQL Safely.

Three common pitfalls during verification

1. Client shows only 2011 – Verify in psql or run SELECT event_date::text; to see the full string. The cast itself does not truncate the year.
2. Date shift after cast – Verify the session time zone (SHOW timezone;). If it differs from the data’s origin, set it explicitly as shown above.
3. “Cannot insert NULL” errors – If source timestamps are nullable, filter them: WHERE ts IS NOT NULL.

FAQ

Why can’t I just do to_date(to_char(ts, 'YYYY/MM/DD'), 'YYYY/MM/DD')?

That text round-trip converts the timestamp to text, parses the text back into a date, and can never produce a different result than ts::date — but it costs extra CPU, breaks any plan-time optimisation, and prevents the use of indexes on the expression. Use the direct cast instead.

For example, EXPLAIN SELECT * FROM events WHERE to_date(to_char(event_ts, 'YYYY/MM/DD'), 'YYYY/MM/DD') = '2025-01-15'; shows a sequential scan, while SELECT * FROM events WHERE event_ts::date = '2025-01-15'; can use an index on the expression (event_ts::date). The extra text parsing and function calls also add measurable CPU overhead on large tables.

Does ::date work in all PostgreSQL versions?

Yes, the cast to date from timestamp has been available since at least PostgreSQL 9.0. All currently supported versions (14, 15, 16, 17, 18) include it. If you are unsure which version you’re running, check this short guide on finding your PostgreSQL version.

Run SELECT version(); in psql — you’ll see output like PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.2.1, 64-bit. The ::date cast works identically in every major release back to 9.0, so there’s no compatibility risk.

How do I output the date exactly as yyyy/mm/dd without losing the DATE type?

The output format is a presentation layer concern. Keep the column as DATE and use to_char(event_date, 'YYYY/MM/DD') only when you need to display it to a user or generate a report. That way you retain the native type for ordering, indexing, and date arithmetic.

For instance, define a table with a proper DATE column:

CREATE TABLE events (event_date DATE);
INSERT INTO events VALUES ('2025-02-01');

Now query using formatting only for display:

SELECT to_char(event_date, 'YYYY/MM/DD') AS formatted_date FROM events;

That returns 2025/02/01, but the underlying column remains a DATE — so range filters like WHERE event_date BETWEEN '2025-02-01' AND '2025-02-28' use btree indexes efficiently. This avoids the cost of casting text columns on every scan.

Related

• PostgreSQL DESCRIBE TABLE: The psql \d Equivalent
• Which Version of PostgreSQL Am I Running? 3 Ways to Check
• How to Drop All Tables in PostgreSQL Safely (2026)

---

Originally published at https://www.iloveblogs.blog