Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

postgres

How to extract the date from a column in Postgres

abhilash

Overview

We can extract the date from a timestamp column in the following ways:

  1. The DATE() method
  2. The ::date suffix
  3. The TO_CHAR() method
  4. The DATE_TRUNC() method

The DATE() method

We use the DATE() method to extract the date part of a date or date/time or timestamp expression.

Example

select DATE('2022-03-17 01:18:30') as date;

The ::date suffix

We use the ::date suffix to cast the timestamp column to the date type.

Example

In the below code, we first convert the string to a timestamp by suffixing it with ::timestamp suffix. Then we convert the timestamp to a date by suffixing it with ::date.

select '2022-03-17 01:18:30'::timestamp::date;

The TO_CHAR() method

The TO_CHAR() method returns a string in a TEXT data type representing the first argument formatted according to the specified format.

We can use TO_CHAR() to convert the timestamp to date by specifying the format as yyyy-mm-dd.

Example

select TO_CHAR('2022-03-17 01:18:30'::timestamp, 'yyyy-mm-dd') as date;

The DATE_TRUNC() method

We use the DATE_TRUNC() method to truncate the TIMESTAMP or an INTERVAL value and return the truncated timestamp or interval with a level of precision with the below syntax:

date_trunc('datepart', field)

The datepart can be day, second, month, and so on.

Example

select date_trunc('day', '2022-03-17 01:18:30'::timestamp) as date;

RELATED TAGS

postgres
RELATED COURSES

View all Courses

Keep Exploring