Dates are notoriously difficult in programming, and SQL is no exception. Between different formats, timezones, and database-specific functions, it's easy to get lost.
In this guide, we'll break down the essential operations you need to master: getting the current time, adding/subtracting intervals, formatting dates, and calculating differences.
Note: This playground uses SQLite. While the concepts are universal, date syntax varies heavily between databases (PostgreSQL, MySQL, SQL Server). We'll focus on SQLite syntax here but mention standard SQL equivalents.
A Real Business Scenario: Reporting by Local Business Day
Date logic becomes operational the moment a team asks for reports that line up with how the business actually works.
Imagine an ecommerce or SaaS team needing answers like:
- how many orders arrived yesterday in Shanghai time
- which subscriptions renew within the next 7 days
- what was revenue by calendar month in the customer's billing timezone
- how long does it take support to resolve tickets in business hours
Those questions sound simple, but they break quickly if the query mixes UTC timestamps, local dates, and string-formatted time values without a clear rule.
1. Getting the Current Date and Time
The most basic operation is finding out "now".
CURRENT_DATE: Returns today's date (YYYY-MM-DD).CURRENT_TIMESTAMP: Returns date and time (YYYY-MM-DD HH:MM:SS).
Interactive Example: What day is it?
2. Date Arithmetic: Adding and Subtracting
How do you calculate a deadline 7 days from now? Or find the date 1 month ago?
In SQLite, we use modifiers with the date() or datetime() functions:
date('now', '+7 days')date('now', '-1 month')
Standard SQL: Other databases often use
DATE_ADD(date, INTERVAL 7 DAY)or simplydate + INTERVAL '7 days'.
Interactive Example: Project Deadlines
A Common Mistake: Formatting Too Early and Losing the Real Time Value
Many date bugs start when a query turns a timestamp into a string too early:
SELECT strftime('%Y-%m', created_at) AS month_label
FROM orders;
That is fine for display or grouping, but once a timestamp is reduced to a label, you have lost part of the original time information.
This causes issues like:
- sorting text labels instead of actual timestamps
- filtering on formatted strings rather than native date ranges
- grouping in one timezone and filtering in another
- comparing dates that were truncated differently across queries
The reliable pattern is: store and filter with true date/time values first, then format for presentation as the final step.
3. Formatting Dates: Extracting Parts
Often you need to group data by year, month, or day.
In SQLite, we use strftime(format, date_string):
%Y: Year (2025)%m: Month (01-12)%d: Day (01-31)%H: Hour (00-23)
Standard SQL: Many databases use
EXTRACT(YEAR FROM date)orDATE_FORMAT().
Interactive Example: Sales by Month
4. Date Differences
How many days have passed between two dates?
In SQLite, we convert dates to "Julian Day Numbers" (continuous count of days since the beginning of the Julian Period) and subtract them:
julianday(end_date) - julianday(start_date)
Standard SQL: Most databases have a
DATEDIFF(unit, start, end)function.
Interactive Challenge: Employee Tenure
You have a list of employees with their hire dates.
Challenge: Calculate how many days each employee has been working at the company (tenure). Assume "today" is '2025-12-31' for this exercise.
You have access to:
employees_date_challenge
id(INTEGER)name(TEXT)hire_date(TEXT)
Click to see the solution
SELECT
name,
hire_date,
-- Calculate difference in days
CAST(julianday('2025-12-31') - julianday(hire_date) AS INTEGER) as days_worked
FROM employees_date_challenge;
Boundary and Performance Notes
Date logic is one of the easiest places to get a correct-looking but wrong query:
- wrapping a timestamp column in a function can make an otherwise index-friendly filter much slower
- month arithmetic is not the same as adding 30 days, especially around month-end
- daylight saving transitions can make hour differences behave differently than expected
- date strings that are not stored in a sortable ISO format become fragile fast
That is why production systems often keep timestamps in UTC, use explicit conversion rules, and avoid ad hoc formatting logic scattered across reports.
When NOT to Solve It with Ad Hoc Date Functions
Avoid relying on one-off date expressions when:
- the business calendar depends on holidays or fiscal periods
- the reporting timezone differs by customer or account
- "business day" logic matters more than raw calendar days
- the same date rules need to be reused across many dashboards and pipelines
In those cases, a calendar table, shared semantic model, or standardized transformation layer is usually safer than repeating bespoke date expressions everywhere.
Official References
- PostgreSQL date/time functions and operators for the broader SQL date toolkit and interval behavior.
- SQLite date and time functions for the SQLite-specific syntax used in this playground.
- MySQL date and time functions for dialect comparison when your production system is not SQLite.
Test Your Skills with Real Interview Questions
Ready to apply your date/time knowledge? Try these real interview questions that require date manipulation:
- 30-Day Video Retention Rate (ByteDance) - Calculate retention using date arithmetic and self-joins to find users who returned exactly 30 days later
- Average Issue Resolution Time (Atlassian) - Use JULIANDAY() to calculate time differences between created and resolved dates
- Daily Trading Volume Trend (Coinbase) - Analyze time-series data with LAG() to calculate day-over-day percentage changes
- Month-over-Month Follower Growth (Twitch) - Extract year-month with STRFTIME() and use window functions to track growth
These questions test your mastery of date arithmetic, time differences, and time-series analysis in production scenarios.
Tool Workflow
Use tools when date logic should become part of a broader query workflow
Date arithmetic is often where reporting queries become harder to read and debug. It helps to explain the final SQL, inspect filters, and connect time logic back to time-series analysis patterns.
Related Articles
- Time Series Analysis with SQL for the broader reporting patterns that rely on strong date manipulation basics.
- SQL ORDER BY and LIMIT: Sorting and Pagination Made Easy for the sorting and paging behavior often paired with date-based queries.
- Calculating Moving Averages in SQL for a concrete analytical pattern that depends heavily on date logic.
Conclusion
Working with dates is less about memorizing one syntax and more about respecting grain, timezone, and calendar rules. Once those are clear, SQL date functions become reliable tools instead of a recurring source of bugs.
SQLite Cheat Sheet:
- Now:
CURRENT_DATE - Add/Sub:
date(col, '+N days') - Format:
strftime('%Y-%m', col) - Diff:
julianday(end) - julianday(start)
Master these, and you'll be able to handle any time-series analysis task!