Scribd
Upload
13 views26 pages

Chapter2 34

The document discusses various functions for manipulating date and time data in PostgreSQL. It provides examples of using arithmetic operators to add and subtract dates and times. It also demonstrates using the AGE(), EXTRACT(), DATE_PART(), DATE_TRUNC(), and INTERVAL functions to calculate time periods, extract subfields from timestamps, and truncate timestamps. The functions allow for common date/time operations like retrieving current timestamps, extracting specific fields like year or quarter, and standardizing timestamp precision.

Uploaded by

Mạnh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
13 views26 pages

Chapter2 34

The document discusses various functions for manipulating date and time data in PostgreSQL. It provides examples of using arithmetic operators to add and subtract dates and times. It also demonstrates using the AGE(), EXTRACT(), DATE_PART(), DATE_TRUNC(), and INTERVAL functions to calculate time periods, extract subfields from timestamps, and truncate timestamps. The functions allow for common date/time operations like retrieving current timestamps, extracting specific fields like year or quarter, and standardizing timestamp precision.

Uploaded by

Mạnh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

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

You might also like