SQL - 日付と時間：初めての人向けの包括ガイド

こんにちは、未来のデータベース魔术師さんたち！今日は、SQLにおける日付と時間の興味深い世界に踏み込みます。あなたの近所の親切なコンピュータ教師として、私は多くの例と説明を交えてこの旅を案内します。那么、バーチャルなタイムマシンを手に取り、始めましょう！

SQLにおける日付と時間のデータ型

日付と時間を操作する前に、SQLがこの情報をどのように保存するかを理解する必要があります。これらのデータ型は、時系列データ専用に設計された特別なコンテナと考えられます。

DATE

DATEデータ型は、YYYY-MM-DDの形式で日付を保存します。

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, '会社のピクニック', '2023-07-15');

この例では、eventsテーブルを作成し、2023年7月15日に予定されている会社のピクニックを追加しています。DATEデータ型は、時間情報なしで日付を保存することを確保します。

TIME

TIMEデータ型は、HH:MI:SSの形式で時間を保存します。

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, 'SQLの紹介', '09:30:00');

ここでは、時間割を作成し、午前9時30分に始まるSQLのクラスを追加しています。TIMEデータ型は、この時間を正確に保存します。

DATETIME

DATETIMEデータ型は、日付と時間を兼ね備え、通常YYYY-MM-DD HH:MI:SSの形式で保存されます。

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, 'ジョン・ドー', '2023-06-20 14:30:00');

この例では、ジョン・ドーの医師の予約を2023年6月20日14時30分にスケジュールしています。DATETIMEデータ型は、日付と時間を1つのカラムに保存します。

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, '私の最初のブログ投稿', 'こんにちは、世界！');

この例では、last_updatedカラムは、レコードが挿入または更新されたときに自動的に現在の日付と時間に設定されます。

SQLにおける日付と時間の関数

日付と時間を保存する方法を知ったところで、このデータを操作するための魔法のような関数を探ってみましょう。これらの関数は、私たちのSQLツールキットにおけるタイムトラベリングツールだと思っています！

現在の日付と時間

まず基本的なことから始めましょう - 現在の日付と時間を取得する方法です。

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

このクエリは、現在の日付、時間、日時を取得します。便利ですよね？

日付の一部を抽出

時々、日付の特定の部分だけが必要な場合があります。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ヶ月前の日付、および2つの日付間の日数を計算しています。まるでタイムマシンを持っているようなものです！

日付のフォーマット

時々、日付を特定の形式で表示する必要があります。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;

このクエリは、日付を「火曜日、6月20日、2023年」という形式で、時間を「02:30 PM」という形式でフォーマットします。まるで日付を格式ばかにするようなものです！

以下は、よく使用されるフォーマット指定子の一覧です：

指定子	説明	例
%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

すべてを組み合わせる

最後に、いくつかの概念を組み合わせたより複雑な例を紹介します：

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 '週末ログイン'
ELSE '平日ログイン'
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テーブルを作成し、データを追加した後、さまざまな日付と時間の操作を行ったクエリを実行しています。ログイン時間をフォーマットし、週末か平日かを判断し、ログインから何時間が経過したかを計算しています。

そして、ここまでが、SQLで日付と時間を操作する方法のすべてです！タイムトラベリングを楽しみながら、データベースの時間を操るスキルを身につけましょう。練習すれば、すぐにデータベースの中で時間を自在に操ることができるようになります！

Credits: Image by storyset