SQL - Date & Time

SQL Date & Time Data

Handling date and time is a fundamental aspect of almost every application, from logging events to scheduling appointments. SQL provides a powerful set of data types and functions to store, manipulate, and query temporal data accurately and efficiently. This tutorial covers modern best practices for working with dates and times in SQL.

Standard SQL Date/Time Data Types

The SQL standard defines several data types for temporal values. Most modern database systems support these or very similar types.

Data Type	Description	Example
DATE	Stores a calendar date (year, month, day).	‘2024-10-26’
TIME	Stores a time of day (hour, minute, second).	‘14:30:05’
TIMESTAMP	Stores both date and time, without time zone information. Also known as a ‘naive’ timestamp.	‘2024-10-26 14:30:05.123’
TIMESTAMP WITH TIME ZONE	Stores date, time, and time zone information. The most robust choice for recording a specific instant in time.	‘2024-10-26 14:30:05.123-05:00’
INTERVAL	Stores a duration of time.	‘3 days’, ‘5 hours’, ‘2 years’

Note on Vendor Variations: Some systems have historical types like DATETIME (SQL Server, MySQL). Modern best practice is to prefer the standard types like TIMESTAMP WITH TIME ZONE or DATETIME2(n) in SQL Server for new development.

The Critical Importance of Time Zones

Mishandling time zones is one of the most common and costly bugs in software development. A ‘naive’ TIMESTAMP like ‘2024-10-26 09:00:00’ could mean 9 AM in New York, London, or Tokyo—three very different moments in time.

Best Practice: Store in UTC

The industry-standard best practice is to always store timestamps in Coordinated Universal Time (UTC). Your database server, application servers, and database columns should be configured for UTC. Use TIMESTAMP WITH TIME ZONE (or an equivalent) as your data type.

The conversion to a user’s local time zone should happen at the last possible moment, typically in the presentation layer (e.g., in the JavaScript front-end or server-side rendering logic).

Essential Date/Time Functions

SQL provides functions to get the current time, manipulate dates, and extract components.

• Getting Current Time: The standard function is CURRENT_TIMESTAMP, which returns a TIMESTAMP WITH TIME ZONE. CURRENT_DATE and CURRENT_TIME are also available. (Note: NOW() is a common, non-standard equivalent in many databases like PostgreSQL and MySQL).
• Extracting Components: The EXTRACT function lets you pull out parts of a date. EXTRACT(YEAR FROM my_timestamp_column) will return the year.
• Date Arithmetic: You can add or subtract INTERVAL values from timestamps. CURRENT_DATE + INTERVAL '7 day' gives the date one week from now.

Example Functions

-- Get the current time with time zone (Standard SQL)
SELECT CURRENT_TIMESTAMP;
-- Result: 2024-10-26 15:31:32.123+00

-- Get the year from a specific date
SELECT EXTRACT(YEAR FROM DATE '2023-08-22');
-- Result: 2023

-- Calculate the date 3 months from now (PostgreSQL syntax)
SELECT (CURRENT_DATE + INTERVAL '3 month');
-- Result: a date 3 months in the future

Practical Examples and Common Operations

Let’s create a table to log user events.

CREATE TABLE user_events (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    event_type VARCHAR(50),
    -- Best practice: use TIMESTAMP WITH TIME ZONE and default to the current time in UTC
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Insert an event (the 'created_at' will be set automatically)
INSERT INTO user_events (user_id, event_type) VALUES (123, 'LOGIN_SUCCESS');

Querying for a Time Range

To find all events that happened yesterday:

SELECT * FROM user_events
WHERE created_at >= (CURRENT_DATE - INTERVAL '1 day')
  AND created_at < CURRENT_DATE;

Common Pitfalls to Avoid

• Storing Dates as Strings: Never store dates or times in VARCHAR or TEXT columns. This prevents proper sorting, date arithmetic, validation, and is extremely inefficient.
• Ignoring Time Zones: Failing to use time zone-aware timestamps (TIMESTAMP WITH TIME ZONE) for global applications will lead to incorrect data.
• Using BETWEEN for Timestamps: BETWEEN is inclusive on both ends. For timestamp ranges, it’s safer and more precise to use a combination of >= and < to avoid accidentally including midnight of the following day.