Date and Time Functions

MySQL provides comprehensive funcsions for extracting and manipulating date and time values.

Retrieving Current Date/Time

-- Get current date and time
SELECT NOW();
-- Returns: YYYY-MM-DD HH:MM:SS

-- Get current date only
SELECT CURDATE();
-- Returns: YYYY-MM-DD

-- Get current time only
SELECT CURTIME();
-- Returns: HH:MM:SS

Extracting Date Components

-- Extract individual components from a datetime value
SELECT YEAR(NOW());      -- Returns the year
SELECT MONTH(NOW());     -- Returns month as number (1-12)
SELECT DAY(NOW());       -- Returns day of month
SELECT HOUR(NOW());      -- Returns hour
SELECT MINUTE(NOW());    -- Returns minute
SELECT SECOND(NOW());    -- Returns second

-- Get day name (e.g., Monday, Tuesday)
SELECT DAYNAME(NOW());

-- Get month name (e.g., January, February)
SELECT MONTHNAME(NOW());

Using EXTRACT Function

The EXTRACT function provides an alternative syntax for pulling specific parts from dates:

SELECT EXTRACT(YEAR FROM NOW());
SELECT EXTRACT(MONTH FROM NOW());
SELECT EXTRACT(DAY FROM NOW());
SELECT EXTRACT(HOUR FROM NOW());

Conditional Functions

IFNULL and COALESCE

These functions handle NULL values by providing substitute values.

-- IFNULL: Replace NULL with a specified value
SELECT 
    customer_name,
    IFNULL(phone, 'Unavailable') AS contact_phone
FROM customers;

-- COALESCE: Returns first non-NULL value from a list
SELECT 
    customer_name,
    COALESCE(phone, mobile, email, 'No Contact') AS contact_info
FROM customers;

Example: Display customer full name and phone number, showing "Unknown" when phone is NULL:

SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    COALESCE(phone, 'Unknown') AS phone_number
FROM customers;

IF Function

The IF function performs conditional logic within SQL queries:

IF(condition, value_if_true, value_if_false)

Example: Categorize products based on stock levels:

SELECT 
    product_name,
    stock_quantity,
    IF(stock_quantity > 0, 'Available', 'Out of Stock') AS status
FROM products;

Example: Mark customers who placed more than one order:

SELECT 
    customer_id,
    order_count,
    IF(order_count > 1, 'Multiple Orders', 'Single Order') AS purchase_frequency
FROM (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) AS order_summary;

CASE Expression

The CASE expression handles multiple conditional branches, similar to switch statements in programming languages.

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN condition3 THEN result3
    ELSE default_result
END

Example: Classify customers into tiers based on their score:

SELECT 
    customer_name,
    score,
    CASE 
        WHEN score >= 90 THEN 'Platinum'
        WHEN score >= 70 THEN 'Gold'
        WHEN score >= 50 THEN 'Silver'
        ELSE 'Bronze'
    END AS customer_tier
FROM customers;

Example: Calculate shipping costs based on order total:

SELECT 
    order_id,
    total_amount,
    CASE 
        WHEN total_amount >= 100 THEN 0
        WHEN total_amount >= 50 THEN 5.99
        ELSE 9.99
    END AS shipping_cost
FROM orders;

Practical Applications

These conditional functions are particularly useful for:

• Data transformation and cleaning
• Generating computed columns
• Creating derived categories from continuous values
• Handling missing or incomplete data
• Building reports with conditional logic

When working with large datasets, using these functions at the database level is often more efficient than processing data in application code.