【保存版】MySQLで日付をバッチリに変換！文字列からUNIXタイムスタンプまで

2024-06-14

MySQLで日付文字列をUnixタイムスタンプに変換する

書式

UNIX_TIMESTAMP(date_string)

ここで、date_stringは、変換対象の日付文字列です。文字列形式は、YYYY-MM-DD hh:mm:ss または YYYYMMDDhhmmss のいずれかである必要があります。

例

SELECT UNIX_TIMESTAMP('2024-06-13 21:21:00');

このクエリは、2024年6月13日 21:21:00 のUnixタイムスタンプを返します。結果は次のようになります。

1686815260

DATETIME型とTIMESTAMP型

UNIX_TIMESTAMP関数は、DATE型、DATETIME型、TIMESTAMP型のいずれかの値を引数として受け取ることができます。

DATE型には、時間情報が含まれません。UNIX_TIMESTAMP関数で変換すると、'00:00:00' の時間部分が追加されます。
DATETIME型とTIMESTAMP型には、時間情報が含まれています。UNIX_TIMESTAMP関数で変換すると、時間情報も保持されます。

注意点

UNIX_TIMESTAMP関数は、文字列形式の日付を解釈する際に、現在のタイムゾーンを使用します。そのため、変換結果が予期しないものになる可能性があります。変換対象の日付文字列がどのタイムゾーンのものなのかを意識する必要があります。
UNIX_TIMESTAMP関数は、引数が無効な形式の場合、NULLを返します。

FROM_UNIXTIME関数：Unixタイムスタンプを日付文字列に変換します。
STR_TO_DATE関数：文字列を指定した形式の日付に変換します。

これらの関数を組み合わせて使用することで、様々な日付処理を行うことができます。

例1：特定の日付文字列をUnixタイムスタンプに変換

SELECT UNIX_TIMESTAMP('2024-06-13 21:22:00');

例2：テーブルから日付文字列を取得し、Unixタイムスタンプに変換

SELECT UNIX_TIMESTAMP(created_at) AS unix_timestamp
FROM my_table;

このクエリは、my_table テーブルの created_at 列にある日付文字列をすべてUnixタイムスタンプに変換し、unix_timestamp 列として返します。

例3：Unixタイムスタンプを日付文字列に変換

SELECT FROM_UNIXTIME(1686815260);

このクエリは、Unixタイムスタンプ 1686815260 を日付文字列に変換し、返します。結果は次のようになります。

2024-06-13 21:21:00

例4：条件付きで日付文字列をUnixタイムスタンプに変換

SELECT UNIX_TIMESTAMP(created_at) AS unix_timestamp
FROM my_table
WHERE status = 'active';

これらの例は、UNIX_TIMESTAMP関数を使用して、様々な日付処理を行う方法を示しています。

MySQLで日付文字列をUnixタイムスタンプに変換するその他の方法

STR_TO_DATE関数とCAST関数

STR_TO_DATE関数とCAST関数を使用して、日付文字列をDATETIME型に変換し、UNIX_TIMESTAMP関数でUnixタイムスタンプに変換することができます。

SELECT UNIX_TIMESTAMP(CAST(STR_TO_DATE('2024-06-13 21:23:00', '%Y-%m-%d %H:%i:%s') AS DATETIME));

利点:

STR_TO_DATE関数を使用して、様々な形式の日付文字列を解析することができます。

CAST関数とUNIX_TIMESTAMP関数を2つ使用するため、UNIX_TIMESTAMP関数単体よりも記述量が多くなります。

CONCAT関数とSUBSTRING関数

SELECT UNIX_TIMESTAMP(CONCAT(SUBSTRING('2024-06-13 21:23:00', 1, 4), SUBSTRING('2024-06-13 21:23:00', 6, 2), SUBSTRING('2024-06-13 21:23:00', 9, 2), SUBSTRING('2024-06-13 21:23:00', 12, 2), SUBSTRING('2024-06-13 21:23:00', 15, 2)));

日付文字列の形式に依存せずに変換することができます。

日付文字列の形式が不正な場合、エラーが発生する可能性があります。

CASE WHEN式

SELECT UNIX_TIMESTAMP(
  CASE
    WHEN SUBSTRING('2024-06-13 21:23:00', 1, 4) = 'YYYY' THEN
      CAST(STR_TO_DATE('2024-06-13 21:23:00', '%Y-%m-%d %H:%i:%s') AS DATETIME)
    ELSE
      CAST(CONCAT(SUBSTRING('2024-06-13 21:23:00', 1, 4), SUBSTRING('2024-06-13 21:23:00', 6, 2), SUBSTRING('2024-06-13 21:23:00', 9, 2), SUBSTRING('2024-06-13 21:23:00', 12, 2), SUBSTRING('2024-06-13 21:23:00', 15, 2)) AS DATETIME)
  END
);