DATETIME FUNCTIONS

Pain and joy: two sentiments I associate with handling dates and times using SQL. The pain is born out of the flavors of SQL and associated datetime functions offered by each relational database management system (RDBMS) and the ensuing time spent reviewing the corresponding product documentation. While most RDBMS products will allow you to achieve similar, if not the same thing, when working with dates and times, the function(s) used and/or strategy may differ. The joy is associated with the ease in which analyses requiring temporal dimensions can be done after enduring the documentation. Luckily, the pain is typically short and the joy, long-lasting.

Let’s explore a handful of Oracle’s datetime functions within the context of solving real-world problems. The schemas, corresponding tables, and data used throughout the examples can be found at livesql.oracle.com.

Please note that the following statements have been executed prior to running the code blocks within each example. These individual statements update the default date and timestamp formats.

Updating Session Parameters

ALTER SESSION
    SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

ALTER SESSION
    SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24.MI.SSXFF';

ALTER SESSION
    SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24.MI.SSXFF TZR';

Retrieving System Date and Time (Client/session and Server)
Having access to the current date, time, or both, can be extremely useful in solving real-world problems. Fortunately, retrieving these values is straightforward in SQL. Depending on which function is used, the value can represent the client’s (i.e., session) or the server’s date and/or time. Simply use the appropriate function to retrieve the desired value. In the code block below, the current date and time for both client and server, are retrieved. The query’s corresponding result set follows the code block.

SELECT
    CURRENT_DATE AS session_date,
    CURRENT_TIMESTAMP AS session_date_time,
	SYSDATE AS server_date,
    SYSTIMESTAMP AS server_date_time
FROM
	dual;

The example above illustrates how to use datetime functions to retrieve the current date and/or time; however, it lacks real-world application. Let’s look at an example applying these functions to solve a realistic task.

Imagine I sell my goods online and notice, while business is better than it’s ever been, there are some customers that do not place subsequent orders after their initial order. In an effort to better understand why, I could gather the full list of those customers, details of the orders placed, and set a time boundary, in terms of days since the first order was placed, that delineates customers possibly returning from those that are likely to never return. Of course, to make use of the list of customers, corresponding order details, and the boundary I’ve set, I’ll need to know how to calculate the amount of time elapsed since each of the customer’s first orders. This can be done by simply taking the difference between the SYSDATE value and the order date value for the corresponding order. When using Oracle, the value returned when the difference between two datetime values is computed is of interval data type. The interval contains the number of days, hours, minutes, seconds, and fractional seconds.

/*
Retrieve a list of customers having only placed a single order.
Subsequently, retrieve those customer's order details and compute 
the amount of time elapsed since the order was placed.
*/
WITH single_order_customer AS (
    SELECT
    	oe.orders.customer_id
    FROM
    	oe.orders
    GROUP BY
    	oe.orders.customer_id
    HAVING
    	COUNT(*) = 1
)
SELECT
	oe.orders.customer_id,
    oe.orders.order_id,
    SYSDATE - oe.orders.order_date AS time_elapsed,
    oe.orders.order_mode,
    oe.orders.order_total,
    oe.orders.sales_rep_id
FROM
	oe.orders
    INNER JOIN
    single_order_customer
    	ON oe.orders.customer_id = single_order_customer.customer_id
ORDER BY
    time_elapsed DESC;

As a next step, the time boundary could be implemented as a filter within the query to limit your data to only those corresponding to customers with an amount of time since placing their first order exceeding some value (e.g., 45 days). I’ll do just that after exploring the function used to extract portions of the interval values returned from the query above and datetime values, in general.

Extracting Date Parts
Often, reporting and analytics tasks will require data to be aggregated at a higher level of detail than it originally exists. For example, when sales are captured as individual transactions with a transaction date (e.g., 2023/13/06), it may be necessary to aggregate the sales data at the month or year level (i.e., sales by month, sales by year) due to the volume of transactions. Meaningful information may also be gleaned from analyzing sales at a lower level of detail. For example, suppose I own a coffee shop and wish to ensure my shop is adequately staffed at all times. I could look at sales at the hour level to understand when my coffee shop is typically busy and when it’s rather slow.

To easily handle these types of tasks, most RDBMS products provide the function, EXTRACT. This function permits the extracting of specific parts of a value of datetime data type (e.g., date, timestamp, etc.). Below is a simple example highlighting the functionality provided by EXTRACT. In short, for order 2458, the order date is broken down into its individual parts (e.g., year, month, day, etc.). See the result set that follows for clarity.

SELECT
    oe.orders.order_id,
    oe.orders.order_date,
	EXTRACT(YEAR FROM oe.orders.order_date) AS order_year,
	EXTRACT(MONTH FROM oe.orders.order_date) AS order_month,
	EXTRACT(DAY FROM oe.orders.order_date) AS order_day,
	EXTRACT(HOUR FROM oe.orders.order_date) AS order_hour,
	EXTRACT(MINUTE FROM oe.orders.order_date) AS order_minute,
	EXTRACT(SECOND FROM oe.orders.order_date) AS order_second
FROM
	oe.orders
WHERE
    oe.orders.order_id = 2458;

While the example above tackles extracting the parts of a datetime value and representing each of those parts as an individual column, I find it more common to use specific date parts for aggregation purposes. As previously mentioned, sales could be aggregated at the year level. The example below retrieves all orders and groups them by year, returning the order year and total sales value corresponding to each year’s orders. The same code could easily be adjusted to calculate sales at a different level of detail by adjusting the date part used in the EXTRACT function.

SELECT
	EXTRACT(YEAR FROM oe.orders.order_date) AS order_year,
    SUM(oe.orders.order_total) AS total_sales
FROM
	oe.orders
GROUP BY
	EXTRACT(YEAR FROM oe.orders.order_date)
ORDER BY
	order_year;

Truncating Datetime Values
In the previous section, EXTRACT was used to pull out specific parts of a datetime value and to represent those parts in individual columns. When EXTRACT is used, it returns the numeric representation of the specified part. That’s typically the desired result; however, there are instances where it’s useful to maintain the datetime data type of the original value. For example, recall the query above returning sales by year. This example can be extended to capturing sales by year and month by simply adding an additional column containing the extracted month value and updating the GROUP BY clause to reflect the newly added column. While the result set does technically contain sales by year and month, year and month values are spread across two columns. If the goal was to achieve the same result set, but with the year and month values represented in a single column, then there are a couple of options that come to mind. Concatenate the extraction of year and month values along with a constant placeholder for the first of the month or truncate the original datetime values to the month portion using the truncate function, TRUNC. I’ll focus on the latter.

TRUNC allows a user to represent a datetime value with a specified portion of the value truncated. In the example below, the server’s date and time value is captured (i.e. SYSDATE). Additionally, this same value is used as an argument to the TRUNC function and the desired unit or portion of the date to truncate to is specified. Passing the year unit to this function will return the date value corresponding to the first of the year for the date value passed. As seen in the example’s corresponding result set, using the date, 2023-06-17, and specifying to truncate to the year unit yields 2023-01-01. Similarly, when specifying to truncate to the month unit, the function will return the date value corresponding to the first of the month for the date value passed (i.e., 2023-06-01). Truncating to the month unit can be useful when it’s necessary to aggregate a measure by year and month. Again, think about the previous example where the EXTRACT function was used to represent these date parts across two columns.

In the fourth column of the example code below, notice the absence of a date part or unit argument for the TRUNC function. When a unit is not provided, the default value of DAY is used. Truncating to the day portion of the date value passed will strip the value of its time parts. For clarity, review the final three columns in the code example below and the corresponding result set.

SELECT
    SYSDATE,
	TRUNC(SYSDATE, 'YEAR') AS first_of_the_year,
    TRUNC(SYSDATE, 'MONTH') AS first_of_the_month,
    SYSTIMESTAMP AS server_date_time,
    TRUNC(SYSTIMESTAMP) AS server_date,
    TO_TIMESTAMP(TRUNC(SYSTIMESTAMP)) AS beginning_of_the_day
FROM
	dual;

Date Arithmetic
The need for adding and subtracting dates is all but guaranteed in analytics tasks. Questions, such as how long has it been since a particular event, how much time elapsed between two events, and what is the average or median time elapsed between two particular types of events over time are examples of information commonly sought after.

Answering these questions can be straightforward with a modicum of understanding as it relates to each RDBMS handling of date arithmetic. Using Oracle database, a given number of days and a datetime value can be added by simply using the addition operator, “+.” This is because the default interval for date arithmetic is days. For example, if there is a pre-existing column, “order_date,” and the goal is the compute or return the datetime value associated with seven days after the order date value, then the following expression will achieve that.

order_date + 7

Similarly, days can be subtracted from the same pre-existing column by using the subtraction operator, “-.”

When intervals aside from days are needed (e.g., weeks, months, years, etc.), we can take advantage of Oracle’s interval expressions and/or built-in functions. Let’s explore interval expressions and built-in functions by examining a series of real-world problems.

Employee Milestone Dates
In this problem, there exists a table containing employee information. Within this table are various attributes describing the employee; however, for the moment, consider two attributes, employee ID and hire date. The employee ID uniquely identifies an employee while the hire date refers to the date on which the associated employee was hired. The task here is to enrich the data by deriving various new attributes. These attributes are described below.

Benefits eligible date
The date the employee becomes eligible for benefits. This occurs 30 days after the employee is hired.

Initial review data (90-day review)
The date the employee’s initial review takes place. This occurs 90 days after the employee is hired.

One-year anniversary
The date of the employee’s one year anniversary. An employee’s anniversary date falls on the same calendar day each year. The manager should receive a notification of their employee’s upcoming one year anniversary seven days prior.

Five-year anniversary
The date of the employee’s five year anniversary.

The example solution and corresponding result set below highlights one way this task can be achieved. The default interval expression, days, is used to compute the benefits eligibility date and the ninety-day review date for the employee with an ID of 100. The ADD_MONTHS function is used to determine the employee’s one year anniversary date, the date for which their manager should receive a notification of the upcoming anniversary, and the employee’s five-year anniversary date. The function is quite straightforward, accepting two arguments. This first argument is the date to add months to and the second, the number of months to add.

SELECT
    hr.employees.employee_id,
    hr.employees.hire_date,
    hr.employees.hire_date + 30 AS benefits_eligible_date,
    hr.employees.hire_date + 90 AS ninety_day_review,
    ADD_MONTHS(hr.employees.hire_date, 12) AS one_year_anniversary,
    ADD_MONTHS(hr.employees.hire_date, 12) - 7 AS manager_email_notice,
    ADD_MONTHS(hr.employees.hire_date, 60) AS five_year_anniversay
FROM
	hr.employees
WHERE
    hr.employees.employee_id = 100;

Although the example above uses the ADD_MONTHS function in deriving the final three attributes, it may make more sense to use interval expressions when the number of months being added is greater than eleven and can be expressed in whole years (e.g., twelve for one, twenty-four for two, sixty for five, etc.). This is because the reader of the code, whether that be someone else or your future self, may be able to understand it more quickly. The following code will yield the same result set above.

SELECT
    hr.employees.employee_id,
    hr.employees.hire_date,
    hr.employees.hire_date + 30 AS benefits_eligible_date,
    hr.employees.hire_date + 90 AS ninety_day_review,
	hr.employees.hire_date + INTERVAL '1' YEAR AS one_year_anniversary,
	hr.employees.hire_date + INTERVAL '1' YEAR - 7 AS manager_email_notice,
	hr.employees.hire_date + INTERVAL '5' YEAR AS five_year_anniversay
FROM
	hr.employees
WHERE
    hr.employees.employee_id = 100;

For the three real-world problems that follow, the orders table below will be used.

Computing Days Elapsed
The need to determine the amount of time elapsed since a particular event is common. Imagine your manager requires a report indicating, for each customer, the date of the customer’s most recent order and the number of whole days elapsed since that date.

The following coding example utilizes previous learnings to generate the desired report. The day value is extracted from interval expression resulting from the difference between the current system date and the date associated with the customer’s most recent order.

SELECT
    oe.orders.customer_id,
    MAX(oe.orders.order_date) AS most_recent_order,
    EXTRACT(DAY FROM (SYSDATE - MAX(oe.orders.order_date))) AS days_since_last_order
FROM
	oe.orders
GROUP BY
	oe.orders.customer_id;

Computing Months Elapsed
Like the task above, the need is to compute the amount of time elapsed. However, for this task, the number of months elapsed between each employee’s hire date and their manager’s hire date is of interest. Have managers been with the company longer than their direct reports or are there employees reporting to managers with less time at the company? How much more or less time have managers been at the company compared to each of their employees? Being able to answer those two questions is the initial goal.

In the example code below, the MONTHS_BETWEEN function is used. What does the function do? You guessed it, it calculates the number of months between two datetime values provided. The ROUND function is wrapped around the MONTHS_BETWEEN function to round the number of months elapsed to two decimal places. More information on the ROUND function and other numeric functions, with real-world examples, can be found on my numeric functions page.

Please note only the first five rows are shown in the result set.

SELECT
    employee.employee_id,
    manager.employee_id AS manager_id,
    employee.hire_date AS employee_hire_date,
    manager.hire_date AS manager_hire_date,
    ROUND(
    	MONTHS_BETWEEN(
        	manager.hire_date,
        	employee.hire_date
    	),
    	2
    ) AS months_elapsed
FROM
	hr.employees employee
    LEFT JOIN
    hr.employees manager
    	ON employee.manager_id = manager.employee_id
FETCH FIRST 5 ROWS ONLY;

Retrieving the Last Day and Second of the Month
In this final problem, imagine a company guarantees every order placed by customers to be shipped in the same month as the month the order is placed. As a result, management considers proper order tracking crucial to maintaining this promise to its customers. A report outlining each order, the ship by date and time, and the amount of time remaining until the order must be shipped is required.

From an SQL perspective, this problem boils down to being able to derive the datetime value associated with the last second of the last day of each order’s order month. With that value for each order, the difference between it and each order’s order date can be computed to determine the amount of time remaining for the order to be shipped.

Many of the concepts previously covered are incorporated in the example code below. Focusing on the time remaining attribute, the LAST_DAY function is used to determine the last day of each order’s order month. This value is then truncated to the day portion, stripping away the hours, minutes, seconds, and fractional seconds date parts. This ensures when a day is added to the resulting truncated date the first of the following month is returned. Subtracting one second from the first of the month for the month following each order’s order date returns the last second of the month for which each order was placed. Finally, the difference between the datetime value associated with the last second in each order’s order month and the original order date value is computed. This difference is returned as an interval value. In the next section, I’ll revisit how to extract each of the resulting interval’s parts with a fun twist, but for now, see the result set following the example code to make sense of the corresponding output. The ship by date and ship by date and time have been included in the output for clarity.

Please note only the first five rows are shown in the result set.

SELECT
	oe.orders.order_id,
    oe.orders.order_date,
    LAST_DAY(oe.orders.order_date) AS ship_by_date,
	TO_TIMESTAMP(
        (
        	TRUNC(LAST_DAY(oe.orders.order_date))
        	+ 1
        	- INTERVAL '1' SECOND
        )
    ) AS ship_by_date_time,
    (
    	TRUNC(LAST_DAY(oe.orders.order_date))
    	+ 1
    	- INTERVAL '1' SECOND
    	-  oe.orders.order_date
    ) AS time_remaining
FROM
	oe.orders
FETCH FIRST 5 ROWS ONLY;

Intervals as a Plain English String
In the Extracting Date Parts section of this page, I covered Oracle’s EXTRACT function with a couple of examples. In each of the examples, the focus was on extracting a portion or part of a datetime value and representing it as a new attribute. But what if we want to translate interval values, such as those found within the time remaining attribute in the output above, to plain English? The EXTRACT function combined with somewhat clever concatenation can achieve this while maintaining the values in a single column.

I’ve taken the example code written above and expressed it as a common table expression (CTE) the code below. If you’re unfamiliar with common table expressions, then it may be helpful to read my page containing a high-level overview on the topic, with supporting examples, here. From the shipping expectation CTE, each attribute is selected, and an additional one is derived. This newly derived attribute concatenates the numeric values associated with each date part within the original interval value and the corresponding plain English unit (e.g., days, hours). The resulting string resembles a countdown timer. Neat! See the result set that immediately follows the code for clarity.

WITH shipping_expectation AS (
    SELECT
    	oe.orders.order_id,
        oe.orders.order_date,
        TRUNC(LAST_DAY(oe.orders.order_date)) AS ship_by_date,
    	TO_TIMESTAMP(
            (
            	TRUNC(LAST_DAY(oe.orders.order_date))
            	+ 1
            	- INTERVAL '1' SECOND
            )
        ) AS ship_by_date_time,
        (
        	TRUNC(LAST_DAY(oe.orders.order_date))
        	+ 1
        	- INTERVAL '1' SECOND
        	-  oe.orders.order_date
        ) AS time_remaining
    FROM
    	oe.orders
    FETCH FIRST 5 ROWS ONLY
)
SELECT
    shipping_expectation.order_id,
    shipping_expectation.order_date,
    shipping_expectation.ship_by_date,
    shipping_expectation.ship_by_date_time,
    shipping_expectation.time_remaining,
	(
    	CAST(EXTRACT(DAY FROM shipping_expectation.time_remaining) AS VARCHAR2(255))
    	|| ' days, '
    	|| CAST(EXTRACT(HOUR FROM shipping_expectation.time_remaining) AS VARCHAR2(2))
    	|| ' hours, '
    	|| CAST(EXTRACT(MINUTE FROM shipping_expectation.time_remaining) AS VARCHAR2(2))
    	|| ' minutes, '
    	|| CAST(EXTRACT(SECOND FROM shipping_expectation.time_remaining) AS VARCHAR2(2))
    	|| ' seconds'
    ) AS time_remaining_string
FROM
    shipping_expectation;