SQL Date and Time Functions

SQL Date and Time Functions

Date and time manipulation is essential for reporting, scheduling, and data analysis. While syntax varies across databases, the core concepts remain consistent.

Current Date and Time

SELECT
  CURRENT_DATE,                    -- 2025-09-15
  CURRENT_TIMESTAMP,               -- 2025-09-15 14:30:00+00
  NOW(),                           -- PostgreSQL/MySQL: current timestamp
  GETDATE()                        -- SQL Server: current timestamp
;

Extracting Parts

-- PostgreSQL / MySQL
SELECT
  EXTRACT(YEAR FROM order_date) AS year,
  EXTRACT(MONTH FROM order_date) AS month,
  EXTRACT(DOW FROM order_date) AS day_of_week
FROM orders;

-- SQL Server
SELECT YEAR(order_date), MONTH(order_date), DAY(order_date)
FROM orders;

Date Truncation

Round down to the start of a period:

-- PostgreSQL
SELECT DATE_TRUNC('month', order_date) AS month_start FROM orders;

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS month_start FROM orders;

Date Arithmetic

-- PostgreSQL
SELECT order_date + INTERVAL '30 days' AS due_date FROM orders;

-- MySQL
SELECT DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date FROM orders;

-- Difference between dates (PostgreSQL)
SELECT EXTRACT(DAY FROM NOW() - hire_date) AS days_employed FROM employees;

-- MySQL
SELECT DATEDIFF(NOW(), hire_date) AS days_employed FROM employees;

Formatting

-- PostgreSQL
SELECT TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI:SS') FROM orders;

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-%d %H:%i:%s') FROM orders;

Timezone Handling

-- PostgreSQL
SELECT created_at AT TIME ZONE 'America/New_York' AS local_time FROM events;

-- Convert between zones
SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokyo' FROM events;

Common Patterns

-- Records from the last 30 days
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'

-- Records from current month
WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE)

Best Practices

• Store timestamps in UTC and convert for display
• Use TIMESTAMP WITH TIME ZONE for any user-facing datetime
• Avoid functions on indexed date columns in WHERE clauses (prevents index usage)
• Use date range comparisons instead of extracting parts for filtering

Date functions are critical for time-series analysis, scheduling systems, and any application that processes temporal data.