<jupyter_start><jupyter_text>Handling Dates- Most data requests and analysis involve dates- Columns need to be a date-related data type to take advantage of date manipulations    - ```DATE```    - ```DATETIME```    - ```TIMESTAMP``` (factors in timezones)  UTC or Local Time Zone?- [DateTime values should always be stored in UTC](https://blog.abodit.com/2010/02/datetime-values-should-always-be-stored-in-utc)- [3 simple rules for effectively handling dates and timezones](https://dev.to/corykeane/3-simple-rules-for-effectively-handling-dates-and-timezones-1pe0)- [STORING UTC IS NOT A SILVER BULLET](https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/)  [60 MySQL Date and Time Functions](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html)- Oracle has 25- Microsoft SQL Server has 26- We will cover 14 of the 60    - [80/20 rule](https://medium.com/better-programming/change-your-life-as-a-programmer-with-the-80-20-rule-17c325609343) Install [ipython-sql](https://github.com/catherinedevlin/ipython-sql) library<jupyter_code>!pip install ipython-sql
# installing more modules to fix databse connection issues
!pip install PyMySQL
import pymysql
pymysql.install_as_MySQLdb()
import sqlalchemy<jupyter_output>Requirement already satisfied: PyMySQL in /opt/anaconda3/lib/python3.8/site-packages (1.0.2)<jupyter_text>Load SQL extension<jupyter_code>%load_ext sql<jupyter_output>The sql extension is already loaded. To reload it, use:
  %reload_ext sql<jupyter_text>Connect to the LMU Build sakila database<jupyter_code>%sql mysql://sjilmubu_dba:<EMAIL>/sjilmubu_sakila<jupyter_output><empty_output><jupyter_text>--- MySQL Date Functions  https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html ---01 - ```CURDATE()``` - Return current date<jupyter_code>%%sql 
SELECT CURDATE();<jupyter_output>* mysql://sjilmubu_dba:***@sji.lmu.build/sjilmubu_sakila
1 rows affected.<jupyter_text>---02 - ```NOW()``` - Return current datetime<jupyter_code>%%sql
SELECT NOW();<jupyter_output>* mysql://sjilmubu_dba:***@sji.lmu.build/sjilmubu_sakila
1 rows affected.<jupyter_text>---03 - ```DATE()``` - Returns the date portion of a datetime value<jupyter_code>%%sql
SELECT DATE(NOW());<jupyter_output>* mysql://sjilmubu_dba:***@sji.lmu.build/sjilmubu_sakila
1 rows affected.<jupyter_text>---04 - Return just the date of the rental_date for a random sample of 10 rentals.Randomize sort with ```ORDER BY RAND()``````SELECT DATE(column/expression);```<jupyter_code>%%sql
SELECT rental_date
FROM rental
ORDER BY RAND()
LIMIT 10;<jupyter_output>* mysql://sjilmubu_dba:***@sji.lmu.build/sjilmubu_sakila
10 rows affected.<jupyter_text>---05 - Return the number of rentals for each day in August 2005. ```rental_date``` is a ```DATETIME```<jupyter_code>%%sql
SELECT COUNT(rental_date) AS august_rental_count
FROM rental
WHERE rental_date BETWEEN '2005-08-01 00:00:00' AND '2005-08-31 23:59:59';<jupyter_output>* mysql://sjilmubu_dba:***@sji.lmu.build/sjilmubu_sakila
1 rows affected.<jupyter_text>---06 - ```DATE_FORMAT(date, format)``` - Format a date  Return the number of rentals for each day in August 2005.  Format the date in month/day/year, i.e. 08/23/2005.  [Format options](https://www.w3schools.com/sql/func_mysql_date_format.asp)<jupyter_code>%%sql
SELECT 
    DATE_FORMAT(DATE(rental_date),'%m/%d/%Y') AS date_rental_date,
    COUNT(*) AS rental_count
FROM rental
WHERE rental_date BETWEEN '2005-08-01' AND '2005-08-31 23:59:59'
GROUP BY date_rental_date;<jupyter_output>* mysql://sjilmubu_dba:***@<EMAIL>.build/sjilmubu_sakila
10 rows affected.<jupyter_text>Assign results to a variable<jupyter_code>%%sql rental_count_by_date <<
SELECT 
    DATE_FORMAT(DATE(rental_date),'%m/%d/%Y') AS date_rental_date,
    COUNT(*) AS rental_count
FROM rental
WHERE rental_date BETWEEN '2005-08-01' AND '2005-08-31 23:59:59'
GROUP BY date_rental_date;<jupyter_output>* mysql://sjilmubu_dba:***@sji.lmu.build/sjilmubu_sakila
10 rows affected.
Returning data to local variable rental_count_by_date<jupyter_text>Convert variable to a pandas DataFrame<jupyter_code>rental_count_by_date_df = rental_count_by_date.DataFrame()<jupyter_output><empty_output><jupyter_text>Display the first 5 rows of the DataFrame<jupyter_code>rental_count_by_date_df.head()
    # The SQL result is now a pandas dataframe
# fixing bug
rental_count_by_date_df=list<jupyter_output><empty_output><jupyter_text>Install matplolib for graphing<jupyter_code>!pip install matplotlib<jupyter_output>Requirement already satisfied: matplotlib in /opt/anaconda3/lib/python3.8/site-packages (3.3.2)
Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.3 in /opt/anaconda3/lib/python3.8/site-packages (from matplotlib) (2.4.7)
Requirement already satisfied: kiwisolver>=1.0.1 in /opt/anaconda3/lib/python3.8/site-packages (from matplotlib) (1.3.0)
Requirement already satisfied: python-dateutil>=2.1 in /opt/anaconda3/lib/python3.8/site-packages (from matplotlib) (2.8.1)
Requirement already satisfied: cycler>=0.10 in /opt/anaconda3/lib/python3.8/site-packages (from matplotlib) (0.10.0)
Requirement already satisfied: certifi>=2020.06.20 in /opt/anaconda3/lib/python3.8/site-packages (from matplotlib) (2020.6.20)
Requirement already satisfied: numpy>=1.15 in /opt/anaconda3/lib/python3.8/site-packages (from matplotlib) (1.19.2)
Requirement already satisfied: pillow>=6.2.0 in /opt/anaconda3/lib/python3.8/site-packages (from matplotlib) (8.0.1)
Requirement already satisfied: six>=1.[...]<jupyter_text>Display plots within the notebook with a magic function<jupyter_code>%matplotlib inline<jupyter_output><empty_output><jupyter_text>Plot a bar chart for the rental counts by date SQL magic command results<jupyter_code>rental_count_by_date.bar()<jupyter_output><empty_output><jupyter_text>---07 - ```DATEDIFF(date1, date2)``` - Return difference in days between 2 dates. Time portion is ignored. Dates need to be in a MySQL date format: ```YYYY-MM-DD``` or ```YYYY-MM-DD hh:mm:ss```.  What is the difference in days between Jan 1, 2021 and Feb 12, 2021 and between August 23, 2016 and December 7, 2015?<jupyter_code>%%sql

# Jan 1, 2021 and Feb 12, 2021
SELECT
    DATEDIFF('2021-01-01', '2021-02-12') AS past_to_future,
    DATEDIFF('2016-08-23','2015-12-07') AS future_to_past;<jupyter_output>* mysql://sjilmubu_dba:***@sji.lmu.build/sjilmubu_sakila
1 rows affected.<jupyter_text>---08 - Find the difference in days between the rental_date and return_date for August 2005 rentals.<jupyter_code>%%sql
SELECT 
    date_rental_date, 
    date_return_date, 
    DATEDIFF(date_rental_date, date_return_date) AS days_rented
FROM
    (SELECT 
        DATE(rental_date) AS date_rental_date,
        DATE(return_date) AS date_return_date
    FROM rental
    WHERE rental_date BETWEEN '2005-08-01' AND '2005-08-31 23:59:59'
    GROUP BY date_rental_date)
AS august_rentals;<jupyter_output>* mysql://sjilmubu_dba:***@sji.lmu.build/sjilmubu_sakila
10 rows affected.<jupyter_text>---```DATE_ADD(date, INTERVAL expr unit)``` - Add a time value to a date or datetimeUseful for tracking when to charge a recurring subscription payment[Unit options](https://www.w3schools.com/sql/func_mysql_date_add.asp)09 - Return the following dates:- current date- 1 day after the current date- 2 weeks after the current date- 3 months after the current date- 4 years after the current date<jupyter_code>%%sql
SELECT
    CURDATE() AS today,
    DATE_ADD(CURDATE(), INTERVAL 1 DAY) AS one_day_later,
    DATE_ADD(CURDATE(), INTERVAL 1 WEEK) AS one_week_later,
    DATE_ADD(CURDATE(), INTERVAL 3 MONTH) AS three_months_later,
    DATE_ADD(CURDATE(), INTERVAL 4 YEAR) AS four_years_later;<jupyter_output>* mysql://sjilmubu_dba:***@sji.lmu.build/sjilmubu_sakila
1 rows affected.<jupyter_text>---10 - If a DVD is not returned 30 days after the rental date, the cost of the DVD is charged to the account. Calculate the date that is 30 days after the rental date for August 2005 rentals.<jupyter_code>%%sql
SELECT 
    date_rental_date, 
    DATE_ADD(date_rental_date, INTERVAL 30 DAY) AS rental_due_date
FROM
    (SELECT DATE(rental_date) AS date_rental_date
    FROM rental
    WHERE rental_date BETWEEN '2005-08-01' AND '2005-08-31 23:59:59'
    GROUP BY date_rental_date)
AS august_rental_due_dates;<jupyter_output>* mysql://sjilmubu_dba:***@sji.lmu.build/sjilmubu_sakila
10 rows affected.<jupyter_text>---```DATE_SUB(date, INTERVAL expr unit)``` - Subtract a time value from a date or datetime11 - Subtract 2 hours from the current datetime<jupyter_code>%%sql
SELECT 
    NOW() as now,
    DATE_SUB(NOW(), INTERVAL 2 HOUR) AS two_hours_ago;<jupyter_output>* mysql://sjilmubu_dba:***@sji.lmu.build/sjilmubu_sakila
1 rows affected.<jupyter_text>--- Names, indexes, and descriptions of a date.https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.htmlHandy for reports. Can also use a column name instead of a date string.```DAYOFWEEK()``` - 1 for Sunday, 2 for Monday, … 7 for Saturday   12 - Return the following date parts for 2016-08-23- ```DAY```- ```MONTH```- ```QUARTER```- ```YEAR```- ```DAYNAME```- ```DAYOFWEEK```- ```WEEKOFYEAR```<jupyter_code>%%sql
SELECT
    DAY('2016-08-23') AS day,
    MONTH('2016-08-23') AS month,
    QUARTER('2016-08-23') AS quarter,
    YEAR('2016-08-23') AS year,
    DAYNAME('2016-08-23') AS day_name,
    DAYOFWEEK('2016-08-23') AS day_of_week,
    WEEKOFYEAR('2016-08-23') AS week_of_year;<jupyter_output>* mysql://sjilmubu_dba:***@sji.lmu.build/sjilmubu_sakila
1 rows affected.<jupyter_text>--- Names, indexes, and descriptions of a time.https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html13 - Return the following time parts for 2016-08-23 09:10:08.221435- ```HOUR```- ```MINUTE```- ```SECOND```- ```MICROSECOND```<jupyter_code>%%sql
SELECT
    HOUR('2016-08-23 09:10:08.221435') AS hour,
    MINUTE('2016-08-23 09:10:08.221435') AS minute,
    SECOND('2016-08-23 09:10:08.221435') AS second,
    MICROSECOND('2016-08-23 09:10:08.221435') AS microsecond;<jupyter_output>* mysql://sjilmubu_dba:***@sji.lmu.build/sjilmubu_sakila
1 rows affected.<jupyter_text>---14 - How many rentals per quarter in 2005?<jupyter_code>%%sql
SELECT 
    QUARTER(date_rental_date) AS quarter_2005,
    COUNT(*) AS rentals_per_quarter
FROM
    (SELECT DATE(rental_date) AS date_rental_date
    FROM rental
    WHERE rental_date BETWEEN '2005-01-01 00:00:00' AND '2005-12-31 23:59:59')
AS august_rentals
GROUP BY quarter_2005;<jupyter_output>* mysql://sjilmubu_dba:***@sji.lmu.build/sjilmubu_sakila
2 rows affected.
{'fraction_non_alphanumeric': 0.13369389885188349, 'fraction_numerical': 0.06034728152576146, 'mean_word_length': 5.303827751196172, 'pattern_counts': {'":': 0, '<': 79, '<?xml version=': 0, '>': 85, 'https://': 11, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '23', 'dir': 'jupyter-structured-clean-dedup', 'id': '214159', 'license': 'MIT', 'n_tokens_mistral': 4428, 'n_tokens_neox': 3941, 'n_words': 890, 'path': 'SQL Date Functions silvia ji.ipynb', 'repo_name': 'jisilvia/SQL_String_Date_Functions', 'size': '10554'}