Date Time Function

Why do we need date-time manipulation?

Most of the data in the real world include date-time data. For most of the analysis, we need to perform
data manipulation on date columns to perform research (not limited) to:

● Daily/weekly/monthly trends(for example, sales trends for an e-commerce website)

● Perform forecast (Eg: Demand for cabs in a city)

● Analysis using time data( Eg: Number of days since a customer made a transaction, time is taken
between two cab

We need to learn SQL functions for manipulating data for all the analyses mentioned above.

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Date/Time Data Types

There are four main data types:

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Understanding Data Time Functions

In the coming slides, we will understand the usage of each function with a demonstration. We will use the below dataset for
demonstration:

Ride_share

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 NOW() Function

It returns the current date and time.

Query
SELECT *, NOW() AS current_date_time FROM ride_share
Output

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 CURRENT_DATE Function

It returns the current date

Query
SELECT *, CURRENT_DATE AS current_date FROM ride_share
Output

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 CURRENT_TIME Function

It returns the current time

Query
SELECT *, CURRENT_TIME AS current_time FROM ride_share
Output

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 DATE() Function

It extracts the date part from a date_time expression.

Query
SELECT *, CURRENT_TIME AS current_time FROM ride_share
Output

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 EXTRACT() Function

It returns a single part from a date/time datatype.

Syntax of Extract Function

EXTRACT (field from date/time source)

This function takes two inputs:

1. Field: It refers to the value you want to extract from the date/time datatype

1. Date/time source: It refers to the date/time column from which we want to extract the field

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 EXTRACT() Function – Possible inputs for Field

Potential input for field

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 EXTRACT() Function – Possible inputs for Field

Potential input for field

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 EXTRACT() Function – An Example

Query
SELECT *,
EXTRACT(DAY FROM start_time) AS ride_start_day,
EXTRACT(MONTH FROM start_time) AS ride_start_month,
EXTRACT(YEAR FROM start_time) AS ride_start_year
FROM ride_share
Output

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Instructions for practice questions

● Log into https://mode.com/

● Create a new report

● Access database tutorial.dc_bikeshare_q1_2012, tutorial.crunchbase_companies_clean_date,

tutorial.crunchbase_acquisitions_clean_date

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Practice Question - 1

Find the number of rides per month.

Instructions:
Use tutorial.dc_bikeshare_q1_2012 table. Extract the month from start_terminal and then count the id.

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Solution - 1

SELECT
EXTRACT(month FROM start_time) AS month,
COUNT(id) AS num_rides
FROM
tutorial.dc_bikeshare_q1_2012
GROUP BY 1
ORDER BY
month

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Solution - 1

Output

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Practice Question - 2

Find the number of rides per month per rider type.

Instructions:
Use tutorial.dc_bikeshare_q1_2012 table. Extract the month from start_terminal and then count the id and group by rider type.

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Solution - 2

SELECT
EXTRACT(month FROM start_time) AS month,
rider_type,
COUNT(id) AS num_rides
FROM
tutorial.dc_bikeshare_q1_2012
GROUP BY 1, rider_type
ORDER BY
month

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Solution - 2

Output

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Adding time interval to the date/time column

We can add a time interval to a date/time column. The interval value can be minute, hour, day, week,
month.

Syntax

Date_column + INTERVAL ‘mention the interval value’

* Refer to the field values discussed earlier to find the interval values

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Adding time interval to the date/time column

Query
SELECT *,
id, start_time, start_time + INTERVAl '1 DAY' AS start_date_day_add, start_time + INTERVAl '1 HOUR' AS start_date_hour_add,
start_time + INTERVAl '1 WEEK' AS start_date_week_add, start_time + INTERVAl '1 MONTH' AS start_date_month_add
FROM ride_share
Output

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Subtracting time interval from date/time column

We can add a time interval to a date/time column. Interval value can be minute, hour, day,
week, month.

Syntax of Extract Function

Date_column - INTERVAL ‘mention the interval value’

* Refer to the field values discussed earlier to find the interval values

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Subtracting time interval from date/time column

Query
SELECT *,
id, start_time, start_time - INTERVAl '1 DAY' AS start_date_day_diff, start_time - INTERVAl '1 HOUR' AS start_date_hour_diff, start_time -
INTERVAl '1 WEEK' AS start_date_week_diff, start_time - INTERVAl '1 MONTH' AS start_date_month_diff
FROM ride_share
Output

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Practice Question - 3

Find the difference (in hours) between the first ride and the last ride per terminal. Show terminal_id, first ride start time, last ride start time,
and the difference in hours.

Instructions:
Use tutorial.dc_bikeshare_q1_2012 table. Use the Max and Min functions to find the last and first ride. Subtract the first and last ride time
using date_part function.

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Solution - 3

SELECT
start_terminal,
first_ride_start,
last_ride_start,
DATE_PART('day',last_ride_start - first_ride_start) *24 + DATE_PART('hour',last_ride_start - first_ride_start) AS diff_hours
FROM(
SELECT
start_terminal,
MIN(start_time) AS first_ride_start,
MAX(start_time) AS last_ride_start
FROM
tutorial.dc_bikeshare_q1_2012
GROUP BY start_terminal
) AS a

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Solution - 3

Output

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Subtracting two dates

We can achieve this using the date_part function.

Syntax of Extract Function

Date_part(‘interval’, date1-date2) [date1>date2]

* Refer to the field values discussed earlier to find the interval values

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Subtracting two dates

Query
SELECT *,
DATE_PART('minute',end_time - start_time) AS diff_minutes
FROM
ride_share
Output

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Practice Question - 4

Find the time taken to acquire a company.

Instructions:
Join tutorial.crunchbase_companies_clean_date and tutorial.crunchbase_acquisitions_clean_date . Take a difference between the
founded date and acquired date.

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Solution - 4

SELECT
companies.permalink,
companies.founded_at_clean,
acquisitions.acquired_at_cleaned,
acquisitions.acquired_at_cleaned - companies.founded_at_clean::timestamp AS time_to_acquisition
FROM
tutorial.crunchbase_companies_clean_date companies
JOIN
tutorial.crunchbase_acquisitions_clean_date acquisitions
ON acquisitions.company_permalink = companies.permalink
WHERE founded_at_clean IS NOT NULL

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Solution - 4

Output

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Practice Question - 5

Write a query to find how long ago a company is founded.

Instructions:
Use tutorial.crunchbase_companies_clean_date. Use NOW() function and subtract it with the founded date.

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Solution - 5

SELECT
companies.permalink,
companies.founded_at_clean,
NOW() - companies.founded_at_clean::timestamp AS founded_time_ago
FROM
tutorial.crunchbase_companies_clean_date companies
WHERE
founded_at_clean IS NOT NULL

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Solution - 5

Output

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Practice Question - 6

Write a query that counts the number of companies acquired within 3 years, 5 years, and 10 years of being founded (in 3 separate
columns). Include a column for total companies acquired as well. Group by the category and limit to only rows with a founding date.

Instructions:
Use tutorial.crunchbase_companies_clean_date. Use NOW() function and subtract it with the founded date.

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Solution - 6

SELECT
companies.category_code,
COUNT(CASE WHEN acquisitions.acquired_at_cleaned <= companies.founded_at_clean::timestamp + INTERVAL '3 years' THEN 1
ELSE NULL END) AS acquired_3_yrs,
COUNT(CASE WHEN acquisitions.acquired_at_cleaned <= companies.founded_at_clean::timestamp + INTERVAL '5 years' THEN 1
ELSE NULL END) AS acquired_5_yrs,
COUNT(CASE WHEN acquisitions.acquired_at_cleaned <= companies.founded_at_clean::timestamp + INTERVAL '10 years' THEN 1
ELSE NULL END) AS acquired_10_yrs,
COUNT(1) AS total
FROM
tutorial.crunchbase_companies_clean_date companies
JOIN
tutorial.crunchbase_acquisitions_clean_date acquisitions
ON acquisitions.company_permalink = companies.permalink
WHERE founded_at_clean IS NOT NULL
GROUP BY 1
ORDER BY 5 DESC

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Solution - 6

Output

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 Curious Case of Date Time Function

Unlike aggregate and windows functions, time date functions depend significantly on the database type.
The functions which work on one database might not work on another.

For Example:

● In the PostgreSQL database, we use the date_part() function to take the difference between two
dates. In the MySQL database, we can use the DATEDIFF function.

● Similarly, MYSQL uses CUR_TIME CUR_DATE to find the current time or date. Whereas PostgreSQL
use CURRENT_TIME and CURRENT_DATE

In this session, we have taught functions by PostgreSQL. We will advise referring to the database type
before using the date/time function.

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 In the next class we will study:

String Functions and Pivoting Data

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose
 THANK YOU

Class 31
17
27
4 #90DaysofPurpose
#150DaysofPurpose