SQL - Date Functions

SQL Date and Time Functions

SQL provides a rich set of built-in functions to handle date and time data. These functions allow you to perform operations like retrieving the current time, calculating date differences, extracting parts of a date, and formatting dates for display. Understanding these is crucial for almost any application that deals with time-sensitive data, such as logging, scheduling, or financial records.

Standard SQL Date and Time Data Types

Before diving into functions, it’s important to know the common data types for storing date and time information:

• DATE: Stores only the date (e.g., ‘2023-10-27’).
• TIME: Stores only the time of day (e.g., ‘15:30:00’).
• TIMESTAMP: Stores both date and time (e.g., ‘2023-10-27 15:30:00’).
• TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ): Stores a timestamp along with time zone information. This is critical for global applications.

Common Date and Time Functions

While specific function names can vary between SQL dialects (like PostgreSQL, MySQL, and SQL Server), the core concepts are universal. Here are some of the most essential functions, with notes on dialect-specific syntax.

Functionality	Standard SQL / Common Name	Description & Examples
Get Current Time	CURRENT_TIMESTAMP, NOW()	Returns the current date and time of the database server. NOW() is a popular synonym. GETDATE() is specific to SQL Server.
Get Current Date	CURRENT_DATE	Returns the current date of the database server.
Get Current Time	CURRENT_TIME	Returns the current time of the database server.
Extract a Part of a Date	EXTRACT(part FROM date)	The ANSI SQL standard for extracting a component (like YEAR, MONTH, DAY, HOUR) from a date/time value. SELECT EXTRACT(YEAR FROM '2023-10-27'); returns 2023.
Date/Time Arithmetic	INTERVAL	Used to add or subtract periods of time. Syntax varies slightly. PostgreSQL/MySQL: SELECT NOW() + INTERVAL '1 day'; SQL Server: SELECT DATEADD(day, 1, GETDATE());
Calculate Difference	DATEDIFF(part, start_date, end_date)	Calculates the difference between two dates in a specified unit (day, month, year). The function name and argument order can vary. For instance, in PostgreSQL, you can simply subtract timestamps to get an interval.
Format Date to String	TO_CHAR() / DATE_FORMAT() / FORMAT()	Converts a date/time value to a string with a specified format. PostgreSQL: TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS') MySQL: DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') SQL Server: FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss')
Get Last Day of Month	EOMONTH(date)	Returns the last day of the month for a given date. Widely supported, especially in SQL Server and PostgreSQL (via a function or date arithmetic).
Truncate a Date	DATE_TRUNC(‘part’, date)	Truncates a date/time value to a specified precision (e.g., to the start of the month, hour, or day). Very common in PostgreSQL. SELECT DATE_TRUNC('month', NOW()); would return the first day of the current month at midnight.
Construct a Date/Time	MAKE_DATE() / DATEFROMPARTS()	Creates a date or timestamp from individual parts (year, month, day, etc.). The name DATEFROMPARTS is used in SQL Server, while PostgreSQL has functions like MAKE_DATE and MAKE_TIMESTAMP.

Practical Example: User Activity Analysis

Imagine you have a user_logins table and you want to find out how many users logged in each month.

CREATE TABLE user_logins (
    user_id INT,
    login_time TIMESTAMP WITH TIME ZONE NOT NULL
);

-- Sample Data
INSERT INTO user_logins (user_id, login_time) VALUES
(1, '2023-01-15 10:00:00Z'),
(2, '2023-01-20 12:30:00Z'),
(1, '2023-02-05 11:00:00Z');

-- PostgreSQL / Standard SQL Example
SELECT
    DATE_TRUNC('month', login_time)::DATE AS login_month,
    COUNT(DISTINCT user_id) AS unique_users
FROM
    user_logins
GROUP BY
    login_month
ORDER BY
    login_month;

This query first truncates the login_time to the beginning of the month, then groups by this truncated value to count the unique users for that month. This is a common pattern in data analytics.