Blog/SQL Learning Tips/Date Time Functions

Date and Time Functions Every Analyst Should Know

November 20, 20249 min read
Date FunctionsTime SeriesAnalytics

Date and time analysis is fundamental to product analytics. From cohort analysis to seasonal trends, mastering these SQL functions will unlock powerful insights about user behavior, business cycles, and temporal patterns in your data.

Essential Date Extraction Functions

EXTRACT and Date Parts

The EXTRACT function is your go-to tool for pulling specific components from dates and timestamps. It's standardized across most SQL databases.

-- Essential date extraction functions
SELECT 
  order_date,
  EXTRACT(YEAR FROM order_date) as order_year,
  EXTRACT(MONTH FROM order_date) as order_month,
  EXTRACT(DAY FROM order_date) as order_day,
  EXTRACT(DOW FROM order_date) as day_of_week,  -- 0=Sunday, 6=Saturday
  EXTRACT(WEEK FROM order_date) as week_number,
  EXTRACT(QUARTER FROM order_date) as quarter
FROM orders
WHERE order_date >= '2024-01-01';

-- Alternative syntax (database-specific)
SELECT 
  order_date,
  YEAR(order_date) as order_year,           -- MySQL, SQL Server
  MONTH(order_date) as order_month,         -- MySQL, SQL Server
  DAYOFWEEK(order_date) as day_of_week,     -- MySQL
  DATEPART(quarter, order_date) as quarter -- SQL Server
FROM orders;

Key EXTRACT components:

  • YEAR, MONTH, DAY: Basic date components
  • DOW (Day of Week): 0=Sunday, 6=Saturday
  • WEEK: Week number within the year
  • QUARTER: 1-4 for business quarters
  • HOUR, MINUTE, SECOND: Time components

DATE_TRUNC: Grouping by Time Periods

Time-Based Aggregations

DATE_TRUNC rounds dates down to specified time periods, perfect for creating time-based reports and dashboards.

-- DATE_TRUNC for time period grouping
SELECT 
  DATE_TRUNC('month', order_date) as order_month,
  COUNT(*) as orders_count,
  SUM(order_amount) as total_revenue,
  AVG(order_amount) as avg_order_value
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

-- Daily, weekly, and quarterly aggregations
SELECT 
  DATE_TRUNC('day', created_at) as signup_date,
  COUNT(*) as daily_signups
FROM users
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY signup_date;

SELECT 
  DATE_TRUNC('week', order_date) as week_start,
  COUNT(*) as weekly_orders
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY week_start;

Common DATE_TRUNC periods:

  • 'day': Daily metrics and trends
  • 'week': Weekly performance (starts Monday)
  • 'month': Monthly reporting periods
  • 'quarter': Business quarter analysis
  • 'year': Annual comparisons

Date Arithmetic and Intervals

Calculating Time Differences

Date arithmetic allows you to calculate time differences, add/subtract periods, and perform temporal calculations essential for user journey analysis.

-- Date arithmetic and calculations
SELECT 
  user_id,
  signup_date,
  first_order_date,
  -- Days between signup and first order
  first_order_date - signup_date as days_to_first_order,
  -- Alternative syntax
  DATE_DIFF('day', signup_date, first_order_date) as days_diff,
  -- Age calculations
  CURRENT_DATE - signup_date as days_since_signup,
  EXTRACT(YEAR FROM AGE(CURRENT_DATE, signup_date)) as years_as_customer
FROM (
  SELECT 
    u.user_id,
    u.signup_date,
    MIN(o.order_date) as first_order_date
  FROM users u
  LEFT JOIN orders o ON u.user_id = o.customer_id
  GROUP BY u.user_id, u.signup_date
) user_timeline;

-- Adding and subtracting intervals
SELECT 
  CURRENT_DATE as today,
  CURRENT_DATE + INTERVAL '30 days' as thirty_days_later,
  CURRENT_DATE - INTERVAL '1 year' as one_year_ago,
  CURRENT_DATE + INTERVAL '3 months' as quarter_end,
  -- Business day calculations
  CURRENT_DATE + INTERVAL '5 days' as five_days_later
FROM dual;

Common date arithmetic patterns:

  • Conversion time: Days from signup to first purchase
  • Customer age: Time since registration
  • Activity recency: Days since last action
  • Retention windows: 30-day, 90-day analysis periods

Cohort Analysis with Date Functions

User Retention Analysis

Cohort analysis tracks user behavior over time by grouping users by their signup period. This is fundamental for understanding retention and lifecycle patterns.

-- Cohort analysis using date functions
WITH user_cohorts AS (
  SELECT 
    user_id,
    DATE_TRUNC('month', signup_date) as signup_month,
    signup_date
  FROM users
  WHERE signup_date >= '2024-01-01'
),
user_orders AS (
  SELECT 
    o.customer_id as user_id,
    o.order_date,
    DATE_TRUNC('month', o.order_date) as order_month
  FROM orders o
  WHERE o.order_date >= '2024-01-01'
),
cohort_data AS (
  SELECT 
    uc.signup_month,
    uo.order_month,
    -- Calculate months since signup
    EXTRACT(YEAR FROM uo.order_month) * 12 + EXTRACT(MONTH FROM uo.order_month) - 
    (EXTRACT(YEAR FROM uc.signup_month) * 12 + EXTRACT(MONTH FROM uc.signup_month)) as months_since_signup,
    COUNT(DISTINCT uo.user_id) as active_users
  FROM user_cohorts uc
  JOIN user_orders uo ON uc.user_id = uo.user_id
  GROUP BY uc.signup_month, uo.order_month
),
cohort_sizes AS (
  SELECT 
    signup_month,
    COUNT(DISTINCT user_id) as cohort_size
  FROM user_cohorts
  GROUP BY signup_month
)
SELECT 
  cd.signup_month,
  cd.months_since_signup,
  cd.active_users,
  cs.cohort_size,
  ROUND(cd.active_users * 100.0 / cs.cohort_size, 2) as retention_rate
FROM cohort_data cd
JOIN cohort_sizes cs ON cd.signup_month = cs.signup_month
WHERE cd.months_since_signup <= 12  -- First year retention
ORDER BY cd.signup_month, cd.months_since_signup;

Cohort analysis applications:

  • Retention rates: How many users return after signup
  • Feature adoption: Usage patterns over time
  • Revenue cohorts: Customer lifetime value by acquisition period
  • Product changes impact: Before/after retention comparison

Seasonal and Cyclical Analysis

Identifying Patterns and Trends

Understanding seasonal patterns helps with forecasting, resource planning, and identifying business opportunities.

-- Seasonal and cyclical analysis
-- Day of week patterns
SELECT 
  EXTRACT(DOW FROM order_date) as day_of_week,
  CASE EXTRACT(DOW FROM order_date)
    WHEN 0 THEN 'Sunday'
    WHEN 1 THEN 'Monday'
    WHEN 2 THEN 'Tuesday'
    WHEN 3 THEN 'Wednesday'
    WHEN 4 THEN 'Thursday'
    WHEN 5 THEN 'Friday'
    WHEN 6 THEN 'Saturday'
  END as day_name,
  COUNT(*) as order_count,
  AVG(order_amount) as avg_order_amount,
  SUM(order_amount) as total_revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY EXTRACT(DOW FROM order_date)
ORDER BY day_of_week;

-- Monthly seasonality
SELECT 
  EXTRACT(MONTH FROM order_date) as month_num,
  TO_CHAR(DATE_TRUNC('month', order_date), 'Month') as month_name,
  COUNT(*) as order_count,
  SUM(order_amount) as total_revenue,
  -- Year-over-year comparison
  LAG(COUNT(*)) OVER (ORDER BY EXTRACT(MONTH FROM order_date)) as prev_year_orders,
  LAG(SUM(order_amount)) OVER (ORDER BY EXTRACT(MONTH FROM order_date)) as prev_year_revenue
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY EXTRACT(MONTH FROM order_date), DATE_TRUNC('month', order_date)
ORDER BY month_num;

-- Holiday impact analysis
SELECT 
  order_date,
  COUNT(*) as order_count,
  CASE 
    WHEN EXTRACT(MONTH FROM order_date) = 12 AND EXTRACT(DAY FROM order_date) = 25 THEN 'Christmas'
    WHEN EXTRACT(MONTH FROM order_date) = 1 AND EXTRACT(DAY FROM order_date) = 1 THEN 'New Year'
    WHEN EXTRACT(MONTH FROM order_date) = 7 AND EXTRACT(DAY FROM order_date) = 4 THEN 'July 4th'
    WHEN EXTRACT(MONTH FROM order_date) = 11 AND EXTRACT(DOW FROM order_date) = 4 
         AND EXTRACT(DAY FROM order_date) BETWEEN 22 AND 28 THEN 'Thanksgiving'
    ELSE 'Regular Day'
  END as day_type
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY order_date
ORDER BY order_date;

Seasonal analysis insights:

  • Weekly patterns: Peak usage days and times
  • Monthly trends: Seasonal business cycles
  • Holiday impact: Special event effects on metrics
  • Year-over-year: Growth trends and comparisons

Timezone Handling

Global User Analysis

For applications with global users, proper timezone handling is crucial for accurate temporal analysis.

-- Timezone handling and conversions
-- Convert UTC to different timezones
SELECT 
  event_timestamp,
  event_timestamp AT TIME ZONE 'UTC' as utc_time,
  event_timestamp AT TIME ZONE 'America/New_York' as est_time,
  event_timestamp AT TIME ZONE 'America/Los_Angeles' as pst_time,
  event_timestamp AT TIME ZONE 'Europe/London' as london_time,
  event_timestamp AT TIME ZONE 'Asia/Tokyo' as tokyo_time
FROM user_events
WHERE event_timestamp >= CURRENT_TIMESTAMP - INTERVAL '24 hours';

-- Analyze user activity by local time zones
WITH user_timezones AS (
  SELECT 
    user_id,
    COALESCE(timezone, 'UTC') as user_timezone
  FROM users
),
localized_events AS (
  SELECT 
    ue.user_id,
    ue.event_timestamp,
    ut.user_timezone,
    (ue.event_timestamp AT TIME ZONE 'UTC' AT TIME ZONE ut.user_timezone) as local_time
  FROM user_events ue
  JOIN user_timezones ut ON ue.user_id = ut.user_id
  WHERE ue.event_timestamp >= CURRENT_TIMESTAMP - INTERVAL '7 days'
)
SELECT 
  EXTRACT(HOUR FROM local_time) as local_hour,
  user_timezone,
  COUNT(*) as event_count
FROM localized_events
GROUP BY EXTRACT(HOUR FROM local_time), user_timezone
ORDER BY user_timezone, local_hour;

Timezone considerations:

  • Store in UTC: Always store timestamps in UTC
  • Convert for analysis: Convert to local time for user behavior analysis
  • Business hours: Analyze activity during local business hours
  • Global metrics: Use consistent timezone for company-wide reporting

Business Days and Working Hours

B2B Analytics Considerations

For B2B products, excluding weekends and holidays from calculations provides more accurate business metrics.

-- Business days and working hours analysis
-- Exclude weekends from calculations
WITH business_days_only AS (
  SELECT 
    order_date,
    order_amount,
    EXTRACT(DOW FROM order_date) as day_of_week
  FROM orders
  WHERE EXTRACT(DOW FROM order_date) BETWEEN 1 AND 5  -- Monday to Friday
    AND order_date >= '2024-01-01'
),
-- Calculate business days between dates
business_day_calculations AS (
  SELECT 
    user_id,
    signup_date,
    first_order_date,
    first_order_date - signup_date as total_days,
    -- Approximate business days (excludes weekends only)
    CASE 
      WHEN first_order_date - signup_date <= 0 THEN 0
      ELSE 
        (first_order_date - signup_date) - 
        (EXTRACT(WEEK FROM first_order_date) - EXTRACT(WEEK FROM signup_date)) * 2
    END as approx_business_days
  FROM (
    SELECT 
      u.user_id,
      u.signup_date,
      MIN(o.order_date) as first_order_date
    FROM users u
    LEFT JOIN orders o ON u.user_id = o.customer_id
    WHERE u.signup_date >= '2024-01-01'
    GROUP BY u.user_id, u.signup_date
  ) user_data
  WHERE first_order_date IS NOT NULL
)
SELECT 
  CASE 
    WHEN approx_business_days = 0 THEN 'Same Day'
    WHEN approx_business_days <= 1 THEN '1 Business Day'
    WHEN approx_business_days <= 5 THEN '2-5 Business Days'
    WHEN approx_business_days <= 10 THEN '6-10 Business Days'
    ELSE '10+ Business Days'
  END as conversion_timeframe,
  COUNT(*) as user_count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as percentage
FROM business_day_calculations
GROUP BY 
  CASE 
    WHEN approx_business_days = 0 THEN 'Same Day'
    WHEN approx_business_days <= 1 THEN '1 Business Day'
    WHEN approx_business_days <= 5 THEN '2-5 Business Days'
    WHEN approx_business_days <= 10 THEN '6-10 Business Days'
    ELSE '10+ Business Days'
  END
ORDER BY MIN(approx_business_days);

Business day applications:

  • Response times: Customer support SLAs
  • Sales cycles: B2B conversion timeframes
  • Trial periods: Exclude non-working days
  • Campaign analysis: Weekday vs weekend performance

Time Series Analysis

Trend Analysis and Gap Filling

Complete time series analysis requires handling missing dates and calculating moving averages for trend identification.

-- Time series analysis and trends
-- Generate complete date series (no gaps)
WITH date_series AS (
  SELECT generate_series(
    '2024-01-01'::date,
    '2024-12-31'::date,
    '1 day'::interval
  )::date as date
),
daily_metrics AS (
  SELECT 
    DATE(order_date) as order_date,
    COUNT(*) as daily_orders,
    SUM(order_amount) as daily_revenue,
    COUNT(DISTINCT customer_id) as daily_customers
  FROM orders
  WHERE order_date >= '2024-01-01'
  GROUP BY DATE(order_date)
),
complete_daily_data AS (
  SELECT 
    ds.date,
    COALESCE(dm.daily_orders, 0) as daily_orders,
    COALESCE(dm.daily_revenue, 0) as daily_revenue,
    COALESCE(dm.daily_customers, 0) as daily_customers
  FROM date_series ds
  LEFT JOIN daily_metrics dm ON ds.date = dm.order_date
)
SELECT 
  date,
  daily_orders,
  daily_revenue,
  daily_customers,
  -- Moving averages
  AVG(daily_orders) OVER (
    ORDER BY date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as seven_day_avg_orders,
  AVG(daily_revenue) OVER (
    ORDER BY date 
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) as thirty_day_avg_revenue,
  -- Week-over-week comparison
  LAG(daily_orders, 7) OVER (ORDER BY date) as orders_week_ago,
  ROUND(
    (daily_orders - LAG(daily_orders, 7) OVER (ORDER BY date)) * 100.0 / 
    NULLIF(LAG(daily_orders, 7) OVER (ORDER BY date), 0), 2
  ) as week_over_week_growth,
  -- Month-over-month comparison
  LAG(daily_orders, 30) OVER (ORDER BY date) as orders_month_ago
FROM complete_daily_data
WHERE date >= '2024-02-01'  -- Allow for lookback periods
ORDER BY date;

Time series techniques:

  • Gap filling: Include zero values for missing dates
  • Moving averages: Smooth out daily volatility
  • Period comparisons: Week-over-week, month-over-month
  • Trend identification: Growth rates and direction

Churn Analysis with Dates

Customer Lifecycle Management

Date-based churn analysis helps identify at-risk customers and understand customer lifecycle patterns.

-- Churn analysis using date functions
WITH user_activity AS (
  SELECT 
    customer_id,
    MAX(order_date) as last_order_date,
    MIN(order_date) as first_order_date,
    COUNT(*) as total_orders,
    SUM(order_amount) as lifetime_value
  FROM orders
  GROUP BY customer_id
),
churn_analysis AS (
  SELECT 
    ua.*,
    CURRENT_DATE - ua.last_order_date as days_since_last_order,
    CASE 
      WHEN CURRENT_DATE - ua.last_order_date <= 30 THEN 'Active'
      WHEN CURRENT_DATE - ua.last_order_date <= 60 THEN 'At Risk'
      WHEN CURRENT_DATE - ua.last_order_date <= 90 THEN 'Churning'
      ELSE 'Churned'
    END as customer_status,
    -- Customer lifecycle length
    ua.last_order_date - ua.first_order_date as customer_lifespan_days,
    -- Average days between orders
    CASE 
      WHEN ua.total_orders > 1 THEN 
        (ua.last_order_date - ua.first_order_date) / (ua.total_orders - 1)
      ELSE NULL
    END as avg_days_between_orders
  FROM user_activity ua
  WHERE ua.first_order_date >= '2024-01-01'
)
SELECT 
  customer_status,
  COUNT(*) as customer_count,
  AVG(days_since_last_order) as avg_days_since_last_order,
  AVG(total_orders) as avg_total_orders,
  AVG(lifetime_value) as avg_lifetime_value,
  AVG(customer_lifespan_days) as avg_lifespan_days,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as percentage_of_customers
FROM churn_analysis
GROUP BY customer_status
ORDER BY 
  CASE customer_status
    WHEN 'Active' THEN 1
    WHEN 'At Risk' THEN 2
    WHEN 'Churning' THEN 3
    WHEN 'Churned' THEN 4
  END;

Churn analysis metrics:

  • Recency: Days since last activity
  • Frequency: Average time between actions
  • Lifecycle stage: Active, at-risk, churned segments
  • Predictive indicators: Early warning signs

Date Formatting and Presentation

User-Friendly Date Display

Proper date formatting makes reports more readable and professional for stakeholders.

-- Date formatting and presentation
SELECT 
  order_date,
  -- Standard formatting
  TO_CHAR(order_date, 'YYYY-MM-DD') as iso_date,
  TO_CHAR(order_date, 'Month DD, YYYY') as formatted_date,
  TO_CHAR(order_date, 'Mon YYYY') as month_year,
  TO_CHAR(order_date, 'Day') as day_name,
  TO_CHAR(order_date, 'Q') as quarter,
  
  -- Custom business formats
  TO_CHAR(order_date, 'YYYY-"Q"Q') as fiscal_quarter,
  TO_CHAR(order_date, 'YYYY-MM') as year_month,
  TO_CHAR(order_date, 'IYYY-"W"IW') as iso_week,
  
  -- Relative date descriptions
  CASE 
    WHEN order_date = CURRENT_DATE THEN 'Today'
    WHEN order_date = CURRENT_DATE - 1 THEN 'Yesterday'
    WHEN order_date >= CURRENT_DATE - 7 THEN 'This Week'
    WHEN order_date >= DATE_TRUNC('month', CURRENT_DATE) THEN 'This Month'
    WHEN order_date >= DATE_TRUNC('year', CURRENT_DATE) THEN 'This Year'
    ELSE 'Older'
  END as relative_time
FROM orders
WHERE order_date >= '2024-01-01'
ORDER BY order_date DESC;

Database-Specific Considerations

Cross-Platform Compatibility

While most date functions are standardized, some syntax varies between database systems:

  • PostgreSQL: Strong date/time support, timezone-aware types
  • MySQL: Use YEAR(), MONTH(), DAY() functions
  • SQL Server: DATEPART(), DATEDIFF(), DATEADD() functions
  • BigQuery: EXTRACT(), DATE_TRUNC(), DATE_DIFF() functions
  • Snowflake: Similar to PostgreSQL with additional time travel features

Best Practices for Date Analysis

Data Quality and Consistency

Following best practices ensures accurate and reliable temporal analysis:

  • Standardize timezones: Store all timestamps in UTC, convert for display
  • Handle NULL dates: Use COALESCE() or IS NOT NULL filters appropriately
  • Validate date ranges: Check for reasonable date boundaries
  • Use appropriate data types: DATE for dates, TIMESTAMP for precise timing
  • Index date columns: Create indexes on frequently filtered date columns

Performance Optimization

Date queries can be expensive on large datasets. Optimize with these techniques:

  • Use date ranges: WHERE date >= '2024-01-01' AND date < '2025-01-01'
  • Avoid functions in WHERE: Don't use WHERE YEAR(date) = 2024
  • Partition by date: Use table partitioning for very large time-series data
  • Materialized views: Pre-aggregate common date-based reports

Common Pitfalls and Solutions

Timezone Confusion

The most common date analysis errors involve timezone mishandling:

  • Mixed timezones: Ensure all dates use consistent timezone
  • Daylight saving: Be aware of DST transitions affecting calculations
  • UTC vs local time: Clearly document which timezone is used

Incomplete Time Periods

Avoid analysis errors from partial time periods:

  • Partial months: Exclude current incomplete month from monthly comparisons
  • Weekend effects: Consider how partial weeks affect weekly metrics
  • Holiday adjustments: Account for holidays in business day calculations

Advanced Date Analysis Patterns

Rolling Windows and Moving Averages

Use window functions with date filtering for sophisticated time-based analysis:

  • 7-day rolling average: Smooth daily volatility
  • Month-to-date totals: Progressive monthly tracking
  • Year-over-year growth: Same period previous year comparisons
  • Seasonal adjustments: Account for predictable seasonal patterns

Business Calendar Considerations

Different businesses have different calendar needs:

  • Fiscal years: Companies with non-calendar fiscal years
  • 4-4-5 calendar: Retail calendar with consistent comparison periods
  • Academic calendar: Education-focused businesses
  • Custom holidays: Company-specific non-working days

Real-World Application Examples

Product Analytics Use Cases

Here are practical applications of date functions in product analytics:

  • Feature adoption curves: Track how quickly new features gain adoption
  • Onboarding funnel timing: Analyze time between onboarding steps
  • Subscription lifecycle: Trial-to-paid conversion timelines
  • Support ticket patterns: Peak support times and resolution speeds
  • A/B test duration: Ensure sufficient test duration for statistical significance

Tools and Extensions

Helpful Libraries and Functions

Consider these additional tools for complex date analysis:

  • Business day libraries: Handle complex holiday calendars
  • Fiscal calendar functions: Custom fiscal year calculations
  • Time series databases: InfluxDB, TimescaleDB for high-frequency data
  • Visualization tools: Ensure your BI tool handles timezones correctly

Conclusion

Mastering date and time functions is essential for any analyst working with temporal data. From basic date extraction to complex cohort analysis, these functions enable deep insights into user behavior, business cycles, and growth patterns.

Start with the fundamental functions like EXTRACT and DATE_TRUNC, then gradually incorporate more advanced patterns like cohort analysis and time series forecasting. Always be mindful of timezone handling and data quality to ensure your temporal analysis is accurate and reliable.

The key to successful date analysis is understanding your business context: Are you analyzing global users requiring timezone considerations? Do you need to account for business days? Are there seasonal patterns specific to your industry? Tailor your approach to match your specific analytical needs and always validate your results against known business patterns.