SQL: JOINS - Advanced SQL Commands - Timestamps and Extract

Timestamps

Timestamp data types are used to retain information.

Extract

The extract function extracts parts from a date. Look at extract functions for a more detailed list on extract functions.

Syntax:

-- EXTRACT
extract (unit_from_date)

Example: Examine the payment_date in the payment table

-- Examine the payment_date in the payment table. Extract the day from the payment date
SELECT customer_id, extract(day from payment_date) AS day
FROM
payment
limit 10;
##    customer_id day
## 1          341  15
## 2          341  16
## 3          341  16
## 4          341  19
## 5          341  20
## 6          341  21
## 7          342  17
## 8          342  20
## 9          342  20
## 10         343  16

Example: What is the total amount of expenditure by the month?

-- What is the total amount of expenditure by the month?
SELECT SUM(amount), extract(month from payment_date) AS month
FROM payment
GROUP BY month
ORDER BY SUM(amount) DESC ;
##        sum month
## 1 28559.46     4
## 2 23886.56     3
## 3  8351.84     2
## 4   514.18     5