Understanding Date and Time Units in MariaDB SQL Statements

Date and Time Data Types

MariaDB offers several data types to represent dates, times, and timestamps:

• TIMESTAMP
  Similar to DATETIME but often with automatic updates based on the database server time.
• DATETIME
  Combines both date and time information (e.g., '2024-06-14 18:46:00').
• TIME
  Stores hours, minutes, and seconds (e.g., '18:46:00').
• DATE
  Stores year, month, and day information (e.g., '2024-06-14').

Working with Intervals

SELECT order_date + INTERVAL 7 DAY FROM orders;

This query retrieves orders placed 7 days after the order_date.

Date and Time Functions

MariaDB provides a rich set of functions for manipulating date and time data. Some common examples include:

• DATEDIFF(): Calculates the difference between two dates in days.
• DATE_ADD(): Similar to ADDDATE() but offers more flexibility.
• ADDTIME(): Adds a specified interval to a time value.
• ADDDATE(): Adds a specified interval to a date value.
• CURRENT_TIME(): Returns the current time.
• CURDATE(): Returns the current date.

• MariaDB allows time zone conversions using the CONVERT_TZ() function.
• You can use various formats to represent dates and times within your SQL statements. Refer to the MariaDB documentation for supported formats.

---

SELECT CURDATE(), CURRENT_TIME();

This code retrieves both the current date and current time as separate values.

Adding Interval to a Date

-- Select orders placed one month after June 1st, 2024
SELECT * FROM orders
WHERE order_date >= DATE_ADD('2024-06-01', INTERVAL 1 MONTH);

This code uses DATE_ADD() to add one month to the date '2024-06-01' and then selects orders with dates greater than or equal to the new date.

Calculating Age based on Birthdate

-- Assuming a 'birthdate' column exists in the 'users' table
SELECT TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) AS age
FROM users;

This code calculates the age of users by subtracting their birthdate from the current date using TIMESTAMPDIFF() and specifying the difference in years.

Formatting a DATETIME Value

-- Select formatted order date with time in 12-hour format
SELECT DATE_FORMAT(order_date, '%Y-%m-%d %h:%i %p') AS formatted_date
FROM orders;

This code uses DATE_FORMAT() to display the order_date in a specific format, including year, month, day, hour in 12-hour format (h), minutes (i), and AM/PM indicator (p).

Converting Time Zone

-- Assuming a 'utc_timestamp' column exists
SELECT CONVERT_TZ(utc_timestamp, 'UTC', 'America/Los_Angeles') AS local_time
FROM events;

This code converts a timestamp stored in UTC (Coordinated Universal Time) to Pacific Standard Time (America/Los_Angeles) using CONVERT_TZ().

---

• Working with Intervals
  Instead of "Date and Time Units" for intervals, you can directly use the INTERVAL keyword followed by specific units (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND). This is the standard way to define durations in MariaDB.

• Data Types
  If you're looking for alternatives to the built-in date and time data types (DATE, TIME, DATETIME, TIMESTAMP), there aren't any direct replacements within MariaDB. However, you could potentially store dates as strings in a specific format, but this approach is generally less efficient and error-prone compared to dedicated data types.

Aspect of "Date and Time Units"	Alternative	Description
Data Types	N/A (consider strings with caution)	Built-in data types (DATE, TIME, DATETIME, TIMESTAMP) are preferred.
Working with Intervals	INTERVAL keyword + units	Define durations explicitly (e.g., INTERVAL 3 DAY).
Date and Time Functions	Specific functions (CURDATE(), ADDDATE(), etc.)	Utilize dedicated functions for various manipulations.