Angular Ansible Apache HTTP Server C C++ CMake Composer CSS Cypress date-fns Django Django REST Framework Eigen3 ESLint FastAPI Git GNU Make HTML htmx HTTP JavaScript Julia LaTeX MariaDB Node.js NumPy Octave pandas Perl PHP PostgreSQL Pygame Python PyTorch Qt R SQLite Tailwind CSS

UNIX タイムスタンプを人間が読める形式に変換する：MariaDB の FROM_UNIXTIME 関数徹底解説


MariaDB の FROM_UNIXTIME 関数は、UNIX タイムスタンプ を人間が読める形式の日付時刻に変換するものです。UNIX タイムスタンプとは、1970 年 1 月 1 日 00:00:00 UTC から経過した秒数で表される時間表現です。

構文

FROM_UNIXTIME(unix_timestamp_value [, format])
  • format: (オプション) 出力形式を指定する文字列。指定しない場合は '%Y-%m-%d %H:%i:%S' になります。
  • unix_timestamp_value: UNIX タイムスタンプ値 (整数)

出力形式

format パラメータで指定できる出力形式は以下の通りです。

  • %p: 午前/午後 (例: AM)
  • %S: 秒 (例: 00)
  • %i: 分 (例: 06)
  • %H: 時 (例: 08)
  • %d: 日 (例: 23)
  • %m: 月 (例: 05)
  • %Y: 年 (例: 2024)

例

SELECT FROM_UNIXTIME(1653520000);

このクエリは、UNIX タイムスタンプ 1653520000 (2023-05-23 00:00:00 UTC) を人間が読める形式の日付時刻に変換し、以下のように出力します。

2023-05-23 00:00:00

SELECT FROM_UNIXTIME(1653520000, '%Y-%m-%d %H:%i:%S %p');

このクエリは、同じ UNIX タイムスタンプを別の形式に変換し、以下のように出力します。

2023-05-23 08:00:00 AM

注意点

  • MariaDB 10.2.13 以降では、FROM_UNIXTIME 関数は最大 10 桁の整数値 (マイクロ秒まで) を処理できます。
  • FROM_UNIXTIME 関数は、32 ビット整数型の値しか処理できません。そのため、2038 年 1 月 19 日 5 時 14 分 7 秒 (UTC) より後の日付時刻を扱う場合は注意が必要です。
  • ログファイルなどのタイムスタンプ付きデータを解析する
  • 特定の日付時刻に対応する UNIX タイムスタンプを生成する
  • データベースに格納されている UNIX タイムスタンプを人間が読める形式の日付時刻に変換する

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2023-05-23 08:00:00'));

このクエリは、2023 年 5 月 23 日 8 時 00 分 00 秒 (UTC) の日付時刻を UNIX タイムスタンプに変換し、以下のように出力します。

1653520000

データベースから UNIX タイムスタンプを取得し、日付時刻に変換

SELECT FROM_UNIXTIME(created_at) AS timestamp
FROM my_table;

このクエリは、my_table テーブルの created_at 列に格納されている UNIX タイムスタンプをすべて取得し、日付時刻に変換して timestamp という列名で出力します。

特定の期間に含まれるレコードを抽出

SELECT *
FROM my_table
WHERE created_at BETWEEN FROM_UNIXTIME(1653516400) AND FROM_UNIXTIME(1653523600);

このクエリは、my_table テーブルから、2023 年 5 月 23 日 00 時 00 分 00 秒 (UTC) から 2023 年 5 月 23 日 8 時 00 分 00 秒 (UTC) の間に作成されたレコードをすべて抽出します。

タイムゾーンを考慮した変換

SELECT FROM_UNIXTIME(1653520000) AT TIME ZONE 'America/Los_Angeles';

このクエリは、UNIX タイムスタンプ 1653520000 (2023-05-23 00:00:00 UTC) をロサンゼルス標準時 (PST) に変換し、以下のように出力します。

2023-05-22 16:00:00 PST

カスタムフォーマットでの出力

SELECT FROM_UNIXTIME(1653520000, '%Y-%m-%d %H:%M:%S %z');

このクエリは、UNIX タイムスタンプ 1653520000 (2023-05-23 00:00:00 UTC) を以下の形式に変換し、出力します。

2023-05-23 08:00:00 +00:00

DATE_FORMAT 関数

DATE_FORMAT 関数は、文字列の日付時刻を指定した形式に変換するものです。UNIX タイムスタンプを文字列に変換してから DATE_FORMAT 関数を使用することで、日付時刻に変換することができます。

SELECT DATE_FORMAT(CAST(unix_timestamp_value AS CHAR), '%Y-%m-%d %H:%i:%S');

このクエリは、UNIX タイムスタンプ unix_timestamp_value を文字列に変換し、%Y-%m-%d %H:%i:%S 形式でフォーマットして出力します。

CONVERT 関数

CONVERT 関数は、値をあるデータ型から別のデータ型に変換するものです。UNIX タイムスタンプを DATETIME 型に変換してから、フォーマット文字列を使用して日付時刻に変換することができます。

SELECT CONVERT(unix_timestamp_value, DATETIME) AS formatted_datetime;

このクエリは、UNIX タイムスタンプ unix_timestamp_value を DATETIME 型に変換し、formatted_datetime という列名で出力します。

ストアドプロシージャ

ストアドプロシージャを使用して、独自のロジックで UNIX タイムスタンプを日付時刻に変換することができます。

CREATE PROCEDURE convert_unixtime_to_datetime(IN unix_timestamp BIGINT, OUT formatted_datetime DATETIME)
BEGIN
  SET formatted_datetime = FROM_UNIXTIME(unix_timestamp);
END;

CALL convert_unixtime_to_datetime(1653520000, @formatted_datetime);
SELECT @formatted_datetime;

このストアドプロシージャは、convert_unixtime_to_datetime という名前で定義され、UNIX タイムスタンプを引数として受け取り、日付時刻を戻り値として返します。

サードパーティ製ライブラリ

Moment.js や Day.js などのサードパーティ製ライブラリを使用して、UNIX タイムスタンプを日付時刻に変換することができます。これらのライブラリは、より柔軟なフォーマットや機能を提供することがあります。

方法利点欠点
FROM_UNIXTIME 関数シンプルでわかりやすいタイムゾーンの考慮が難しい
DATE_FORMAT 関数柔軟なフォーマット指定が可能文字列への変換が必要
CONVERT 関数型変換とフォーマットを同時にできるやや複雑な構文
ストアドプロシージャ独自のロジックを実装できる開発とメンテナンスの手間がかかる
サードパーティ製ライブラリ柔軟性と機能が豊富ライブラリの導入と理解が必要