MySQL - Today Date

Working with Dates in MySQL

Effectively managing dates and times is a fundamental skill in database management. MySQL provides powerful data types and functions to store, retrieve, and manipulate temporal data accurately.

Common Mistake: Storing Dates as Text

A common anti-pattern is to store dates in VARCHAR or TEXT columns. Avoid this at all costs. Using proper date/time data types ensures data integrity, allows for correct sorting and calculations, and uses storage more efficiently.

Choosing the Right Data Type

MySQL offers several data types for temporal values. The most common are:

• DATE: Stores a date value (‘YYYY-MM-DD’). Ideal for birthdays, event dates, etc. Range: ‘1000-01-01’ to ‘9999-12-31’.
• DATETIME: Stores both date and time (‘YYYY-MM-DD HH:MM:SS’). Useful for logging specific moments like an order placement. It is not timezone-aware.
• TIMESTAMP: Also stores date and time. However, it converts the value from the current session’s time zone to UTC for storage, and back to the session’s time zone on retrieval. This makes it perfect for applications used across different time zones. Its range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

Getting the Current Date and Time

MySQL provides several functions to get the current date and time from the server.

• CURDATE() or CURRENT_DATE(): Returns the current date as a DATE value.
• NOW() or SYSDATE(): Returns the current date and time as a DATETIME or TIMESTAMP value.
• UTC_TIMESTAMP(): Returns the current Coordinated Universal Time (UTC) date and time.

Example Queries

SELECT CURDATE() AS today_date;
-- Result: 2023-10-27

SELECT NOW() AS current_datetime;
-- Result: 2023-10-27 10:30:00

Practical Example: An Orders Table

Let’s create a table to store order information, using appropriate data types.

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL, -- The date the order was placed
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Automatically records creation time
    amount DECIMAL(10, 2)
);

Now, let’s insert some data. We’ll use MySQL’s date functions for this.

INSERT INTO orders (customer_id, order_date, amount)
VALUES
    -- An order for today
    (101, CURDATE(), 199.99),

    -- An order from yesterday, using date arithmetic
    (102, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 49.50),

    -- An order for next month
    (101, DATE_ADD(CURDATE(), INTERVAL 1 MONTH), 120.00);

To retrieve the orders, you can simply query the table:

SELECT order_id, customer_id, order_date, created_at, amount FROM orders;

Working with Dates in Client Programs

When fetching date/time values in your application, modern database drivers typically convert them into native date/time objects for the programming language, not just strings.

Node.js (using mysql2)

const mysql = require('mysql2/promise');

async function getOrders() {
  // ... (connection setup)
  const [rows] = await connection.execute('SELECT order_id, order_date FROM orders');
  rows.forEach(row => {
    // The 'order_date' field is a JavaScript Date object
    console.log(`Order ID: ${row.order_id}, Date: ${row.order_date.toDateString()}`);
  });
  // ... (connection teardown)
}
getOrders();

Python (using mysql-connector-python)

import mysql.connector
import datetime

# ... (connection setup)
with connection.cursor(dictionary=True) as cursor:
    cursor.execute("SELECT order_id, order_date FROM orders")
    for row in cursor.fetchall():
        # The 'order_date' field is a Python datetime.date object
        order_date: datetime.date = row['order_date']
        print(f"Order ID: {row['order_id']}, Date: {order_date.strftime('%Y-%m-%d')}")
# ... (connection teardown)