Date and Time Operations Questions

Tests practical skills for working with dates and times in data, reporting, and everyday technical work. Candidates should be comfortable with date and time data types (date vs. timestamp vs. timestamp with time zone) and their storage and comparison semantics, date filtering, relative date ranges such as last-n-days or rolling windows, inclusive versus exclusive range boundaries, timezone conversions and daylight saving time edge cases, business-day and holiday-aware calculations, epoch/unix timestamp conversions, and fiscal or custom period logic. Interviewers assess the ability to translate a reporting or business requirement into correct date logic, choose the right date/time representation for a given system, and reason through common pitfalls such as timezone mismatches between systems and off-by-one boundary errors. This shows up across contexts: SQL queries, spreadsheet formulas, BI tool calculated fields and filters, and date/time handling in general-purpose code.

HardTechnical

52 practiced

Given an event stream (user_id, event_ts UTC, event_type), implement SQL to sessionize events per user with a 30-minute inactivity timeout. Additionally, only count time that falls within business hours (09:00-18:00) local to the user. Return average session length per user in minutes. Outline how you handle events that cross business-hour boundaries and DST transitions.

Sample Answer

Approach (brief): 1) Sessionize per user using 30-minute inactivity (lag + cumulative sum of session starts). 2) For each session compute session_start/ session_end. 3) Convert UTC timestamps to user's local tz (join users table with timezone). 4) Split sessions by calendar day in local time, intersect each piece with business hours (09:00–18:00 local) and sum the intersection durations. 5) Aggregate per user and compute average session length in minutes.

Assumptions:- events(user_id, event_ts_utc TIMESTAMP, event_type)- users(user_id, tz STRING) where tz is IANA timezone (e.g. 'America/Los_Angeles')

SQL (Postgres-style / BigQuery-ish — adapt functions to your RDBMS):

sql

WITH ev AS (
  SELECT e.*,
         u.tz,
         e.event_ts_utc AT TIME ZONE 'UTC' AT TIME ZONE u.tz AS event_local_ts
  FROM events e
  JOIN users u USING (user_id)
),
sess AS (
  SELECT *,
    CASE WHEN EXTRACT(EPOCH FROM (event_ts_utc - LAG(event_ts_utc) OVER (PARTITION BY user_id ORDER BY event_ts_utc))) > 1800
         OR LAG(event_ts_utc) OVER (PARTITION BY user_id ORDER BY event_ts_utc) IS NULL
    THEN 1 ELSE 0 END AS is_new_session
  FROM ev
),
session_ided AS (
  SELECT *,
    SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_ts_utc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_seq
  FROM sess
),
sessions AS (
  -- session start = first event local ts in group; end = last event local ts + 0 (or + small gap). We set session_end = last_event_ts + 30min inactivity window
  SELECT user_id, tz, session_seq,
         MIN(event_ts_utc) AS session_start_utc,
         MAX(event_ts_utc) + INTERVAL '30 minutes' AS session_end_utc,
         MIN(event_local_ts) AS session_start_local,
         (MAX(event_ts_utc) + INTERVAL '30 minutes') AT TIME ZONE 'UTC' AT TIME ZONE tz AS session_end_local
  FROM session_ided
  GROUP BY user_id, tz, session_seq
),
-- expand sessions into per-day slices in local time
days AS (
  SELECT s.*,
         generate_series(
           date_trunc('day', session_start_local),
           date_trunc('day', session_end_local),
           INTERVAL '1 day'
         )::timestamp AS slice_day_local
  FROM sessions s
),
slice_bounds AS (
  SELECT d.user_id, d.tz, d.session_seq,
         GREATEST(d.session_start_local, d.slice_day_local) AS slice_start_local,
         LEAST(d.session_end_local, d.slice_day_local + INTERVAL '1 day') AS slice_end_local
  FROM days d
),
business_intersect AS (
  SELECT *,
    -- business window on that day: 09:00 to 18:00 local
    GREATEST(slice_start_local, slice_day_local + INTERVAL '9 hours') AS inter_start_local,
    LEAST(slice_end_local, slice_day_local + INTERVAL '18 hours') AS inter_end_local
  FROM slice_bounds
),
slice_duration AS (
  SELECT user_id, session_seq,
    CASE WHEN inter_end_local > inter_start_local
         THEN EXTRACT(EPOCH FROM (inter_end_local - inter_start_local))/60.0
         ELSE 0 END AS minutes_in_business
  FROM business_intersect
)
SELECT user_id,
       AVG(session_minutes) AS avg_session_minutes
FROM (
  SELECT user_id, session_seq, SUM(minutes_in_business) AS session_minutes
  FROM slice_duration
  GROUP BY user_id, session_seq
) per_session
GROUP BY user_id;

Key points / reasoning:- Sessionization uses 30-min inactivity on UTC event timestamps; we extend session_end by 30 minutes to include idle tail.- Converting to user's IANA tz preserves DST rules; DB timezone functions handle DST transitions (ambiguous or non-existent local times are resolved by the DB — verify for your engine).- Splitting by local calendar day allows clean intersection with daily business-hours windows.- Summing intersections yields only minutes within 09:00–18:00 local.- Average computed per user over their sessions.

Handling events crossing business hours:- A session spanning 08:50–09:10 contributes only 09:00–09:10 (10 minutes). A session spanning multiple days is split and each day's slice intersected with business hours.

Handling DST transitions:- Using IANA tz and database time zone conversion ensures DST forward/back adjustments. On "spring forward" a local hour may not exist — conversion will map UTC times correctly; on "fall back" ambiguous local times map deterministically per DB rules (verify behavior). If strict control is needed, normalize using AT TIME ZONE and explicit rules (e.g., prefer first/second occurrence) or use timezone-aware functions provided by your DB.

Edge cases / notes:- If your DB lacks generate_series for timestamps or AT TIME ZONE behavior, implement day-splitting with a numbers table or windowed joins and use timezone conversion functions available.- Consider sessions with zero business-minute contribution — exclude or keep per business requirement.- Performance: pre-aggregate events per session before day-splitting; index on (user_id, event_ts_utc).

MediumTechnical

44 practiced

Stakeholder request: 'Send me the last 7 days' data. Clarify and implement both SQL examples: (A) last 7 rolling days including today, and (B) last 7 complete days excluding today. Also explain how to configure a BI tool relative date filter to match each behavior.

Sample Answer

Clarify requirements- Ask stakeholder: Do they mean calendar days based on the reporting timezone or last 7x24-hour windows relative to now? Do times in data use UTC or a local timezone? Confirm whether "including today" should include partial today (i.e., from 00:00 today) or last 7 full 24-hour rolling periods.- I’ll provide two common interpretations: A) Rolling last 7 days including today: 7 days back from now (includes partial today). B) Last 7 complete days excluding today: the seven full calendar days prior to today (midnight-to-midnight).

SQL examples (Postgres / ANSI SQL style)

A) Last 7 rolling days including today (now - 7 days up to now)

sql

SELECT *
FROM events
WHERE event_ts >= (now() - interval '7 days')
  AND event_ts <= now();

- Uses timestamp "now()", includes partial current day; good for trailing 7×24h.

B) Last 7 complete days excluding today (midnight 7 days ago through midnight yesterday)

sql

SELECT *
FROM events
WHERE event_ts >= date_trunc('day', now() - interval '7 days')
  AND event_ts < date_trunc('day', now());

- date_trunc('day', now()) is today's midnight. This returns rows from 00:00:00 seven days ago up to 00:00:00 today (exclusive), i.e., seven full calendar days.

Notes and variations- If your timestamps are in UTC but stakeholder expects local business day, convert: (event_ts AT TIME ZONE 'UTC') AT TIME ZONE 'America/Los_Angeles' or compare against now() AT TIME ZONE.- If event_ts is DATE (no time), use BETWEEN date - 6 and date for inclusive semantics: - Last 7 calendar days including today: WHERE event_date BETWEEN current_date - 6 AND current_date - Last 7 complete days excluding today: WHERE event_date BETWEEN current_date - 7 AND current_date - 1

BI tool relative date filter guidance- Power BI: - For rolling 7 days including today: set relative date filter -> "in the last" -> 7 days -> include today (toggle on). - For last 7 complete days: use relative date filter -> "in the previous" -> 7 days (this excludes today).- Tableau: - Rolling including today: Filter on date field -> Relative date -> choose "Last" -> Days -> 7 (checks include today). - Last complete days: Relative date -> "Previous" -> Days -> 7 (excludes current partial day).- Looker: - Use filter: created_date >= add_days(now(), -7) and created_date <= now() for rolling. - Or use built-in "7 days" vs "previous 7 days" relative filters in Looker's UI (choose "in the past" vs "previous").

Practical tips- Always document which interpretation you implemented on the dashboard (“rolling 7 days (includes today)” vs “previous 7 full days (excludes today)”).- Add timezone note and a small annotation showing the filter definition so consumers understand the window.- Validate by spot-checking counts for known dates (e.g., include a test row with today's timestamp).

MediumTechnical

46 practiced

Given table events(event_date date, revenue numeric), write an ANSI/Postgres SQL query that returns the date and its 7-day rolling average of daily revenue. Explain how you handle missing dates (days with zero revenue) so the rolling average is computed over actual calendar days.

Sample Answer

Approach: generate a continuous calendar of dates covering the range in events, left-join to fill missing days with zero revenue, then compute a 7-day rolling average using a window over the ordered calendar.

sql

WITH calendar AS (
  SELECT generate_series(
           (SELECT MIN(event_date) FROM events),
           (SELECT MAX(event_date) FROM events),
           INTERVAL '1 day'
         )::date AS day
),
daily AS (
  -- sum revenue per day (in case of multiple rows per date) and fill missing with 0
  SELECT
    c.day AS event_date,
    COALESCE(e.daily_revenue, 0) AS revenue
  FROM calendar c
  LEFT JOIN (
    SELECT event_date, SUM(revenue) AS daily_revenue
    FROM events
    GROUP BY event_date
  ) e ON e.event_date = c.day
)
SELECT
  event_date,
  -- 7-day rolling average over calendar days (current day and prior 6 days)
  ROUND(AVG(revenue) OVER (
          ORDER BY event_date
          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        )::numeric, 2) AS avg_7d_revenue
FROM daily
ORDER BY event_date;

Why this works:- generate_series ensures every calendar day appears; missing event days become rows with revenue = 0 via COALESCE.- Using ROWS BETWEEN 6 PRECEDING ensures the window spans exactly seven calendar rows (days), so the average counts zeros for missing days.- Aggregating events per date before the join handles multiple event rows per day.

Notes / trade-offs:- If you need the average to ignore missing days (i.e., average over only days with data), use a window that divides SUM(revenue) by COUNT(NULLIF(revenue,0)) or exclude zeros — but for calendar-based rolling average zeros are usually appropriate.- For large date ranges, generate_series can be expensive; restrict range with business bounds or prebuilt calendar table. Ensure events.event_date is indexed.

MediumTechnical

66 practiced

Explain inclusive vs exclusive date ranges for reporting (for example, should a daily report use created_at >= '2024-01-01' AND created_at <= '2024-01-31' or created_at >= '2024-01-01' AND created_at < '2024-02-01'?). Give examples of bugs that can occur when mixing approaches and recommend a best practice for timestamp-based comparisons in BI SQL queries.

Sample Answer

Use half-open (inclusive start, exclusive end) intervals — i.e. created_at >= '2024-01-01' AND created_at < '2024-02-01' — as the default for timestamp-based reporting. It avoids ambiguity at boundaries and composes cleanly (adjacent ranges don’t overlap or leave gaps).

Examples of bugs when mixing approaches- Off-by-one/missing rows: Using <= '2024-01-31' on a timestamp column drops records from '2024-01-31 23:59:59.999...' if the DB stores more precision or uses UTC offsets.- Double-counting: One process uses <= end_date, another uses < next_day; when joining or deduping across systems with different range rules, the same event may be included twice.- Timezone issues: Filtering by date string in local time but data stored in UTC causes records near midnight to shift into adjacent days.- Aggregation mismatch: GROUP BY DATE(created_at) vs. range filters can disagree if you mix exclusive and inclusive logic.

Best practices (BI-friendly)- Use half-open ranges: created_at >= @start AND created_at < @end_next.- Normalize timestamps to a single timezone (usually UTC) before filtering.- For daily reports, compute end as date_add('day', 1, start_date) and use date_trunc/cast for display:

sql

-- recommended pattern (Postgres-like)
SELECT DATE(created_at AT TIME ZONE 'UTC') AS day,
       COUNT(*) AS events
FROM events
WHERE created_at >= TIMESTAMP '2024-01-01 00:00:00+00'
  AND created_at <  TIMESTAMP '2024-02-01 00:00:00+00'
GROUP BY 1
ORDER BY 1;

- If you need to filter by date (no time), either cast the timestamp to date (but be explicit about timezone) or use half-open ranges derived from dates.- Document and enforce the convention across ETL/BI layers and tests that assert adjacent ranges compose without overlap or gaps.

EasyTechnical

57 practiced

An imported CSV has dates in a column that are mixed: numeric Excel serials like 44561, text '2020-01-01', and some improperly parsed '01/02/20'. Describe a robust set of steps or Excel formulas to normalize this column to proper Excel date values, considering Excel epoch differences (1900 vs 1904) and two-digit year ambiguity.

Sample Answer

Approach: detect each cell type (numeric serial, unambiguous ISO text, ambiguous dd/mm/yy or mm/dd/yy), convert to a proper Excel date value, and normalize epoch (1900 vs 1904). Use helper columns so steps are auditable.

1) Helper: trim and store rawAssume raw in A2. In B2:

excel

=TRIM(A2)

2) If it's a numeric Excel serial (pure number)

excel

=IF(ISNUMBER(--B2), --B2, "")

If numbers originate from Mac/1904-system, add 1462:

excel

=IF(ISNUMBER(--B2), --B2 + IF(source_is_1904, 1462, 0), "")

3) If it's ISO text "2020-01-01" use DATEVALUE

excel

=IFERROR(DATEVALUE(B2), "")

4) Handle ambiguous "01/02/20" — decide organization rule (common: interpret 00–29 as 2000–2029). Parse parts and build date explicitly to avoid locale mis-parse:

excel

=IFERROR(
  LET(
    s, SUBSTITUTE(B2," ",""),
    p, TEXTSPLIT(s, "/"),           /* Excel 365; otherwise use MID/FIND */
    m, VALUE(INDEX(p,1)), d, VALUE(INDEX(p,2)), y, VALUE(INDEX(p,3)),
    yy, IF(y<30, 2000+y, IF(y<100, 1900+y, y)),
    DATE(yy, m, d)
  ),
"")

If TEXTSPLIT not available, use:

excel

=IFERROR(
  DATE(
    IF(VALUE(RIGHT(B2,2))<30,2000+VALUE(RIGHT(B2,2)),IF(LEN(B2)>8,VALUE(RIGHT(B2,4)),1900+VALUE(RIGHT(B2,2)))),
    VALUE(LEFT(B2,FIND("/",B2)-1)),
    VALUE(MID(B2,FIND("/",B2)+1, FIND("/",B2,FIND("/",B2)+1)-FIND("/",B2)-1))
  ),
"")

5) Unified normalized column (C2): prioritize conversions

excel

=IF(ISNUMBER(--B2), --B2 + IF(source_is_1904,1462,0),
 IFERROR(DATEVALUE(B2),
 IFERROR(<ambiguous-date-formula>,"")))

6) Format C column as Date.

Notes & best practices:- Confirm source epoch (ask sender or check known serials); 1904 difference = +1462 days.- Decide business rule for two-digit years (document it); common pivot is 00–29 => 2000–2029.- Validate with a sample set and flag failures: =IF(C2="", "Bad/Unknown", "OK").- For bulk ETL prefer Power Query: it lets you detect type, set locale, and unambiguously parse dd/mm vs mm/dd.

Unlock Full Question Bank

Get access to hundreds of Date and Time Operations interview questions and detailed answers.

Join thousands of developers preparing for their dream job.