In this module, we explore the various date functions provided by PostgreSQL to manipulate and format date data. This is crucial when dealing with any time series data, such as sales records, user activity logs, or any other date-stamped information. In this module, we will try to cover the most common date functions and their use cases. Some of these date functions might look overwhelming but trust us you don’t need to remember most of these (Even most of the experienced analyst remember the most common ones only). You can bookmark this page to refer back to based on your use case or you can always google if you prefer that. We however have collated some really useful tha:)
1. Extracting Date Parts:
PostgreSQL provides the EXTRACT() function to retrieve specific parts from a date, such as year, month, or day.
Sample Question: How do you find out how many orders were placed in the year 2023?
Example:
SELECT COUNT(order_id)
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023;
<aside>
💡 The EXTRACT function in SQL is used to retrieve a specific part of a date or time value. When you use EXTRACT(YEAR FROM order_date), you're asking the database to give you just the year component from the order_date column of your table.
For example, if order_date is '2023-11-04', EXTRACT(YEAR FROM order_date) will return 2023.
</aside>
2. Date Arithmetic:
Add or subtract intervals from a date to get a new date using the INTERVAL keyword.
Sample Question: What is the date 30 days after each order was placed?
Example:
SELECT order_id, order_date, order_date + INTERVAL '30 days' AS new_date
FROM orders;
<aside>
💡 The expression order_date + INTERVAL '30 days' in SQL adds 30 days to the date in the order_date column, resulting in a new date. The AS new_date part gives this resulting date a label or alias of new_date in the output.
Furthermore, the INTERVAL keyword is used to specify a duration of time. It can be used to add or subtract various time units to or from a date or timestamp, such as:
INTERVAL '1 year'INTERVAL '2 months'INTERVAL '10 days'INTERVAL '5 hours'INTERVAL '30 minutes'INTERVAL '45 seconds'
</aside>3. Date Truncation:
The DATE_TRUNC function truncates a date to the specified precision.
Sample Question: How can you group sales records by month regardless of the day?
Example:
SELECT DATE_TRUNC('month', order_date) AS month, SUM(total_amount) AS monthly_sales
FROM orders
GROUP BY month;
<aside>
💡 DATE_TRUNC('month', order_date) AS month truncates the order_date to the first day of that month and labels the result as month. This is used for grouping data by month without considering the exact day or time.
Additionally, DATE_TRUNC can be applied with different precisions like 'year', 'day', or 'hour', allowing aggregation of data at various levels of granularity.
</aside>
4. Date Formatting:
Format dates using the TO_CHAR() function.
Sample Question: How do you display order dates in the format "Day, Month DD, YYYY"?
Example:
SELECT order_id, TO_CHAR(order_date, 'Day, Month DD, YYYY') AS formatted_date
FROM orders;
<aside>
💡 TO_CHAR(order_date, 'Day, Month DD, YYYY') AS formatted_date converts the order_date to a string with a format that includes the full name of the weekday, the full name of the month, the day of the month in two digits, and the four-digit year, labeling the result as formatted_date. This function is commonly used for displaying dates in a more readable or report-friendly format.
For example, if order_date is '2023-11-04', the TO_CHAR function would output 'Saturday, November 04, 2023' as formatted_date.
</aside>