MySQL クエリでタイムスタンプフィールドを日付に変換する
このチュートリアル記事では、MySQL クエリでタイムスタンプフィールドを日付フィールドに変換する方法を説明します。これは、データを適切にクエリするときにフォーマットを使用する方法を示しています。
日付形式を使用して MySQL のタイムスタンプを変換する
TIMESTAMP データ型を使用すると、日付と時刻の両方を登録できます。1970 年 1 月 1 日の最初の 1 秒から 2038 年 1 月 19 日までの日付の範囲を正確に 3 時間 14 分 7 秒で有効にする 19 個の固定文字があります。
DATE_FORMAT 関数を使用すると、テーブルからフィールドを取得し、MySQL クエリで目的の形式に変換できます。以下は、operations テーブルの値です。これにより、シャットダウンなどの運用イベントの詳細が工場で登録されます。
| shutdown |
| ------------------- |
| 2021-12-31 09:45:07 |
以下は、列 shutdown を取得し、デフォルト形式 YYYY-MM-DD HH:MM:SS のタイムスタンプから形式 DD-MM-YYYY の日付に変換する変換の例です。
CREATE TABLE operations (
user TEXT NOT NULL,
shutdown TIMESTAMP
);
INSERT INTO operations VALUES ('Mark', '2021-12-31 09:45:07');
SELECT DATE_FORMAT(operations.shutdown, '%d %m %Y')
AS 'Shutdown Date'
FROM operations
出力：
| Shutdown Date |
|:--------------|
| 31 12 2021 |
時間、分、秒の値にそれぞれ%H、%i、%s を含めることで、最終的な日付形式に時刻を含めることもできます。
CREATE TABLE operations (
user TEXT NOT NULL,
shutdown TIMESTAMP
);
INSERT INTO operations VALUES ('Mark', '2021-12-31 09:45:07');
SELECT DATE_FORMAT(operations.shutdown, '%d %m %Y %h%:%i%:%s')
AS 'Shutdown Date'
FROM operations
出力：
| Shutdown Date |
|:---------------------|
| 31 12 2021 09:45:07 |
%m はすでに月次数値に割り当てられているため、%i 記号は数分間使用されます。
Unixtime を使用して MySQL のタイムスタンプを変換する
タイムスタンプが適切な形式の場合、日付/時刻の形式 31 12 2021 09:45:07 は次のようになります：1640943907。FROM_UNIXTIME を使用して、数列を日付/時刻形式に変換できます。
CREATE TABLE operations (
user TEXT NOT NULL,
shutdown int(11)
);
INSERT INTO operations VALUES ('Mark', 1640943907);
SELECT DATE_FORMAT(FROM_UNIXTIME(operations.shutdown), '%d %m %Y %h%:%i%:%s')
AS 'Shutdown Date'
FROM operations
結果：
| Shutdown Date |
|:---------------------|
| 31 12 2021 09:45:07 |
上記のように、タイムスタンプは、FROM_UNIXTIME を追加するだけで同じ形式に変換され、数列を目的の形式に変換しました。
MySQL で Cast を使用して Unixtime を変換する
DATE_FORMAT の代わりに CAST を使用して、UNIXTIME 形式を日付に変換することもできます。
CREATE TABLE operations (
user TEXT NOT NULL,
shutdown int(11)
);
INSERT INTO operations VALUES ('Mark', 1640943907);
SELECT CAST(FROM_UNIXTIME(operations.shutdown)
AS DATE)
AS 'Shutdown Date'
FROM operations
出力：
| Shutdown Date |
|:--------------|
| 2021-12-31 |
ただし、この方法では、必要に応じて日付をフォーマットする機能が制限されるため、DATE_FORMAT を使用することをお勧めします。