SQL Date and Time Functions by Database: PostgreSQL, MySQL, SQLite, and SQL Server

Overview

Date handling has two separate concerns: representing a value correctly and querying it without losing precision or index support. A calendar date such as 2026-11-05 is not the same as a timestamp such as 2026-11-05 14:30:00, and neither necessarily identifies a time zone.

Before writing a SQL date function, answer three questions:

• Is the column a date, a local timestamp, or an instant that should be interpreted in a time zone?
• Do you need a value for display, or a value for filtering and grouping?
• Must the query remain portable, or can it use the native functions of one database?

The most portable filtering pattern is an inclusive lower bound and an exclusive upper bound:

WHERE created_at >= :start_time
  AND created_at <  :end_time

For a report covering one calendar day, set :start_time to midnight at the beginning of that day and :end_time to midnight at the beginning of the next day. This avoids guessing the greatest possible fractional second and continues to work when a database stores more timestamp precision than expected.

Quick function reference

These expressions are deliberately representative rather than interchangeable. SQLite stores date and time values using supported text, real, or integer representations instead of a dedicated date type, so its input format and modifiers deserve extra attention. MySQL, PostgreSQL, and SQL Server also differ in return types, fractional-second behavior, and time-zone handling. Check the target engine when a query crosses database boundaries.

Checklist by scenario

Filtering a day, month, or reporting period

Use a half-open interval for timestamps. For a month, calculate the first instant of the month and the first instant of the following month rather than using a hard-coded final day.

-- PostgreSQL example
WHERE created_at >= TIMESTAMP '2026-11-01 00:00:00'
  AND created_at <  TIMESTAMP '2026-12-01 00:00:00'

In application code, bind both boundaries as parameters. This keeps the query clear and avoids applying a function to the column. A condition such as DATE(created_at) = :day can be convenient, but may prevent an index from being used efficiently unless the database has an appropriate expression or generated-column index.

Building rolling windows

For a rolling period, anchor both boundaries to one notion of “now.” PostgreSQL and MySQL examples look like this:

-- PostgreSQL
WHERE created_at >= CURRENT_TIMESTAMP - interval '7 days'
  AND created_at <  CURRENT_TIMESTAMP;

-- MySQL
WHERE created_at >= CURRENT_TIMESTAMP() - INTERVAL 7 DAY
  AND created_at <  CURRENT_TIMESTAMP();

If the application needs the exact same boundary for several statements, calculate it once in application code or a common query expression and bind it. This prevents tiny differences caused by evaluating the current-time function at different moments.

Grouping by day or month

For PostgreSQL, use date_trunc:

SELECT date_trunc('month', created_at) AS month_start,
       count(*) AS order_count
FROM orders
GROUP BY date_trunc('month', created_at)
ORDER BY month_start;

Equivalent approaches use DATE_FORMAT in MySQL, strftime in SQLite, and DATEFROMPARTS or a date arithmetic expression in SQL Server. Decide whether the grouping should happen in UTC, the database session zone, or a business reporting zone before writing the expression. “Month” is not universal when users operate across time zones.

Adding time and calculating differences

• PostgreSQL: use intervals, such as ts + interval '2 hours', and subtract timestamps when an interval result is appropriate.
• MySQL: use DATE_ADD, DATE_SUB, DATEDIFF, or TIMESTAMPDIFF. Note that date-difference functions may count calendar boundaries rather than elapsed seconds.
• SQLite: use date modifiers such as datetime(ts, '+2 hours') or julianday(end_ts) - julianday(start_ts) for an approximate day difference.
• SQL Server: use DATEADD and DATEDIFF. Choose the date part carefully: DATEDIFF(day, start, end) counts crossed day boundaries, not necessarily complete 24-hour periods.

Converting and displaying dates

Keep formatting at the presentation boundary whenever possible. A formatted string is useful in a report or API response, but it is usually a poor value for sorting, joining, or comparing. PostgreSQL commonly uses to_char, MySQL uses DATE_FORMAT, SQLite uses strftime, and SQL Server provides style codes through CONVERT. Keep the original typed value in the result when downstream code may need it.

Handling time zones

Store and compare instants consistently, then convert them for display. PostgreSQL has AT TIME ZONE; SQL Server supports AT TIME ZONE with named Windows time zones; MySQL can use CONVERT_TZ when its time-zone data is available; SQLite provides modifiers such as utc and localtime but does not provide the same time-zone type and rule system as the other engines.

Do not assume that a timestamp without an offset identifies an instant. Document whether it represents UTC, a server-local value, or a business-local value. Daylight-saving transitions can create local times that are ambiguous or do not exist, so conversion should happen with an explicit source and destination convention.

What to double-check

• Column type: Confirm whether the value is a date, timestamp, timestamp with time-zone semantics, or text. Text comparisons only behave chronologically when the text uses a consistent, sortable format.
• Parameter type: Bind dates and timestamps as typed parameters where the driver supports it. Avoid assembling date literals through string concatenation.
• Boundary convention: Prefer >= start AND < end. Write down whether an end date means the start of that date or the end of the previous date.
• Precision: Test milliseconds and smaller fractional seconds if the application records them. An equality comparison against a rounded timestamp can miss legitimate rows.
• Session settings: Check the connection time zone, locale, date format, and week-start conventions. A query can return different calendar interpretations under different session settings.
• Index use: Compare the execution plan when filtering a raw column versus applying a function to it. If grouping or filtering by a derived date is frequent, consider a supported generated, expression, or computed-column strategy.
• Migration behavior: Verify return types and null behavior in the destination database instead of translating function names mechanically.

Common mistakes

Using an inclusive end of day. Conditions such as created_at <= '2026-11-30 23:59:59' can omit rows with fractional seconds and require special handling for every precision. Use the next boundary exclusively.

Comparing formatted strings. Formatting a column before filtering can change a typed comparison into a string operation and may make an index less useful. Compare native date or timestamp values first.

Confusing elapsed time with calendar boundaries. “Two days later” and “48 hours later” can differ around daylight-saving changes or when a database function counts date boundaries. Choose the meaning the business rule actually needs.

Relying on ambiguous date literals. Prefer ISO-style, unambiguous values and parameters. Do not depend on a server’s language or regional settings to interpret values such as 03/04/2026.

Assuming every engine has the same date model. PostgreSQL intervals, MySQL interval expressions, SQLite modifiers, and SQL Server date parts are similar in purpose but different in semantics. Run boundary tests on the actual production engine.

For broader query cleanup and troubleshooting, see the SQL formatter and query debugging guide. If date logic is part of an API endpoint, the API debugging tools comparison can help you test request parameters and response values consistently.

When to revisit

Return to this checklist whenever you change database engines, upgrade a database major version, alter a timestamp column, or move a workload between regions. Recheck it before seasonal planning cycles when reports use local business dates, and whenever an application begins serving users in additional time zones.

A practical maintenance routine is:

1. Keep a small test dataset containing midnight values, fractional seconds, month ends, leap-day values, and daylight-saving transition cases relevant to your users.
2. Run the same boundary tests against each supported database engine and driver.
3. Inspect execution plans after adding date functions to filters, joins, or grouping expressions.
4. Record the intended time zone and inclusivity rules beside the query or repository method.
5. Review vendor documentation after major releases or when a function’s return type, precision, or time-zone behavior matters to a migration.

When in doubt, make the boundary explicit, keep values typed, and test the query at the exact edges where real data tends to expose date bugs.