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.

Practice

1. What does the EXTRACT function do in PostgreSQL when used with a date or timestamp?

easy

A. It adds days to the date.

B. It changes the date format to a string.

C. It retrieves a specific part like year, month, or day from the date or timestamp.

D. It deletes the time part from the timestamp.

Solution

1. Step 1: Understand the purpose of EXTRACT

   The EXTRACT function is designed to pull out specific parts such as year, month, day, hour, etc., from a date or timestamp value.

2. Step 2: Compare with other options

   Options B, C, and D describe different operations like formatting, adding days, or truncating time, which EXTRACT does not do.

3. Final Answer:

   It retrieves a specific part like year, month, or day from the date or timestamp. -> Option C

4. Quick Check:

   EXTRACT returns date parts = A [OK]

Hint: EXTRACT pulls out parts like year or month from dates [OK]

Common Mistakes:

• Thinking EXTRACT changes date format
• Confusing EXTRACT with date arithmetic
• Assuming EXTRACT removes time part

2. Which of the following is the correct syntax to extract the month from a timestamp column named created_at?

easy

A. EXTRACT(created_at, MONTH);

B. EXTRACT(MONTH FROM created_at);

C. EXTRACT('MONTH', created_at);

D. EXTRACT MONTH created_at;

Solution

1. Step 1: Recall correct EXTRACT syntax

   The correct syntax is EXTRACT(part FROM source), so to get month: EXTRACT(MONTH FROM created_at);

2. Step 2: Identify incorrect syntax forms

   Options A, B, and D use wrong argument order, quotes around part name, missing FROM keyword, or wrong punctuation.

3. Final Answer:

   EXTRACT(MONTH FROM created_at); -> Option B

4. Quick Check:

   EXTRACT(part FROM source) = C [OK]

Hint: Use EXTRACT(part FROM source) syntax exactly [OK]

Common Mistakes:

• Omitting FROM keyword
• Using quotes around part name
• Swapping argument order

3. Given the query:

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

What will be the output?

medium

A. 10

B. 6

C. 2024

D. 15

Solution

1. Step 1: Identify the part extracted

   The query extracts the DAY part from the timestamp '2024-06-15 10:30:00'. The day is 15.

2. Step 2: Confirm output value

   Other options represent month (6), year (2024), and hour (10), which are not extracted here.

3. Final Answer:

   15 -> Option D

4. Quick Check:

   EXTRACT(DAY) = 15 [OK]

Hint: Match EXTRACT part with date component value [OK]

Common Mistakes:

• Confusing day with month or year
• Expecting full date output
• Ignoring time part in timestamp

4. What is wrong with this query?

SELECT EXTRACT('YEAR' FROM CURRENT_DATE);

medium

A. The part name YEAR should not be in quotes.

B. CURRENT_DATE cannot be used with EXTRACT.

C. Missing parentheses around CURRENT_DATE.

D. EXTRACT requires two arguments separated by a comma.

Solution

1. Step 1: Check syntax for EXTRACT part

   The part name like YEAR should be unquoted. Quotes cause syntax error.

2. Step 2: Verify other parts of query

   CURRENT_DATE is valid input, no parentheses needed, and EXTRACT uses FROM keyword, not comma.

3. Final Answer:

   The part name YEAR should not be in quotes. -> Option A

4. Quick Check:

   Part names unquoted in EXTRACT [OK]

Hint: Do not put quotes around date parts in EXTRACT [OK]

Common Mistakes:

• Using quotes around part names
• Adding commas instead of FROM
• Thinking CURRENT_DATE needs parentheses

5. You want to find all records from a table orders where the order was placed in the first quarter (January to March) of any year. Which query correctly uses EXTRACT to filter these records?

hard

A. SELECT * FROM orders WHERE EXTRACT(QUARTER FROM order_date) = 1;

B. SELECT * FROM orders WHERE EXTRACT('MONTH' FROM order_date) < 4;

C. SELECT * FROM orders WHERE EXTRACT(MONTH, order_date) IN (1, 2, 3);

D. SELECT * FROM orders WHERE EXTRACT(MONTH order_date) BETWEEN 1 AND 3;

Solution

1. Step 1: Understand quarter extraction

   PostgreSQL supports EXTRACT(QUARTER FROM date) which returns 1 for Jan-Mar, 2 for Apr-Jun, etc.

2. Step 2: Evaluate each option

   Options A (missing FROM), B (quotes around MONTH), and C (comma instead of FROM) have syntax errors. SELECT * FROM orders WHERE EXTRACT(QUARTER FROM order_date) = 1; correctly uses EXTRACT(QUARTER FROM order_date) = 1.

3. Final Answer:

   SELECT * FROM orders WHERE EXTRACT(QUARTER FROM order_date) = 1; -> Option A

4. Quick Check:

   Use EXTRACT(QUARTER) = 1 for first quarter [OK]

Hint: Use EXTRACT(QUARTER) = 1 for first quarter filtering [OK]

Common Mistakes:

• Missing FROM keyword
• Using quotes around part names
• Using comma instead of FROM
• Not knowing EXTRACT supports QUARTER