This note is designed for my personal use; it’s not a comprehensive SQL cheat sheet, as I only record the functions/codes that I think are worthy

Date

Current Date and Time

To get the current date and time, most SQL databases provide functions like NOW(), CURRENT_DATE, and CURRENT_TIMESTAMP.

  • SELECT CURRENT_DATE
    • Returns the current date
  • SELECT CURRENT_TIMESTAMP
    • Returns the current date and time

Extracting Date Parts

You can extract specific parts of a date (like year, month, day, hour, etc.) using the EXTRACT() function or specific functions like YEAR(), MONTH(), and DAY().

  • SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS Year
  • SELECT MONTH(CURRENT_DATE) AS Month
  • SELECT DAY(CURRENT_DATE) AS Day

Date Arithmetic/DATEDIFF

You can add or subtract days, weeks, months, or years to a date using functions like DATE_ADD or DATE_SUB, or by using interval arithmetic.

  • SELECT CURRENT_DATE + INTERVAL 1 DAY
    • Adds 1 day to the current date
  • SELECT CURRENT_DATE - INTERVAL 7 DAY
    • Subtracts 7 days from the current date
  • MySQL
    • SELECT DATEDIFF('2023-04-01', '2023-01-01') AS DaysBetween
      • Calculate the number of days between two dates
  • PostgreSQL
    • SELECT ‘2023-04-01’::date - ‘2023-01-01’::date AS DaysBetween
      • Calculate the number of days between two dates
    • SELECT AGE(‘2023-04-01’::date, ‘2023-01-01’::date) AS DateDifference
      • Calculate the precise age between two dates
  • SQL Server
    • SELECT DATEDIFF(day, ‘2023-01-01’, ‘2023-04-01’) AS DaysBetween
      • Calculate the number of days between two dates
    • SELECT DATEDIFF(month, ‘2023-01-01’, ‘2023-04-01’) AS MonthsBetween
      • Calculate the number of months between two dates
  • Oracle
    • SELECT (TO_DATE(‘2023-04-01’, ‘YYYY-MM-DD’) - TO_DATE(‘2023-01-01’, ‘YYYY-MM-DD’)) AS DaysBetween
      • Calculate the number of days between two dates

Formatting Dates

To format dates into a specific string format, you might use the DATE_FORMAT() function in MySQL or TO_CHAR() in PostgreSQL and Oracle.

  • MySQL
    • SELECT DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d')
  • PostgreSQL or Oracle
    • SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')

Comparing Dates

You can compare dates to filter records in a WHERE clause.

  • SELECT * FROM orders WHERE order_date > ‘2023-01-01’

  • SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31'

Date Conversion

Sometimes you might need to convert strings to dates or vice versa. Functions like STR_TO_DATE() in MySQL or TO_DATE() in PostgreSQL and Oracle can be used.

  • MySQL
    • SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d')
  • PostgreSQL or Oracle
    • SELECT TO_DATE('2023-01-01', 'YYYY-MM-DD')

Time Zone Conversions

Handling time zones can be crucial for applications spanning multiple regions.

  • PostgreSQL
    • SELECT CURRENT_TIMESTAMP AT TIME ZONE ‘UTC’
    • SELECT CURRENT_TIMESTAMP AT TIME ZONE ‘America/New_York’

GroupBy

In SQL, several standard aggregation functions are typically used with the GROUP BY clause to summarize data across different groups. These functions perform calculations on a set of values and return a single value. Here’s a list of the most commonly used aggregation functions that you can use with GROUP BY:

COUNT(), SUM(), AVG(),MAX(), MIN(),STDDEV(),STDDEV_POP(),VARIANCE(),VAR_POP()

  • SELECT category, COUNT(*) FROM products GROUP BY category
  • SELECT department, SUM(salary) FROM employees GROUP BY department
  • SELECT department, AVG(salary) FROM employees GROUP BY department
  • SELECT department, MAX(salary) FROM employees GROUP BY department
  • SELECT department, MIN(salary) FROM employees GROUP BY department
  • SELECT department, STDDEV(salary) FROM employees GROUP BY department
  • SELECT department, VARIANCE(salary) FROM employees GROUP BY department

GROUP_CONCAT() (MySQL/SQLite)

Concatenates values from a group into a single string with values separated by a delimiter. Not standard SQL but available in several SQL databases.

  • SELECT department, GROUP_CONCAT(employee_name SEPARATOR ', ') FROM employees GROUP BY department;

Additional Aggregation Considerations(Distinct, Over Paritions)

  • Distinct: Many aggregation functions allow you to specify DISTINCT to consider only distinct values within each group.
    • SELECT category, COUNT(DISTINCT product_name) FROM products GROUP BY category;
  • Over Partitions: While not used directly with GROUP BY, many aggregation functions can also be used with the OVER() clause to perform calculations across different partitions of data without collapsing the rows into a single output row per group.
    • SELECT department, salary, AVG(salary) OVER (PARTITION BY department) FROM employees;