Lesson 8	Date and time functions
Objective	Identify valid time-related date/time functions in Oracle PL/SQL.

Oracle Date and Time Functions (Oracle 23ai)

Oracle databases—including Oracle 23ai—store date and time values using strongly typed temporal datatypes. The most commonly used is the DATE datatype, which always stores both a calendar date and a time component down to the second. If no time is explicitly supplied, Oracle assigns a default time of midnight (00:00:00).

Understanding how time is stored, formatted, compared, and manipulated is essential for writing correct and performant SQL and PL/SQL—especially in modern environments such as SQLcl and Oracle Cloud.

Time Formatting with TO_CHAR

The TO_CHAR function converts date and timestamp values into formatted text. Oracle provides dedicated format elements for hours, minutes, seconds, and meridian indicators.

Format Element	Description
HH or HH12	Hour (12-hour clock)
HH24	Hour (24-hour clock)
MI	Minutes
SS	Seconds
SSSSS	Seconds since midnight
AM / PM	Meridian indicator

Date Comparison Fundamentals

When Oracle compares two DATE values, it compares both the date and time components. This behavior is correct and intentional—but it can surprise developers who expect date-only comparisons.

Recommended Oracle 23ai Pattern: Half-Open Date Ranges


SELECT p.last_update_datetime
FROM   pet_care_log p
WHERE  p.last_update_datetime >= DATE '2025-12-13'
AND    p.last_update_datetime <  DATE '2025-12-14';

1) This query returns all rows whose LAST_UPDATE_DATETIME falls anywhere on December 13, 2025. Using a half-open range ensures that every time value on that day is included while preserving index usage.

Verifying the Time Component


SELECT TO_CHAR(p.last_update_datetime,
               'DD-MON-YYYY HH:MI PM') AS datetime
FROM   pet_care_log p
WHERE  p.last_update_datetime >= DATE '2025-12-12'
AND    p.last_update_datetime <  DATE '2025-12-13';

2) Formatting the output confirms that the stored value for December 12 includes a time of 5:30 PM, not midnight.

Why Equality Comparisons Often Fail


SELECT TO_CHAR(p.last_update_datetime,
               'DD-MON-YYYY HH:MI PM') AS datetime
FROM   pet_care_log p
WHERE  p.last_update_datetime = DATE '2025-12-12';

3) This query returns no rows because the literal date represents midnight (2025-12-12 00:00:00), while the stored row contains a non-midnight time.

Using TRUNC (Correct but Index-Unsafe)


SELECT TO_CHAR(p.last_update_datetime,
               'DD-MON-YYYY HH:MI PM') AS datetime
FROM   pet_care_log p
WHERE  TRUNC(p.last_update_datetime) = DATE '2025-12-12';

4) TRUNC removes the time portion before comparison, allowing the row to match. However, this approach disables normal index usage and should be avoided in high-volume queries.

Best Practices for Comparing Dates in Oracle 23ai

Always compare DATE to DATE datatypes.
Use ANSI DATE literals (DATE 'YYYY-MM-DD').
Avoid implicit string-to-date conversions.
Prefer half-open ranges over BETWEEN.
Avoid wrapping indexed columns in functions.

Time Arithmetic

Oracle allows arithmetic on DATE values using fractions of a day.

Unit	Fraction of a Day
Hour	1 / 24
Minute	1 / 1440
Second	1 / 86400


SELECT 
  TO_CHAR(p.last_update_datetime,
          'DD-MON-YYYY HH:MI PM') AS orig,
  TO_CHAR(p.last_update_datetime + (8/24),
          'DD-MON-YYYY HH:MI PM') AS later
FROM pet_care_log p;

Summary

Oracle 23ai date and time handling is precise, predictable, and powerful when used correctly. By relying on explicit datatypes, ANSI literals, and range-based comparisons, you ensure correctness, performance, and long-term maintainability across SQLcl, Oracle Cloud, and PL/SQL applications.