SQL Date and Time Functions

---

SQL provides powerful tools for working with date and time data, essential for storing and manipulating temporal information in your database. Whether you're tracking when records were created, or querying for data within specific time ranges, mastering SQL date and time functions is crucial.

---

1. SQL Date and Time Data Types

Before diving into SQL date and time functions, it's important to understand the basic SQL data types related to dates and times.

1.1 DATE

• Description: Stores the date in the format YYYY-MM-DD.
• Usage: Suitable for cases where only the date (without time) is required.

Example:

CREATE TABLE events (
    event_date DATE  -- Stores only the date
);

1.2 TIME

• Description: Stores the time in the format HH:MM:SS.
• Usage: Used when you need to store only the time component without the date.

Example:

CREATE TABLE work_schedule (
    shift_start TIME  -- Stores time only
);

1.3 DATETIME / TIMESTAMP

• Description: Stores both date and time in the format YYYY-MM-DD HH:MI:SS. The TIMESTAMP type is often used for tracking the exact time of an event.
• Usage: Ideal for tracking when a record was created or last updated.

Example:

CREATE TABLE orders (
    order_date DATETIME  -- Stores both date and time
);

1.4 YEAR

• Description: Stores only the year in the format YYYY.
• Usage: Often used to store the year for events, employment records, or fiscal years.

Example:

CREATE TABLE employees (
    hire_year YEAR  -- Stores only the year
);

---

2. Working with Date and Time Functions

SQL provides a variety of built-in functions for manipulating date and time values. These functions are useful for querying data within specific ranges, formatting dates, and performing calculations.

---

2.1 CURRENT_DATE and CURRENT_TIME

These functions return the current date or time from the system.

• CURRENT_DATE: Returns the current date in the YYYY-MM-DD format.
• CURRENT_TIME: Returns the current time in the HH:MI:SS format.

Example:

SELECT CURRENT_DATE;  -- Returns current date
SELECT CURRENT_TIME;  -- Returns current time

2.2 NOW() / CURRENT_TIMESTAMP

The NOW() or CURRENT_TIMESTAMP function returns the current date and time in the format YYYY-MM-DD HH:MI:SS.

Example:

SELECT NOW();  -- Returns current date and time

2.3 DATE_ADD() and DATE_SUB()

• DATE_ADD(): Adds a specified time interval to a date.
• DATE_SUB(): Subtracts a specified time interval from a date.

Example:

SELECT DATE_ADD(CURRENT_DATE, INTERVAL 5 DAY);  -- Adds 5 days to current date
SELECT DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH);  -- Subtracts 3 months from current date

2.4 DATEDIFF()

The DATEDIFF() function returns the difference between two dates, in days.

Example:

SELECT DATEDIFF('2024-12-31', '2024-01-01');  -- Returns 365 (days difference)

2.5 DATE_FORMAT()

The DATE_FORMAT() function allows you to format a date value into a specific format.

• Syntax: DATE_FORMAT(date, format)
• Common format specifiers:
  • %Y: Year (4 digits)
  • %m: Month (2 digits)
  • %d: Day of the month (2 digits)
  • %H: Hour (24-hour format)
  • %i: Minutes
  • %s: Seconds

Example:

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');  -- Formats current date and time
SELECT DATE_FORMAT('2024-12-25', '%M %d, %Y');  -- Formats date as 'December 25, 2024'

2.6 EXTRACT()

The EXTRACT() function extracts a specific part (like year, month, day) from a date or time.

• Syntax: EXTRACT(unit FROM date)
• Common units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

Example:

SELECT EXTRACT(YEAR FROM NOW());  -- Extracts the year from the current date
SELECT EXTRACT(MONTH FROM '2024-12-25');  -- Extracts the month (12) from a specific date

---

3. SQL Date and Time Comparison Operators

SQL also provides comparison operators to work with date and time values, similar to other types of data.

3.1 Comparing Dates and Times

You can use comparison operators to filter records based on date and time values.

• Example:
  • =: Equal to a specific date/time
  • <> or !=: Not equal to a specific date/time
  • >: After a specific date/time
  • <: Before a specific date/time
  • BETWEEN: A date range

Example:

SELECT * FROM orders
WHERE order_date = '2024-12-25';  -- Exact date match

SELECT * FROM events
WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31';  -- Date range

---

4. Time Zone Handling in SQL

SQL allows you to handle time zones when dealing with TIMESTAMP and DATETIME values, depending on the database system.

4.1 CONVERT_TZ() (MySQL)

In MySQL, the CONVERT_TZ() function converts a timestamp from one time zone to another.

• Syntax: CONVERT_TZ(datetime, from_tz, to_tz)

Example:

SELECT CONVERT_TZ(NOW(), 'UTC', 'America/New_York');  -- Convert current time from UTC to Eastern Time

4.2 AT TIME ZONE (SQL Server)

In SQL Server, you can use the AT TIME ZONE function to convert a timestamp to a specific time zone.

Example:

SELECT GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time';  -- Convert to Pacific Time

---

5. Best Practices for Using Date and Time in SQL

Here are some best practices when working with date and time in SQL:

• Use DATETIME or TIMESTAMP for timestamp data: These types are ideal for storing both date and time information.
• Avoid storing dates as strings: It's best to use native date types (DATE, TIME, DATETIME) for performance and data integrity.
• Consider time zone differences: When dealing with applications that span across different time zones, always consider storing dates and times in UTC and converting them to local time when necessary.
• Format dates appropriately: Use functions like DATE_FORMAT() to display dates in a readable format, but store them in their native date format for consistency and performance.

---

---