SQL Date and Timestamp Interview Questions: Parsing, Bucketing, and Filtering Without Off-by-One Mistakes

Introduction

Time-based SQL questions derail a lot of otherwise strong candidates. The prompt sounds familiar, but then the details start piling up: the timestamp is stored as text, the metric needs full days instead of rolling hours, the business wants local time instead of UTC, or the filter includes one boundary and excludes another.

These are not minor details. Date and timestamp logic changes the answer. That is why interviewers use time questions so often. They want to see whether you know that correct SQL is not only about syntax. It is also about calendars, boundaries, and definitions.

Why Time Questions Break Solid SQL Candidates

Most time-related mistakes come from one of four places:

• Parsing strings into dates or timestamps incorrectly
• Grouping by the wrong time bucket
• Using inclusive end boundaries that double count edge values
• Ignoring timezone differences between stored data and reporting logic

If you learn a few repeatable patterns for each problem, these questions become much less intimidating.

Parse Strings into Real Dates and Timestamps First

If a column is stored as text, do not build the whole query around string comparisons. Parse it once, give it a clear name, and use the typed value afterward.

PostgreSQL example

SELECT
    user_id,
    TO_TIMESTAMP(event_ts_text, 'YYYY-MM-DD HH24:MI:SS') AS event_ts
FROM raw_events;

MySQL example

SELECT
    user_id,
    STR_TO_DATE(event_ts_text, '%Y-%m-%d %H:%i:%s') AS event_ts
FROM raw_events;

This is also a good place to ask a clarifying question in an interview. If the raw value has timezone information or mixed formats, your parsing rule needs to reflect that.

Bucket Events by Day, Week, or Month Explicitly

When the business asks for daily active users or monthly revenue, say out loud what defines the bucket. Do not assume day means the server timezone or week means the default database week boundary.

PostgreSQL

SELECT
    DATE_TRUNC('day', event_ts) AS day_bucket,
    COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY 1
ORDER BY 1;

MySQL

SELECT
    DATE(event_ts) AS day_bucket,
    COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY 1
ORDER BY 1;

For monthly or weekly reporting, keep the same habit: transform the timestamp to the reporting bucket once, then aggregate on that value.

Use Half-Open Time Ranges

One of the safest habits in SQL is to use half-open intervals. That means the lower bound is inclusive and the upper bound is exclusive. It prevents edge timestamps from being counted twice across adjacent periods.

For example, if you need the last seven full days excluding today:

PostgreSQL

WHERE event_ts >= CURRENT_DATE - INTERVAL '7 day'
  AND event_ts < CURRENT_DATE

MySQL

WHERE event_ts >= CURRENT_DATE - INTERVAL 7 DAY
  AND event_ts < CURRENT_DATE

This pattern is safer than trying to write 23:59:59 endpoints, and it scales cleanly to weeks and months.

Ask the Three Time Questions That Change the Answer

Before solving a time prompt, ask or state these assumptions:

• Is the stored timestamp in UTC, or is it already localized?
• Should reporting use user-local time, business timezone, or raw database time?
• Should the current partial day, week, or month be included?

That short checklist prevents a surprising number of mistakes. It also signals that you understand the difference between event time and reporting time.

Worked Example: Daily Active Users for the Last Seven Full Days

Suppose the prompt says: Return daily active users for the last seven full days. A clean answer should exclude today, count each user once per day, and group after the date bucket is defined.

PostgreSQL

WITH filtered AS (
    SELECT
        user_id,
        event_ts,
        DATE_TRUNC('day', event_ts) AS day_bucket
    FROM events
    WHERE event_ts >= CURRENT_DATE - INTERVAL '7 day'
      AND event_ts < CURRENT_DATE
)
SELECT
    day_bucket::date AS activity_date,
    COUNT(DISTINCT user_id) AS daily_active_users
FROM filtered
GROUP BY 1
ORDER BY 1;

MySQL

WITH filtered AS (
    SELECT
        user_id,
        event_ts,
        DATE(event_ts) AS day_bucket
    FROM events
    WHERE event_ts >= CURRENT_DATE - INTERVAL 7 DAY
      AND event_ts < CURRENT_DATE
)
SELECT
    day_bucket AS activity_date,
    COUNT(DISTINCT user_id) AS daily_active_users
FROM filtered
GROUP BY 1
ORDER BY 1;

If the interviewer wants calendar weeks instead of seven rolling days, say that the filter logic changes and you would align to the week boundary explicitly.

Common Time-Logic Mistakes to Avoid

Do not compare text values when the logic is really timestamp logic. Do not mix DATE(event_ts) in one part of the query with raw timestamps elsewhere unless you are deliberately changing grain. Do not use BETWEEN without thinking carefully about inclusive end boundaries.

Also, do not forget that timestamp questions often hide a business-definition question. If the dashboard is meant to show completed days only, a technically valid query that includes partial today data is still the wrong answer.

FAQ

Is BETWEEN always wrong for date filters?

No, but it is easy to misuse because it includes both endpoints. Half-open ranges are usually safer for timestamp data.

Should I cast timestamps to dates as early as possible?

Only when the reporting grain is truly date-level. If you cast too early, you can lose precision that you still need for filtering or interval logic.

Do interviewers really care about timezone assumptions?

Yes, especially for analytics, product, and international reporting roles. Asking about timezone often separates careful candidates from candidates who only recognize the syntax pattern.