EXTRACT function for date parts in PostgreSQL - Step-by-Step Execution

Concept Flow - EXTRACT function for date parts

Start with a timestamp/date

↓

Call EXTRACT(part FROM timestamp)

↓

Identify requested part (year, month, day, etc.)

↓

Retrieve that part from the timestamp

↓

Return the extracted value as a number

↓

End

The EXTRACT function takes a date or timestamp and returns the requested part (like year or month) as a number.

Execution Sample

PostgreSQL

SELECT EXTRACT(year FROM TIMESTAMP '2024-06-15 10:30:00');

Extracts the year part from the given timestamp.

Execution Table

Step	Action	Input	Extracted Part	Result
1	Receive timestamp	2024-06-15 10:30:00	-	-
2	Call EXTRACT(year FROM timestamp)	year, 2024-06-15 10:30:00	year	-
3	Retrieve year part	2024-06-15 10:30:00	year	2024
4	Return result	-	-	2024

💡 Extraction complete, returned year 2024

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
timestamp	2024-06-15 10:30:00	2024-06-15 10:30:00	2024-06-15 10:30:00	2024-06-15 10:30:00
part	-	year	year	year
extracted_value	-	-	2024	2024

Key Moments - 2 Insights

Why does EXTRACT return a number and not a date or timestamp?

Because EXTRACT pulls out only one part (like year or month) as a numeric value, not the whole date. See execution_table row 3 where only the year number 2024 is returned.

Can EXTRACT be used on just a date without time?

Yes, EXTRACT works on both date and timestamp types. The function extracts the requested part regardless of time presence, as shown in the input in execution_table row 1.

Visual Quiz - 3 Questions

Look at the execution_table, what is the extracted value at step 3?

A 06

B 15

C 2024

D 10

💡 Hint

Check the 'Extracted Part' and 'Result' columns at step 3 in the execution_table.

At which step does the EXTRACT function identify the part to retrieve?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Action' column to see when the part is specified.

If we change the part from 'year' to 'month', what would be the final result?

A 6

B 15

C 2024

D 10

💡 Hint

Refer to the timestamp '2024-06-15 10:30:00' and think which number represents the month.

Concept Snapshot

EXTRACT(part FROM timestamp) returns the numeric value of the specified date/time part.
Common parts: year, month, day, hour, minute, second.
Works on date, timestamp, and interval types.
Returns a number, not a date.
Example: EXTRACT(year FROM TIMESTAMP '2024-06-15') returns 2024.

Full Transcript

The EXTRACT function in PostgreSQL takes a date or timestamp and returns a specific part of it as a number. For example, extracting the year from '2024-06-15 10:30:00' returns 2024. The function works by receiving the timestamp, identifying the requested part like year or month, retrieving that part, and returning it as a numeric value. This is useful when you want to work with just one component of a date or time. EXTRACT works on both date and timestamp types and always returns a number, not a full date or time.