Prev Next

MySQL Dates

MySQL provides several data types for handling dates and times, including DATE, DATETIME, TIMESTAMP, and YEAR. These types are used to store dates or date-time values in different formats and are useful for various operations like calculations, comparisons, and formatting.

1. MySQL Date and Time Data Types

  • DATE: Stores a date in the format YYYY-MM-DD.
  • DATETIME: Stores both date and time in the format YYYY-MM-DD HH:MI:SS.
  • TIMESTAMP: Stores both date and time in the format YYYY-MM-DD HH:MI:SS. It is affected by the time zone and is used to track changes.
  • YEAR: Stores a year in the format YYYY or YY.

2. Creating a Table with Date Columns

To work with these data types, you can create a table as shown below:

CREATE TABLE tamil_kings_dates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    king_name VARCHAR(100),
    birth_date DATE,
    coronation_datetime DATETIME,
    recorded_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ruling_year YEAR
);

Code Explanation: This command creates a table tamil_kings_dates with columns for different date and time data types: DATE, DATETIME, TIMESTAMP, and YEAR.

3. Inserting Dates and Times

You can insert date and time values using the standard formats:

INSERT INTO tamil_kings_dates (king_name, birth_date, coronation_datetime, ruling_year)
VALUES ('Raja Raja Chola', '0970-01-01', '0995-06-12 10:30:00', '985');

Code Explanation: This command inserts a record with a birth date, coronation date-time, and ruling year into the tamil_kings_dates table. The recorded_timestamp column automatically records the current timestamp.

4. Date Functions and Usage

4.1. Formatting Dates

SELECT king_name, DATE_FORMAT(coronation_datetime, '%Y-%M-%D %H:%i:%s') AS formatted_date
FROM tamil_kings_dates;

Code Explanation: The DATE_FORMAT function formats the coronation_datetime column to display the date and time in 'Year-Month-Day Hour:Minute:Second' format.

4.2. Extracting Parts of a Date

SELECT king_name, YEAR(birth_date) AS birth_year, MONTH(birth_date) AS birth_month, DAY(birth_date) AS birth_day
FROM tamil_kings_dates;

Code Explanation: The YEAR, MONTH, and DAY functions extract the year, month, and day from the birth_date column.

4.3. Calculating Date Differences

SELECT king_name, DATEDIFF(death_date, birth_date) AS lifespan_days
FROM tamil_kings_dates;

Code Explanation: The DATEDIFF function calculates the difference between the death_date and birth_date, returning the lifespan in days.

4.4. Adding and Subtracting Dates

SELECT king_name, DATE_ADD(birth_date, INTERVAL 30 YEAR) AS thirty_years_later
FROM tamil_kings_dates;

Code Explanation: The DATE_ADD function adds 30 years to the birth_date, showing the date 30 years later.

4.5. Using TIMESTAMP

SELECT king_name, recorded_timestamp
FROM tamil_kings_dates;

Code Explanation: The recorded_timestamp column shows the current timestamp recorded when the record was inserted or last updated.

5. Best Practices

  • Use appropriate date and time formats for data consistency.
  • Be cautious of time zone differences when using TIMESTAMP.
  • Use built-in date functions for efficient calculations and operations.

6. Key Takeaways

  • MySQL has specific data types for handling dates and times: DATE, DATETIME, TIMESTAMP, and YEAR.
  • Using the right data type and functions simplifies date operations and ensures data consistency.
  • Always store dates in a standard format and consider time zone implications when needed.
Prev Next