[Udemy] 15 Days of SQL - Day 4 ② : EXTRACT, TO_CHAR, Timestamp

6. EXTRACT

• Used to extract parts of timestamp/date
• Syntax

EXTRACT (field from date/time/interval)

SELECT
EXTRACT(day from rental_date),
COUNT(*)
FROM rental
GROUP BY EXTRACT(day from rental_date)
ORDER BY COUNT(*) DESC

 

* Challenge

Write a query to find out the following:

1)  What's the month with the highest total payment amount?

2)  What's the day of week with the highest total payment amount?(0 is Sunday)

3)  What's the highest amount one customer has spent in a week?

--#1
SELECT
EXTRACT(month from payment_date) AS month,
SUM(amount) AS total_payment_amount
FROM payment
GROUP BY EXTRACT(month from payment_date)
-- GROUP BY month can also be fine as we named it as 'month' in line 3
ORDER BY SUM(amount) DESC

--#2
SELECT
EXTRACT(week from payment_date) as day_of_week,
SUM(amount) as total_payment_amount, 
customer_id
FROM payment
GROUP BY EXTRACT(week from payment_date), customer_id
-- GROUPY BY day_of_week, customer_id
ORDER BY SUM(amount) DESC

-- #3
SELECT
EXTRACT(dow from payment_date),
SUM(amount)
FROM payment
GROUP BY EXTRACT(dow from payment_date)
ORDER BY SUM(amount) DESC

---

7. TO_CHAR

• Used to get custome formats timestamp/date/numbers
• Syntax

TO_CHAR(date/time/interval, format)
TO_CHAR(rental_date, 'MM-YYYY')

SELECT
SUM(amount),
TO_CHAR(payment_date, 'Dy, Month YYYY')
FROM payment
GROUP BY TO_CHAR(payment_date, 'Dy, Month YYYY')

 

 

reference document :  https://www.postgresql.org/docs/current/functions-formatting.html 

 

* Challenge

Write a query to sum payments and group in the following formats

1)

SELECT
SUM(amount) AS total_amount,
TO_CHAR(payment_date, 'Dy, DD/MM/YYYY') AS day
FROM payment
GROUP BY TO_CHAR(payment_date, 'Dy, DD/MM/YYYY')
ORDER BY SUM(amount) ASC

 

2)

SELECT
SUM(amount) AS total_amount,
TO_CHAR(payment_date, 'Mon,YYYY') AS day
FROM payment
GROUP BY TO_CHAR(payment_date, 'Mon,YYYY')
ORDER BY SUM(amount) ASC

 

3)

SELECT
SUM(amount) AS total_amount,
TO_CHAR(payment_date, 'Dy,HH:MI') AS day
FROM payment
GROUP BY TO_CHAR(payment_date, 'Dy,HH:MI')
ORDER BY SUM(amount) DESC

 

---

8. Timestamp and Interval

• Used to get a current timestamp

SELECT CURRENT_TIMESTAMP

• Used to get an intervals between dates

SELECT 
CURRENT_TIMESTAMP,
rental_date,
CURRENT_TIMESTAMP - rental_date
FROM rental

• Used with EXTRACT to extract particular numbers

SELECT 
CURRENT_TIMESTAMP,
rental_date,
EXTRACT(day from CURRENT_TIMESTAMP - rental_date)
FROM rental

 

* Challenge

Write a query to create a list for the support team of all rental durations of customer with customer_id 35.

Find out which customer has the longest average rental duration.

SELECT
customer_id,
return_date - rental_date AS rental_durations
FROM rental
WHERE customer_id = 35

SELECT
customer_id,
AVG(return_date - rental_date) AS avg
FROM rental
GROUP BY customer_id
ORDER BY avg DESC