Now that we know how to identify data types, let’s apply date time SQL functions to datetime columns to manipulate the values inside. For the full list of datetime SQL functions available in PostgreSQL, please see the datetime SQL functions documentation page.
Note: for date time manipulations, there are always more than one way to achieve the same outcome. Documentation will be your best guide here.
Find all columns in the table employees that are date type columns.
Instead of manually hardcoding the current datetime, SQL has functions for this:
NOW(): Returns the current date and time.CURRENT_DATE: Returns the current date.Note that CURRENT_DATE is a special case where no parentheses are necessary to call the function. There are other variants in the suite of datetime functions starting with CURRENT_*.
Run the following and verify that the output is as expected.
SELECT *,
NOW() AS current_datetime,
CURRENT_DATE as current_date
FROM employees
LIMIT 10
;
Sometimes, what we need is a portion of the datetime data, such as only the year from a date column that stores data in the format 'YYYY-MM-DD'.
EXTRACT() and DATE_PART() perform the same action albeit with different syntax. Note that EXTRACT() is standard SQL, while DATE_PART() is an exclusively PostgreSQL function.
Note the difference in input format and usage for each function:
EXTRACT() requires the keyword FROM and needs the date interval without any quotation marks.DATE_PART() requires a comma as separator and needs the date interval within single quotation marks.SELECT
birth_date,
EXTRACT(year FROM birth_date) AS birth_year_v1,
DATE_PART('year', birth_date) AS birth_year_v2
FROM employees
LIMIT 10
;
For a full list of other date parts that could be extracted (e.g. 'dow', 'month'), refer to the documentation page.
EXTRACT(), create two columns birth_month and birth_day using the column birth_date from table employees.DATE_PART(), replicate the same result as question 1.It is possible to do arithmetic-like operations with date timefunctions, like so:
SELECT
birth_date + 365 AS one_yr_old_date_v1,
birth_date + interval '1 year' AS one_yr_old_date_v2
FROM employees
LIMIT 10
;
The AGE() function can calculate difference between two datetime columns. AGE() is an exclusively PostgreSQL function. In other SQL variants, the function is called DATE_DIFF().
SELECT
emp_no,
salary,
from_date,
to_date,
AGE(to_date, from_date) AS salary_tenure
FROM salaries
LIMIT 10
;
If only we only pass 1 input argument to AGE(), the function subtracts the input argument from the current date.
For example, AGE(from_date) is the equivalent of CURRENT_DATE - from_date.
Use the AGE() function to calculate how old the employees are, as of today’s date. Fill in the blank below.
SELECT
first_name,
last_name,
??? AS employee_age
FROM employees
LIMIT 10
;
There are many cases where you would want to convert one data type into another. For example, we would want to convert a date column into a string column so that we can apply a string SQL function to it.
To do data type conversion, you can use:
::Example
NOW() returns a column of datetime, but if we want to strip out the time part and only keep the date, then we need to convert it into a date only column. To do so, we can either use CAST() or :: to achieve the same results.
SELECT
NOW() AS current_datetime,
CAST(NOW() AS date) AS current_date_v1,
NOW()::date AS current_date_v2
;
Note that aside from the CAST() function, there are other functions that performs similar functions, such as TO_CHAR(), TO_STRING(), TO_DATE(), and TO_TIMESTAMP(). However, TO_* functions are specific to PostgreSQL and are not universally used like the CAST() function.
CAST() function instead of :: and produce the same result.
SELECT
NOW()::date AS current_date,
birth_date::date AS birth_date
FROM employees
LIMIT 10
;
EXTRACT() to extract the birth_year, birth_month, and birth_day, use CAST() to convert the column to text and then apply substring functions (e.g. LEFT, RIGHT, or SPLIT_PART) to break the birth date down into its parts.
SELECT
birth_date,
EXTRACT(year from birth_date) AS birth_year,
EXTRACT(month from birth_date) AS birth_month,
EXTRACT(day from birth_date) AS birth_day
FROM employees
LIMIT 10
;
The last category of SQL functions is called aggregation functions. Aggregate functions perform a calculation on a set of rows and return a single row that describes the data (e.g. summary statistics).
For the full list of aggregation SQL functions available in PostgreSQL, please see the aggregation functions documentation page.
The most common aggregate functions, standard across all SQL databases, are as follows:
AVG(): return the average value.MIN(): return the minimum value.MAX(): return the maximum value.SUM(): return the sum of all or distinct values.COUNT(): return the number of values.Example: Calculate the minimum, maximum, average, and total salary from the salaries table.
SELECT
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary,
SUM(salary) AS total_salary
FROM salaries
;
SUM() and COUNT() functions are frequently used to find the # of rows in a table.
COUNT() takes in either a column or the wildcard * in order to function. When taking in the wildcard *, it counts across all rows. When taking in a column name, it only counts the non null values in that column.SUM() takes in a temporarily created column with value 1 per row and then sums across it to calculate the number of rows in the table.Example:
SELECT
SUM(1) AS num_rows_v1,
COUNT(*) AS num_rows_v2
FROM employees
;
Note: because all aggregation functions return only 1 row, they cannot be used with other columns and/or non aggregation functions without the GROUP BY or HAVING clauses, which we will cover later.
Find the number of rows in the salaries table using SUM() or COUNT(). Verify this count against a SELECT * query against the table.