How do you use SQL for time and date manipulation?

SQL provides robust tools for time and date manipulation, primarily through built-in functions and operators that handle extraction, arithmetic, and formatting. Most SQL dialects support standard date/time data types like DATE, TIME, DATETIME, and TIMESTAMP, along with functions to interact with them. For example, CURRENT_DATE or GETDATE() retrieves the current date, while EXTRACT(YEAR FROM date_column) isolates specific components like year or month. Date arithmetic is performed using operators like + or - with intervals (e.g., date_column + INTERVAL '7 days' in PostgreSQL) or functions like DATEADD(day, 7, date_column) in SQL Server. These operations enable tasks such as calculating deadlines or filtering records within a date range. It’s important to note syntax variations across databases—for instance, MySQL uses DATE_ADD() while PostgreSQL relies on interval literals.

Formatting and time zone handling are critical for displaying or converting dates. Functions like TO_CHAR(date_column, 'YYYY-MM-DD') in PostgreSQL or DATE_FORMAT(date_column, '%Y-%m-%d') in MySQL transform dates into readable strings. Time zones are managed using functions like CONVERT_TZ(date_column, 'UTC', 'America/New_York') in MySQL or AT TIME ZONE clauses in PostgreSQL. For example, SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/London' converts a UTC timestamp to London time. Handling time zones ensures consistency when dealing with global data, though developers must ensure database servers are configured correctly to avoid mismatches. Always store dates in UTC unless business logic requires otherwise.

Calculating intervals and durations is another common use case. Functions like DATEDIFF(day, start_date, end_date) in SQL Server or AGE(end_date, start_date) in PostgreSQL compute the difference between dates. For example, SELECT DATEDIFF(day, '2023-01-01', '2023-01-10') returns 9 days. To generate time series data (e.g., daily reports), use recursive CTEs or database-specific functions like PostgreSQL’s GENERATE_SERIES('2023-01-01'::DATE, '2023-01-10', '1 day'). Timestamp precision can be adjusted with DATE_TRUNC('month', date_column) to group records by month. Always test edge cases, such as leap years or daylight saving time transitions, which can affect interval calculations. These tools collectively enable precise temporal analysis, scheduling, and reporting in applications.