MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Function Overview

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

MySQL DTAETIME, TIMESTAMP, DATE, TIME, YEAR (date and time types)

MySQL provides several data types for storing dates and times:

DATE: The DATE type is used to store a date. The format is 'YYYY-MM-DD' and the range is '1000-01-01' to '9999-12-31'.
Example:
```
CREATE TABLE example (
    date_column DATE
);
```
To insert data:
```
INSERT INTO example (date_column) VALUES ('2023-05-11');
```
TIME: The TIME type is used to store a time. The format is 'hh:mm:ss' and the range is '-838:59:59' to '838:59:59'.
Example:
```
CREATE TABLE example (
    time_column TIME
);
```
To insert data:
```
INSERT INTO example (time_column) VALUES ('12:34:56');
```
DATETIME: The DATETIME type is used to store a date and time. The format is 'YYYY-MM-DD hh:mm:ss' and the range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
Example:
```
CREATE TABLE example (
    datetime_column DATETIME
);
```
To insert data:
```
INSERT INTO example (datetime_column) VALUES ('2023-05-11 12:34:56');
```
TIMESTAMP: The TIMESTAMP data type is used to store a date and time. It has automatic properties that DATETIME does not have. The format is 'YYYY-MM-DD hh:mm:ss', and the range is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. It also converts data to UTC on storage and back to local time on retrieval.
Example:
```
CREATE TABLE example (
    timestamp_column TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```
To insert data:
```
INSERT INTO example (timestamp_column) VALUES ('2023-05-11 12:34:56');
```
This will automatically set the current timestamp when a new row is inserted and update it when the row is updated.
YEAR: The YEAR type is used to store a year. The format is YYYY and the range is 1901 to 2155, or 0000.
Example:
```
CREATE TABLE example (
    year_column YEAR
);
```
To insert data:
```
INSERT INTO example (year_column) VALUES (2023);
```

Remember that when retrieving TIMESTAMP values, MySQL converts the UTC values back to the system's time zone. This is not the case with DATETIME; it will remain constant regardless of time zone settings.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    event_time TIMESTAMP
);

CREATE TABLE date_table (
    id INT PRIMARY KEY,
    event_date DATE
);

CREATE TABLE time_table (
    id INT PRIMARY KEY,
    event_time TIME
);

CREATE TABLE datetime_table (
    id INT PRIMARY KEY,
    event_datetime DATETIME,
    event_timestamp TIMESTAMP
);

CREATE TABLE year_table (
    id INT PRIMARY KEY,
    event_year YEAR
);

CREATE TABLE time_only_table (
    id INT PRIMARY KEY,
    event_time_only TIME
);

CREATE TABLE date_only_table (
    id INT PRIMARY KEY,
    event_date_only DATE
);

CREATE TABLE auto_timestamp_table (
    id INT PRIMARY KEY,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

SELECT CONVERT_TZ(event_time, 'UTC', 'America/New_York') AS event_time_ny FROM timezone_table;

SELECT DATE(event_datetime) AS event_date,
       TIME(event_datetime) AS event_time
FROM datetime_table;