Dates: Beginner

Why

Why "yesterday" is harder than you think

Category

SQL

Difficulty

beginner

Duration

26 minutes

Challenges

0 hands-on challenges

Topics covered: DATE vs TIMESTAMP data types, CURRENT_DATE & CURRENT_TIME, CURRENT_TIMESTAMP function, DATE_ADD(), DATE_DIFF()

Lesson Sections

1. DATE vs TIMESTAMP data types (concepts: sqlDateVsTimestamp)

   Date and Timestamp Literals SQL uses specific syntax to write date and timestamp values directly in queries. Date Literals The ISO 8601 format (YYYY-MM-DD) is universally supported. Always use this format for date literals to avoid ambiguity between American (MM/DD) and European (DD/MM) conventions. Timestamp Literals Comparison Date vs Timestamp Here is a side-by-side comparison of the two core temporal types. Which type would you use to track when users perform actions?

2. CURRENT_DATE & CURRENT_TIME (concepts: sqlCurrentDate)

   This function is your go-to tool for queries that need to reference "today" without hardcoding dates. CURRENT_DATE Usage Patterns Basic Usage The result reflects whatever date the query executes. Running this query tomorrow returns tomorrow's date. This dynamic behavior is essential for queries that need to stay current without manual updates. Filtering Recent Data This query finds all orders placed today. The filter automatically adjusts as days pass. Tomorrow, the same query returns only tomor

3. CURRENT_TIMESTAMP function

   Using CURRENT_TIMESTAMP Basic Usage Here is the simplest way to retrieve the current timestamp. The result captures the exact moment the query executed. This precision is essential for audit trails, event logging, and any situation where knowing the precise time matters. Practical Applications Recording Event Times Adding timestamps to records creates an audit trail of when events occurred. Each record gets the same timestamp within a single query. This is intentional, ensuring consistency withi

4. DATE_ADD() (concepts: sqlDateAdd)

   Adding Time Intervals Adding Days The most common use case is adding days to calculate due dates or deadlines: Different Time Units Subtracting Time Negative Amounts Use negative amounts to subtract time and look backward: Negative values move backward in time. This is essential for historical analysis, comparing year-over-year metrics, and calculating retention windows. Best Practices When to Use DATE_ADD() Here are the most common scenarios where date arithmetic is needed.

5. DATE_DIFF() (concepts: sqlDateDiff)

   Calculating Time Differences Calculating Days Between The most common use case is calculating the number of days between two events: Different Time Units Practical Applications Age Calculations One of the most common uses is calculating ages from birth dates. The year difference gives approximate age. For exact age that accounts for whether the birthday has passed this year, additional logic is needed, but year difference covers most use cases. SLA Metrics This query calculates how many hours ea