The EXTRACT() function with examples in PostgreSQL

At times, you may only need a certain portion of a date interval or TIMESTAMP. Luckily in PostgreSQL, we have the EXTRACT() function that allows you to grab just those particular bits of data. See how with a few simple examples in this post…

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

OS and DB used:

  • Xubuntu Linux 18.04.2 LTS (Bionic Beaver)
  • PostgreSQL 11.2

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

I’ll use a couple of tables from the PostgreSQL practice DVD Rental database for the example queries below.

The examples in this post are based on this arbitrary query and result set:

SELECT
c.first_name AS first_name,
c.last_name AS last_name,
p.payment_date
FROM customer AS c
INNER JOIN payment AS p
ON c.customer_id = p.customer_id
WHERE c.first_name LIKE ‘Z%’
LIMIT 10;
first_name | last_name | payment_date
— — — — — — + — — — — — -+ — — — — — — — — — — — — — —
Zachary | Hite | 2007–02–17 03:04:18.996577
Zachary | Hite | 2007–02–17 06:30:46.996577
Zachary | Hite | 2007–02–17 09:09:02.996577
Zachary | Hite | 2007–02–17 15:01:43.996577
Zachary | Hite | 2007–02–18 13:23:56.996577
Zachary | Hite | 2007–02–19 15:55:51.996577
Zachary | Hite | 2007–03–01 02:41:59.996577
Zachary | Hite | 2007–03–02 06:48:55.996577
Zachary | Hite | 2007–03–17 00:41:52.996577
Zachary | Hite | 2007–03–17 11:31:39.996577
(10 rows)

Pass in the DAY parameter to get the day:

SELECT
EXTRACT(DAY FROM p.payment_date)
FROM customer AS c
INNER JOIN payment AS p
ON c.customer_id = p.customer_id
WHERE c.first_name LIKE ‘Z%’
LIMIT 10;
date_part
— — — — — -
17
17
17
17
18
19
1
2
17
17
(10 rows)

Continue reading the rest of the post here…