← Back to Knowledge Base← Previous questionNext question →
ProgrammingBackend Developer, BI Analyst

Explain how to handle temporal data and date intervals in SQL, what are the specifics of time-related functions (DATEDIFF, DATEADD, INTERVAL) in different DBMS? What traps can occur when working with time zones? Provide an example of a correct time difference calculation.

Pass interviews with Hintsage AI assistant

Answer

In SQL, there is a large set of functions for working with dates and intervals: DATEDIFF, DATEADD (T-SQL), working with the INTERVAL type (PostgreSQL), functionality for converting times to different zones (AT TIME ZONE). For correct date handling, it is key to use data types that account for time zones (TIMESTAMP WITH TIME ZONE, timestamptz), rather than just DATE or DATETIME, especially for global services.

In different DBMS, syntax and operational specifics differ:

  • SQL Server: DATEDIFF, DATEADD, FORMAT, but no full INTERVAL support. Time zones are managed explicitly.
  • PostgreSQL: INTERVAL support, many functions (+/- INTERVAL), direct management of time zones.

Errors often arise from inconsistencies in storing in UTC/local time and mixing operations that do not account for differences.

Example of Difference Calculation (PostgreSQL)

SELECT
  EXTRACT(EPOCH FROM (event_end AT TIME ZONE 'UTC' - event_start AT TIME ZONE 'UTC')) / 3600 AS hours
FROM events

Trick Question

Can the date difference in DATEDIFF give a negative number, and what does their order of appearance mean?

Answer: Yes, DATEDIFF (DATEDIFF(day, d1, d2)) returns a negative number if d2 < d1. It is important not to confuse the parameter order: the unit of measurement comes first, then the start date, then the end date.

Example:

SELECT DATEDIFF(day, '2024-05-01', '2024-04-25') -- will return -6

Examples of Real Errors

Story

In a project with global users, reports were generated based on local server times in each region. The result — mismatched aggregates by dates and the appearance of "gaps" at the day boundary for users from other time zones. Fixed by converting all temporal data to UTC and recalculating only for display.

Story

While calculating subscription duration through DATEDIFF, the order of dates was confused. As a result, some users were mistakenly recognized as "super users" with a retroactive negative duration. Fixed by introducing a check for date order.

Story

A developer stored DATETIME without accounting for time zones; during migration to PostgreSQL, values were interpreted as UTC, whereas they were originally in GMT+3, which led to events in reports shifting 3 hours back. Conclusion — always store and convert dates with time zone consideration!