EXTRACT function for date parts in PostgreSQL

Introduction

The EXTRACT function helps you get specific parts like year, month, or day from a date or time. It makes it easy to work with dates by focusing on just the part you need.

You want to find the year someone was born from their birthdate.

You need to get the month from a sales date to group sales by month.

You want to know the day of the week for a meeting date.

You want to calculate the hour from a timestamp to analyze busy hours.

You want to extract the quarter from a date to report quarterly results.

Syntax

PostgreSQL

EXTRACT(field FROM source)

field can be year, month, day, hour, minute, second, quarter, dow (day of week), etc.

source is a date, time, or timestamp value.

Examples

Gets the year part (2024) from the date.

PostgreSQL

SELECT EXTRACT(year FROM DATE '2024-06-15');

Gets the month part (6) from the timestamp.

PostgreSQL

SELECT EXTRACT(month FROM TIMESTAMP '2024-06-15 14:30:00');

Gets the day part (15) from the date.

PostgreSQL

SELECT EXTRACT(day FROM DATE '2024-06-15');

Gets the hour part (14) from the timestamp.

PostgreSQL

SELECT EXTRACT(hour FROM TIMESTAMP '2024-06-15 14:30:00');

Sample Program

This query extracts the year, month, day, and day of the week from the date '2024-06-15'.

PostgreSQL

SELECT
  EXTRACT(year FROM DATE '2024-06-15') AS year_part,
  EXTRACT(month FROM DATE '2024-06-15') AS month_part,
  EXTRACT(day FROM DATE '2024-06-15') AS day_part,
  EXTRACT(dow FROM DATE '2024-06-15') AS day_of_week_part
;

Important Notes

The day of week (dow) returns 0 for Sunday, 1 for Monday, and so on.

EXTRACT returns a number, so you can use it in calculations or comparisons.

You can use EXTRACT with timestamps to get time parts like hour, minute, and second.

Summary

EXTRACT helps get specific parts from dates or times easily.

Use it to focus on year, month, day, hour, and more.

It returns numbers you can use in your queries.