SQL notes for a data scientist

By Tuo Wang in Data Science

October 28, 2021

These my personal notes for learning SQL.

Some great tutorials

Learning basic SQL:

Advanced SQL:

Difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and OUTER JOIN

SQL Date Functions

  1. ADD days
# MySQL - DATE_ADD, syntax: DATE_ADD(date, INTERVAL value addunit)

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);
SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); # forward 10 days
SELECT DATE_ADD("2017-06-15", INTERVAL -10 DAY); # backward 10 days
# For substraction, we can also use DATE_SUB
SELECT DATE_SUB("2017-06-15", INTERVAL 10 DAY); 

# Note that, we can use CAST .. AS DATE to transform timestamp to date
SELECT CAST("2017-06-15 09:34:21" AS DATE);
# This transform "2017-06-15 09:34:21" -> "2017-06-15"
# OR we can use DATE() function
SELECT DATE("2017-06-15 09:34:21"); # "2017-06-15 09:34:21" -> "2017-06-15"
  1. DATE difference
# MySQL  DATEDIFF: Return the number of *days* between two date values:
SELECT DATEDIFF('2017/08/25', '2011/08/25');
SELECT DATEDIFF('2017/08/25', '2011/08/25');
SELECT DATEDIFF('2017/08/25', '2011/08/25');

# TIMEDIFF : Return the difference between two time expressions:
SELECT TIMEDIFF("2017-06-25 13:10:11", "2017-06-15 13:10:10");
  1. Current Date
# MySQL
SELECT CURRENT_DATE(); # retrun current date: "YYYY-MM-DD"
SELECT CURRENT_TIMESTAMP(); # return current date and time: "YYYY-MM-DD HH-MM-SS"
  1. Reformat date
# MySQL
SELECT DATE_FORMAT("2017-06-15", "%Y"); # -> 2017
SELECT DATE_FORMAT("2017-06-15", "%Y-%m"); # -> 2017-06
  1. Get specific infor from date
# MySQL

# (1). EXTRACT()

SELECT EXTRACT(MONTH FROM "2017-06-15"); # -> 6
SELECT EXTRACT(WEEK FROM "2017-06-15"); # -> 24
SELECT EXTRACT(YEAR_MONTH FROM "2017-06-15"); # -> 201706

# (2). WEEKDAY()
# The WEEKDAY() function returns the weekday number for a given date.
# Note: 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 
# 4 = Friday, 5 = Saturday, 6 = Sunday.
SELECT WEEKDAY("2017-06-15");

# (3). YEARWEEK()
SELECT YEARWEEK("2017-06-15"); # -> 201724
  1. Past N days
# MySQL

# (1). EXTRACT()

SELECT EXTRACT(MONTH FROM "2017-06-15"); # -> 6
SELECT EXTRACT(WEEK FROM "2017-06-15"); # -> 24
SELECT EXTRACT(YEAR_MONTH FROM "2017-06-15"); # -> 201706

# (2). WEEKDAY()
# The WEEKDAY() function returns the weekday number for a given date.
# Note: 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 
# 4 = Friday, 5 = Saturday, 6 = Sunday.
SELECT WEEKDAY("2017-06-15");

# (3). YEARWEEK()
SELECT YEARWEEK("2017-06-15"); -> 201724
  1. Use LEFT to reformat date
SELECT LEFT("2017-06-15", 7); # -> "2017-06"

# Sometimes the RIGHT() function can be helpful.
  1. unixtim
SELECT FROM_UNIXTIME(1255033470); # -> 2009-10-08 20:24:30
SELECT DATE(FROM_UNIXTIME(1255033470)); # -> 2009-10-08
Posted on:
October 28, 2021
Length:
2 minute read, 406 words
Categories:
Data Science
See Also: