Date and time types in MySQL

Introduction

Date and time types help us store dates and times in a database clearly and correctly.

To record when a user signs up on a website.

To save the date and time of a sale in a store.

To track when a blog post was published.

To schedule events or appointments.

To calculate durations between two moments.

Syntax

MySQL

DATE
DATETIME
TIMESTAMP
TIME
YEAR

DATE stores only the date (year-month-day).

DATETIME stores date and time (year-month-day hour:minute:second).

Examples

This table stores only the date of events.

MySQL

CREATE TABLE events (
  event_id INT,
  event_date DATE
);

This table stores both date and time for meetings.

MySQL

CREATE TABLE meetings (
  meeting_id INT,
  start_time DATETIME
);

This table stores timestamps that can auto-update when rows change.

MySQL

CREATE TABLE logs (
  log_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Sample Program

This example creates a table with separate date and time columns, inserts two rows, and selects all data.

MySQL

CREATE TABLE appointments (
  id INT PRIMARY KEY,
  appointment_date DATE,
  appointment_time TIME
);

INSERT INTO appointments (id, appointment_date, appointment_time) VALUES
(1, '2024-06-01', '14:30:00'),
(2, '2024-06-02', '09:00:00');

SELECT * FROM appointments;

Important Notes

Use DATE when you only need the day, month, and year.

TIMESTAMP is useful for recording exact moments and can auto-update.

Always use the format 'YYYY-MM-DD' for dates and 'HH:MM:SS' for times in MySQL.

Summary

Date and time types store dates, times, or both in a clear way.

Choose the right type based on what you want to save: date only, time only, or both.

They help keep your data organized and easy to use for time calculations.