SQL
Advanced Date and Time Zone Conversions
Master converting dates and times between different time zones, extracting parts of dates, and calculating date differences in SQL.
-- Convert UTC timestamp to a specific time zone (PostgreSQL example)
SELECT
created_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' AS created_at_nyc
FROM
events;
-- Extract specific parts of a date (e.g., month, year) (SQL Standard/PostgreSQL/MySQL)
SELECT
EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month
FROM
orders;
-- Calculate difference between two dates in days (MySQL/PostgreSQL/SQL Server examples)
-- MySQL:
SELECT
DATEDIFF(end_date, start_date) AS days_difference
FROM
projects;
-- PostgreSQL:
SELECT
(end_date - start_date) AS days_difference
FROM
projects;
-- SQL Server:
SELECT
DATEDIFF(day, start_date, end_date) AS days_difference
FROM
projects;
How it works: This snippet provides practical examples for advanced date and time manipulation in SQL. It covers converting timestamps between different time zones (crucial for global applications), extracting specific components like year or month from a date, and calculating the difference between two dates in various units (like days). The examples are tailored for common SQL databases like PostgreSQL, MySQL, and SQL Server, highlighting their respective syntax for these frequently needed operations.