Overview of basic

arithmetic operators
F U N C T I O N S F O R M A N I P U L AT I N G D ATA I N P O S T G R E S Q L

Brian Piccolo
Sr. Director, Digital Strategy
Topics
Overview of basic arithmetic operators

The CURRENT_DATE , CURRENT_TIMESTAMP , NOW() functions

The AGE() function

The EXTRACT() , DATE_PART() , and DATE_TRUNC() functions

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Adding and subtracting date / time data
SELECT date '2005-09-11' - date '2005-09-10';

+---------+
| integer |
|---------|
| 1 |
+---------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Adding and subtracting date / time data
SELECT date '2005-09-11' + integer '3';

+------------+
| date |
|------------|
| 2005-09-14 |
+------------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Adding and subtracting date / time data
SELECT date '2005-09-11 00:00:00' - date '2005-09-09 12:00:00';

+----------------+
| interval |
|----------------|
| 1 day 12:00:00 |
+----------------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Calculating time periods with AGE
SELECT AGE(timestamp '2005-09-11 00:00:00', timestamp '2005-09-09 12:00:00');

+----------------+
| interval |
|----------------|
| 1 day 12:00:00 |
+----------------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

DVDs, really??
SELECT
AGE(rental_date)
FROM rental;

+-----------------------------------+
| age |
|-----------------------------------|
| 13 years 11 mons 12 days 01:06:30 |
| 13 years 11 mons 12 days 01:05:27 |
| 13 years 11 mons 12 days 00:56:21 |
+-----------------------------------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Date / time arithmetic using INTERVALs
SELECT rental_date + INTERVAL '3 days' as expected_return
FROM rental;

+---------------------+
| expected_return |
|---------------------|
| 2005-05-27 22:53:30 |
+---------------------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Date / time arithmetic using INTERVALs
SELECT timestamp '2019-05-01' + 21 * INTERVAL '1 day';

+----------------------------+
| timestamp without timezone |
|----------------------------|
| 2019-05-22 00:00:00 |
+----------------------------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

 Let's practice!
F U N C T I O N S F O R M A N I P U L AT I N G D ATA I N P O S T G R E S Q L
 Functions for
retrieving current
date/time
F U N C T I O N S F O R M A N I P U L AT I N G D ATA I N P O S T G R E S Q L

Brian Piccolo
Sr. Director, Digital Strategy
Retrieving the current timestamp
SELECT NOW();

+-------------------------------+
| now() |
|-------------------------------|
| 2019-04-19 02:51:18.448641+00 |
+-------------------------------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Retrieving the current timestamp
SELECT NOW()::timestamp;

+----------------------------+
| now() |
|----------------------------|
| 2019-04-19 02:51:18.448641 |
+----------------------------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Retrieving the current timestamp
PostgreSQL speci c casting
SELECT NOW()::timestamp;

CAST() function
SELECT CAST(NOW() as timestamp);

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Retrieving the current timestamp
SELECT CURRENT_TIMESTAMP;

+-------------------------------+
| current_timestamp |
|-------------------------------|
| 2019-04-19 02:51:18.448641+00 |
+-------------------------------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Retrieving the current timestamp
SELECT CURRENT_TIMESTAMP(2);

+-------------------------------+
| current_timestamp |
|-------------------------------|
| 2019-04-19 02:51:18.44+00 |
+-------------------------------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Current date and time
SELECT CURRENT_DATE;

+--------------+
| current_date |
|--------------|
| 2019-04-19 |
+--------------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Current date and time
SELECT CURRENT_TIME;

+------------------------+
| current_time |
|------------------------|
| 04:06:30.929845+00:00 |
+------------------------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

 Let's practice!
F U N C T I O N S F O R M A N I P U L AT I N G D ATA I N P O S T G R E S Q L
 Extracting and
transforming date /
time data
F U N C T I O N S F O R M A N I P U L AT I N G D ATA I N P O S T G R E S Q L

Brian Piccolo
Sr. Director, Digital Strategy
Extracting and transforming date and time data
Exploring the EXTRACT() , DATE_PART() and DATE_TRUNC() functions

Transactional timestamp precision not useful for analysis

2005-05-13 08:53:53

O en need to extract parts of timestamps

2005 or 5 or 2 or Friday

Or convert / truncate timestamp precision to standardize

2005-05-13 00:00:00

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Extracting and transforming date / time data
EXTRACT( eld FROM source )

SELECT EXTRACT(quarter FROM timestamp '2005-01-24 05:12:00') AS quarter;

DATE_PART(' eld', source)

SELECT DATE_PART('quarter', timestamp '2005-01-24 05:12:00') AS quarter;

+---------+
| quarter |
|---------|
| 1 |
+---------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Extracting sub-fields from timestamp data
Transactional data from DVD Rentals payment table

SELECT * FROM payment;

+--------------------------------------------------------------------------------+
| payment_id | customer_id | staff_id | rental_id | amount | payment_date |
|------------|-------------|----------|-----------|--------|---------------------|
| 1 | 1 | 1 | 76 | 2.99 | 2005-05-25 11:30:37 |
| 2 | 1 | 1 | 573 | 0.99 | 2005-05-28 10:35:23 |
| 3 | 1 | 1 | 1185 | 5.99 | 2005-06-15 0:54:12 |
+--------------------------------------------------------------------------------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Extracting sub-fields from timestamp data
Data from payment table by year and quarter Results

SELECT +---------------------------------+
EXTRACT(quarter FROM payment_date) AS quarter, | quarter | year | total_payments |
EXTRACT(year FROM payment_date) AS year, |---------|------|----------------|
SUM(amount) AS total_payments | 2 | 2005 | 14456.31 |
FROM | 3 | 2005 | 52446.02 |
payment | 1 | 2006 | 514.18 |
GROUP BY 1, 2; +---------------------------------+

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

Truncating timestamps using DATE_TRUNC()
The DATE_TRUNC() function will truncate timestamp or interval data types.

Truncate timestamp '2005-05-21 15:30:30' by year

SELECT DATE_TRUNC('year', TIMESTAMP '2005-05-21 15:30:30');

Result: 2005-01-01 00:00:00

Truncate timestamp '2005-05-21 15:30:30' by month

SELECT DATE_TRUNC('month', TIMESTAMP '2005-05-21 15:30:30');

Result: 2005-05-01 00:00:00

FUNCTIONS FOR MANIPULATING DATA IN POSTGRESQL

 Let's practice!
F U N C T I O N S F O R M A N I P U L AT I N G D ATA I N P O S T G R E S Q L