Working with Dates and Times in SQL

1. Date and Time Data Types

SQL provides several data types for storing date and time information:

• DATE: Stores only the date (YYYY-MM-DD).
• TIME: Stores only the time (HH:MM:SS).
• DATETIME: Stores both date and time (YYYY-MM-DD HH:MM:SS).
• TIMESTAMP: Similar to DATETIME but includes a time zone.
• YEAR: Stores a year in a 2-digit or 4-digit format.

2. Date and Time Functions

SQL offers a variety of functions to manipulate and extract information from date and time values:

• CURRENT_DATE: Returns the current date.
• CURRENT_TIME: Returns the current time.
• CURRENT_TIMESTAMP: Returns the current date and time.
• EXTRACT: Extracts a specific part of a date (e.g., year, month, day).
• DATE_ADD: Adds a specified time interval to a date.
• DATE_SUB: Subtracts a specified time interval from a date.

3. Examples

Using CURRENT_DATE and CURRENT_TIME

SELECT CURRENT_DATE AS TodayDate, CURRENT_TIME AS CurrentTime;

This query returns the current date and time.

Extracting Parts of a Date

SELECT 
    EXTRACT(YEAR FROM OrderDate) AS OrderYear,
    EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
    EXTRACT(DAY FROM OrderDate) AS OrderDay
FROM Orders;

This query extracts the year, month, and day from the OrderDate column in the Orders table.

Adding and Subtracting Time Intervals

SELECT 
    DATE_ADD(OrderDate, INTERVAL 7 DAY) AS DueDate,
    DATE_SUB(OrderDate, INTERVAL 1 MONTH) AS PreviousMonth
FROM Orders;

This query adds 7 days to the OrderDate to calculate the due date and subtracts 1 month to find the previous month's date.

4. Formatting Dates and Times

SQL allows you to format dates and times according to your needs using the DATE_FORMAT function:

SELECT 
    DATE_FORMAT(OrderDate, '%Y-%m-%d') AS FormattedDate,
    DATE_FORMAT(OrderDate, '%H:%i:%s') AS FormattedTime
FROM Orders;

This query formats the OrderDate as a date and time string using specific format codes.

5. Time Zones

When working with TIMESTAMP data, you can handle time zones using functions like CONVERT_TZ:

SELECT 
    OrderTimestamp,
    CONVERT_TZ(OrderTimestamp, 'UTC', 'America/New_York') AS LocalTime
FROM Orders;

This query converts the OrderTimestamp from UTC to the local time in New York.

6. Insightful Value

Mastering the manipulation of dates and times in SQL is crucial for tasks such as scheduling, reporting, and data analysis. By understanding and applying these concepts, you can efficiently manage temporal data, ensuring accurate and meaningful results in your queries.