SQL - Date & Time: Полное руководство для начинающих

Здравствуйте, будущие маги баз данных! Сегодня мы погружаемся в fascinierende мир дат и времени в SQL. Как ваш доброжелательный соседний компьютерный учитель, я здесь, чтобы направить вас в этом путешествии с множеством примеров и объяснений. Так что достаньте свою виртуальную машину времени и давайте начнем!

Типы данных дат и времени в SQL

Прежде чем мы начнем манипулировать датами и временем, нам нужно понять, как SQL хранит эту информацию. Представьте эти типы данных как особые контейнеры, предназначенные специально для временных данных.

DATE

Тип данных DATE хранит дату в формате ГГГГ-ММ-ДД.

CREATE TABLE events (
event_id INT PRIMARY KEY,
event_name VARCHAR(100),
event_date DATE
);

INSERT INTO events (event_id, event_name, event_date)
VALUES (1, 'Company Picnic', '2023-07-15');

В этом примере мы создаем таблицу events и добавляем пикник компании, запланированный на 15 июля 2023 года. Тип данных DATE обеспечивает хранение только даты без какой-либо информации о времени.

TIME

Тип данных TIME хранит время в формате ЧЧ:ММ:СС.

CREATE TABLE class_schedule (
class_id INT PRIMARY KEY,
class_name VARCHAR(100),
start_time TIME
);

INSERT INTO class_schedule (class_id, class_name, start_time)
VALUES (1, 'Introduction to SQL', '09:30:00');

Здесь мы создаем расписание занятий и добавляем занятие по SQL, начинающееся в 9:30 утра. Тип данных TIME позволяет нам хранить это время точно.

DATETIME

Тип данных DATETIME combine combinesboth date and time, typically in the format ГГГГ-ММ-ДД ЧЧ:ММ:СС.

CREATE TABLE appointments (
appointment_id INT PRIMARY KEY,
patient_name VARCHAR(100),
appointment_datetime DATETIME
);

INSERT INTO appointments (appointment_id, patient_name, appointment_datetime)
VALUES (1, 'John Doe', '2023-06-20 14:30:00');

В этом примере мы планируем визит к врачу для Джона Доу 20 июня 2023 года в 14:30. Тип данных DATETIME позволяет нам хранить дату и время в одной ячейке.

TIMESTAMP

Тип данных TIMESTAMP аналогичен DATETIME, но часто используется для отслеживания времени последнего изменения записи.

CREATE TABLE blog_posts (
post_id INT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO blog_posts (post_id, title, content)
VALUES (1, 'My First Blog Post', 'Hello, World!');

В этом примере столбец last_updated автоматически устанавливается на текущую дату и время при вставке или обновлении записи.

Функции дат и времени в SQL

Теперь, когда мы знаем, как хранить даты и времена, давайте рассмотрим некоторые из магических функций, которые предоставляет SQL для манипулирования этой информацией. Мне нравится думать о этих функциях как о временных инструментах в нашем наборе инструментов SQL!

Текущая дата и время

Давайте начнем с основ - получения текущей даты и времени.

SELECT CURRENT_DATE() AS today,
CURRENT_TIME() AS current_time,
NOW() AS current_datetime;

Этот запрос даст вам текущую дату, время и datetime. Это как если бы вы спросили SQL: "Эй, который час?" Удобно, правда?

Извлечение частей даты

Иногда нам нужны только определенные части даты. SQL имеет функции и для этого!

SELECT YEAR('2023-06-20') AS year,
MONTH('2023-06-20') AS month,
DAY('2023-06-20') AS day,
WEEKDAY('2023-06-20') AS weekday;

Этот запрос извлекает год, месяц, день и день недели из заданной даты. Это как если бы мы разрезали дату, чтобы получить нужную информацию.

Арифметика с датами

SQL позволяет нам выполнять арифметические операции с датами. Это особенно полезно для расчета продолжительностей или поиска дат в прошлом/будущем.

SELECT DATE_ADD('2023-06-20', INTERVAL 7 DAY) AS week_later,
DATE_SUB('2023-06-20', INTERVAL 1 MONTH) AS month_ago,
DATEDIFF('2023-12-31', '2023-06-20') AS days_until_new_year;

В этом примере мы добавляем 7 дней к дате, вычитаем 1 месяц из даты и calculates the number of days between two dates. Это как если бы у нас была машина времени у нас под рукой!

Форматирование дат

Иногда нам нужно представить даты в определенном формате. Функция DATE_FORMAT приходит нам на помощь.

SELECT DATE_FORMAT('2023-06-20', '%W, %M %d, %Y') AS formatted_date,
DATE_FORMAT('2023-06-20 14:30:00', '%h:%i %p') AS formatted_time;

Этот запрос форматирует дату как "Tuesday, June 20, 2023" и время как "02:30 PM". Это как если бы мы одели наши даты для торжественного случая!

Вот таблица некоторых часто используемых форматных спецификаторов:

Specifier	Описание	Пример
%Y	4-значный год	2023
%y	2-значный год	23
%M	Название месяца	January
%m	Месяц (01-12)	01
%d	День месяца (01-31)	01
%W	Название дня недели	Monday
%w	День недели (0-6, 0 - воскресенье)	1
%H	Час (00-23)	13
%h	Час (01-12)	01
%i	Минуты (00-59)	30
%s	Секунды (00-59)	45
%p	AM или PM	PM

Объединение всехConcepts

Давайте завершим более сложным примером, который объединяет несколькоConcepts:

CREATE TABLE user_logins (
login_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
login_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO user_logins (user_id) VALUES (1), (2), (3);

SELECT
user_id,
login_time,
DATE_FORMAT(login_time, '%W, %M %d, %Y at %h:%i %p') AS formatted_login_time,
CASE
WHEN WEEKDAY(login_time) IN (5, 6) THEN 'Weekend Login'
ELSE 'Weekday Login'
END AS login_type,
TIMESTAMPDIFF(HOUR, login_time, NOW()) AS hours_since_login
FROM user_logins
WHERE login_time > DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK);

Этот пример создает таблицу user_logins, вставляет данные и затем запрашивает их с различными манипуляциями с датами и временем. Мы форматируем время входа, определяем, был ли это выходной или будний день, и calculates how many hours have passed since the login.

И вот мы и здесь, друзья! Мы путешествовали во времени с SQL, изучая типы данных дат и времени и функции. Помните, что практика делает perfect, так что не бойтесь экспериментировать с этими концепциями. Before you know it, вы будете гнуть время по своему желанию... по крайней мере, в ваших базах данных!

Credits: Image by storyset