MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数

8.0 English
5.7 English
5.6 English
5.5 English

MySQL 5.6 リファレンスマニュアル / 関数と演算子 / 日付および時間関数

12.7 日付および時間関数

このセクションでは、時間値の処理に使用できる関数について説明します。各日付日時型が持つ値の範囲、および値を指定する際の有効な書式については、セクション11.3「日付と時間型」を参照してください。

表 12.13 日付/時間関数

名前	説明
`ADDDATE()`	日付値に時間値 (間隔) を加算します
`ADDTIME()`	時間を加算します
`CONVERT_TZ()`	あるタイムゾーンから別のタイムゾーンに変換します
`CURDATE()`	現在の日付を返します
`CURRENT_DATE()`, `CURRENT_DATE`	CURDATE() のシノニムです
`CURRENT_TIME()`, `CURRENT_TIME`	CURTIME() のシノニムです
`CURRENT_TIMESTAMP()`, `CURRENT_TIMESTAMP`	NOW() のシノニムです
`CURTIME()`	現在の時間を返します
`DATE()`	日付または日付時間式の日付部分を抽出します
`DATE_ADD()`	日付値に時間値 (間隔) を加算します
`DATE_FORMAT()`	日付を指定された書式に設定します
`DATE_SUB()`	日付から時間値 (間隔) を引きます
`DATEDIFF()`	2 つの日付の差を求めます
`DAY()`	DAYOFMONTH() のシノニムです
`DAYNAME()`	曜日の名前を返します
`DAYOFMONTH()`	月の日を返します (0 - 31)
`DAYOFWEEK()`	引数の曜日インデックスを返します
`DAYOFYEAR()`	年の日を返します (1 - 366)
`EXTRACT()`	日付の一部を抽出します
`FROM_DAYS()`	日数を日付に変換します
`FROM_UNIXTIME()`	UNIX タイムスタンプを日付として書式設定します
`GET_FORMAT()`	日付書式文字列を返します
`HOUR()`	時を抽出します
`LAST_DAY`	引数の月の最終日を返します
`LOCALTIME()`, `LOCALTIME`	NOW() のシノニムです
`LOCALTIMESTAMP`, `LOCALTIMESTAMP()`	NOW() のシノニムです
`MAKEDATE()`	年と年間通算日から日付を作成します
`MAKETIME()`	時、分、秒から時間を作成します
`MICROSECOND()`	引数からマイクロ秒を返します
`MINUTE()`	引数から分を返します
`MONTH()`	渡された日付から月を返します
`MONTHNAME()`	月の名前を返します
`NOW()`	現在の日付と時間を返します
`PERIOD_ADD()`	年月に期間を加算します
`PERIOD_DIFF()`	期間内の月数を返します
`QUARTER()`	日付引数から四半期を返します
`SEC_TO_TIME()`	秒を「HH:MM:SS」形式に変換します
`SECOND()`	秒 (0-59) を返します
`STR_TO_DATE()`	文字列を日付に変換します
`SUBDATE()`	3 つの引数で呼び出されるときは DATE_SUB() のシノニムです
`SUBTIME()`	時間の差を求めます
`SYSDATE()`	この関数が実行される時間を返します
`TIME()`	渡された式の時部分を抽出します
`TIME_FORMAT()`	時間として書式設定します
`TIME_TO_SEC()`	秒に変換された引数を返します
`TIMEDIFF()`	時間の差を求めます
`TIMESTAMP()`	引数が 1 つの場合、この関数は日付または日付時間式を返します。引数が 2 つの場合、引数の合計を返します
`TIMESTAMPADD()`	日付時間式に間隔を加算します
`TIMESTAMPDIFF()`	日付時間式から間隔を減算します
`TO_DAYS()`	日に変換された日付引数を返します
`TO_SECONDS()`	0 年以降の秒数に変換された日付または日付時間引数を返します
`UNIX_TIMESTAMP()`	UNIX タイムスタンプを返します
`UTC_DATE()`	現在の UTC 日付を返します
`UTC_TIME()`	現在の UTC 時間を返します
`UTC_TIMESTAMP()`	現在の UTC 日付と時間を返します
`WEEK()`	週番号を返します
`WEEKDAY()`	曜日インデックスを返します
`WEEKOFYEAR()`	日付の暦週を返します (0 - 53)
`YEAR()`	年を返します
`YEARWEEK()`	年と週を返します

次に、日付関数の使用例を示します。次のクエリーは、過去 30 日以内の date_col 値を含むすべての行を選択します。

mysql> SELECT something FROM tbl_name
    -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

このクエリーは、将来の日付を持つ行も選択します。

通常、日付値が要求される関数では、日付時間値が受け入れられ、時間の部分は無視されます。通常、時間値が要求される関数では、日付時間値が受け入れられ、日付の部分は無視されます。

現在の日付または時間をそれぞれ返す関数は、クエリー実行の開始時にクエリーごとに 1 回だけ評価されます。つまり、NOW() などの関数が単一クエリー内で複数回参照されても、常に同じ結果が生成されます。(設計上、単一クエリーにはストアドプログラム (ストアドルーチン、トリガー、またはイベント) の呼び出し、およびそのプログラムによって呼び出されるすべてのサブプログラムも含まれています。)この原則は、CURDATE()、CURTIME()、UTC_DATE()、UTC_TIME()、UTC_TIMESTAMP()、およびそれらのシノニムにも適用されます。

CURRENT_TIMESTAMP()、CURRENT_TIME()、CURRENT_DATE()、および FROM_UNIXTIME() 関数は、time_zone システム環境変数の値として使用できる接続の現在のタイムゾーンで値を返します。さらに、UNIX_TIMESTAMP() では、その引数が現在のタイムゾーンでの日付時間値であるとみなされます。セクション10.6「MySQL Server でのタイムゾーンのサポート」を参照してください。

「zero」日付または '2001-11-00' のような不完全な日付とともに使用できる日付関数もありますが、使用できない日付関数もあります。通常、日付の一部を抽出する関数は不完全な日付でも正しく機能するため、ゼロ以外の値が要求される場合に 0 を返すことができます。例:

mysql> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');
        -> 0, 0

その他の関数では完全な日付が要求され、日付が不完全な場合は NULL が返されます。これらには、日付演算を実行する関数や日付の一部を名前にマップする関数が含まれます。例:

mysql> SELECT DATE_ADD('2006-05-00',INTERVAL 1 DAY);
        -> NULL
mysql> SELECT DAYNAME('2006-05-00');
        -> NULL

MySQL 5.6.4 の時点では、いくつかの関数は引数として DATE() 関数の値を渡す際により厳密になったため、ゼロの日付部分を持つ不完全な日付は拒否されます。CONVERT_TZ()、DATE_ADD()、DATE_SUB()、DAYOFYEAR()、LAST_DAY()、TIMESTAMPDIFF()、TO_DAYS()、TO_SECONDS()、WEEK()、WEEKDAY()、WEEKOFYEAR()、YEARWEEK() 関数が影響を受けます。5.6.5 の LAST_DAY() では、この制限は緩やかで、ゼロの日付部分は許可されていました。

MySQL 5.6.4 以上では、マイクロ秒までの精度を持つ小数秒が TIME、DATETIME、および TIMESTAMP 値でサポートされています。時間引数を取る関数は、小数秒を含む値を受け入れます。時間関数からの戻り値には、必要に応じて小数秒が含まれます。

ADDDATE(date,INTERVAL expr unit)、ADDDATE(expr,days)

INTERVAL 形式の 2 番目の引数を付けて呼び出されると、ADDDATE() は DATE_ADD() のシノニムになります。関連する関数 SUBDATE() は DATE_SUB() のシノニムです。INTERVAL unit 引数については、DATE_ADD() の説明を参照してください。
```
mysql> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
        -> '2008-02-02'
mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
        -> '2008-02-02'
```
days 形式の 2 番目の引数を付けて呼び出されると、MySQL では expr に加算される整数の日数として処理されます。
```
mysql> SELECT ADDDATE('2008-01-02', 31);
        -> '2008-02-02'
```

ADDTIME(expr1,expr2)

ADDTIME() は expr2 と expr1 を加算し、その結果を返します。expr1 は時間または日付時間式であり、expr2 は時間式です。

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
        -> '2008-01-02 01:01:01.000001'
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
        -> '03:00:01.999997'

CONVERT_TZ(dt,from_tz,to_tz)

CONVERT_TZ() は、日付時間値 dt を from_tz で指定されたタイムゾーンから、to_tz で指定されたタイムゾーンに変換し、結果の値を返します。タイムゾーンは、セクション10.6「MySQL Server でのタイムゾーンのサポート」で説明されているように指定されます。引数が無効な場合、この関数は NULL を返します。

from_tz から UTC に変換される際に、値が TIMESTAMP でサポートされている範囲から外れている場合は、変換が実行されません。TIMESTAMP の範囲については、セクション11.1.2「日付と時間型の概要」で説明されています。
```
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
        -> '2004-01-01 13:00:00'
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'
```
注記

名前付きタイムゾーン ('MET' または 'Europe/Moscow' など) を使用するには、タイムゾーンテーブルが正しく設定されている必要があります。手順については、セクション10.6「MySQL Server でのタイムゾーンのサポート」を参照してください。
CURDATE()

関数が文字列と数値コンテキストのどちらで使用されているのかに応じて、現在の日付を 'YYYY-MM-DD' または YYYYMMDD 書式の値として返します。
```
mysql> SELECT CURDATE();
        -> '2008-06-13'
mysql> SELECT CURDATE() + 0;
        -> 20080613
```
CURRENT_DATE、CURRENT_DATE()

CURRENT_DATE および CURRENT_DATE() は CURDATE() のシノニムです。
CURRENT_TIME、CURRENT_TIME([fsp])

CURRENT_TIME および CURRENT_TIME() は CURTIME() のシノニムです。
CURRENT_TIMESTAMP、CURRENT_TIMESTAMP([fsp])

CURRENT_TIMESTAMP および CURRENT_TIMESTAMP() は NOW() のシノニムです。
CURTIME([fsp])

関数が文字列と数値コンテキストのどちらで使用されているのかに応じて、現在の時間を 'HH:MM:SS' または HHMMSS 書式の値で返します。値は、現在のタイムゾーンで表現されています。

MySQL 5.6.4 の時点では、0 から 6 までの小数秒の精度を指定するために fsp 引数が指定されている場合は、その桁数の小数秒部分が戻り値に含まれます。5.6.4 よりも前では、すべての引数が無視されます。
```
mysql> SELECT CURTIME();
        -> '23:50:26'
mysql> SELECT CURTIME() + 0;
        -> 235026.000000
```
DATE(expr)

日付または日付時間式 expr の日付部分を抽出します。
```
mysql> SELECT DATE('2003-12-31 01:02:03');
        -> '2003-12-31'
```
DATEDIFF(expr1,expr2)

DATEDIFF() は、ある日付から別の日付までの日数の値として表現された expr1 − expr2 を返します。expr1 および expr2 は、日付または日付時間式です。値の日付部分のみが計算に使用されます。
```
mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
        -> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
        -> -31
```

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

これらの関数は日付演算を実行します。date 引数は、開始日付値または開始日付時間値を指定します。expr は、開始日付から加算または減算される間隔値を指定する式です。expr は文字列であり、負の間隔の場合は「-」で始めることができます。unit は、式を解釈する際の単位を示すキーワードです。

INTERVAL キーワードおよび unit 指定子では、大文字と小文字が区別されません。

次の表には、unit 値ごとに要求される形式の expr 引数を表示します。

`unit` 値	要求される `expr` 書式
`MICROSECOND`	`MICROSECONDS`
`SECOND`	`SECONDS`
`MINUTE`	`MINUTES`
`HOUR`	`HOURS`
`DAY`	`DAYS`
`WEEK`	`WEEKS`
`MONTH`	`MONTHS`
`QUARTER`	`QUARTERS`
`YEAR`	`YEARS`
`SECOND_MICROSECOND`	`'SECONDS.MICROSECONDS'`
`MINUTE_MICROSECOND`	`'MINUTES:SECONDS.MICROSECONDS'`
`MINUTE_SECOND`	`'MINUTES:SECONDS'`
`HOUR_MICROSECOND`	`'HOURS:MINUTES:SECONDS.MICROSECONDS'`
`HOUR_SECOND`	`'HOURS:MINUTES:SECONDS'`
`HOUR_MINUTE`	`'HOURS:MINUTES'`
`DAY_MICROSECOND`	`'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'`
`DAY_SECOND`	`'DAYS HOURS:MINUTES:SECONDS'`
`DAY_MINUTE`	`'DAYS HOURS:MINUTES'`
`DAY_HOUR`	`'DAYS HOURS'`
`YEAR_MONTH`	`'YEARS-MONTHS'`

戻り値は引数によって異なります。

第 1 引数が DATETIME (または TIMESTAMP) 値である場合と、第 1 引数が DATE で、unit 値に HOURS、MINUTES、または SECONDS が使用されている場合は、DATETIME です。
それ以外の場合は文字列です。

必ず結果が DATETIME になるようにするには、CAST() を使用すれば、第 1 引数を DATETIME に変換できます。

MySQL では、expr 書式の句読点区切り文字が許可されます。表には、提案される区切り文字を表示します。date 引数が DATE 値であり、計算に YEAR、MONTH、および DAY 部分のみが含まれる (つまり、時間部分は含まれない) 場合は、結果が DATE 値になります。その他の場合は、結果が DATETIME 値になります。

また、日付演算は、+ または - 演算子とともに INTERVAL を使用して実行することもできます。

date + INTERVAL expr unit
date - INTERVAL expr unit

INTERVAL expr unit は、他方の側の式が日付または日付間値である場合に、+ 演算子の一方の側で許可されます。- 演算子では、間隔から日付または日付間値を抽出しても意味がないため、INTERVAL expr unit は右側でのみ許可されます。

mysql> SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
        -> '2009-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '2008-12-31';
        -> '2009-01-01'
mysql> SELECT '2005-01-01' - INTERVAL 1 SECOND;
        -> '2004-12-31 23:59:59'
mysql> SELECT DATE_ADD('2000-12-31 23:59:59',
    ->                 INTERVAL 1 SECOND);
        -> '2001-01-01 00:00:00'
mysql> SELECT DATE_ADD('2010-12-31 23:59:59',
    ->                 INTERVAL 1 DAY);
        -> '2011-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
    ->                 INTERVAL '1:1' MINUTE_SECOND);
        -> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2005-01-01 00:00:00',
    ->                 INTERVAL '1 1:1:1' DAY_SECOND);
        -> '2004-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
    ->                 INTERVAL '-1 10' DAY_HOUR);
        -> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    ->            INTERVAL '1.999999' SECOND_MICROSECOND);
        -> '1993-01-01 00:00:01.000001'

指定した間隔値 (unit キーワードから要求されるすべての間隔部分は含まれません) が短すぎる場合は、MySQL では間隔値の左端部分が省略されているとみなされます。たとえば、DAY_SECOND の unit を指定した場合は、expr の値には日、時間、分、秒の部分が含まれるとみなされます。'1:10' のような値を指定すると、MySQL では日と時間の部分が欠落していて、値は分と秒を表しているとみなされます。つまり、'1:10' DAY_SECOND は、'1:10' MINUTE_SECOND と同等の方法で解釈されます。これは、MySQL で TIME 値が時間ではなく経過時間を表していると解釈される方法に類似しています。

expr は文字列として処理されるため、INTERVAL に文字列以外の値を指定する場合は注意してください。たとえば、間隔指定子が HOUR_MINUTE の場合は、6/4 が 1.5000 に評価され、1 時間 5000 分として処理されます。

mysql> SELECT 6/4;
        -> 1.5000
mysql> SELECT DATE_ADD('2009-01-01', INTERVAL 6/4 HOUR_MINUTE);
        -> '2009-01-04 12:20:00'

間隔値が予想どおりに解釈されるようにするには、CAST() 演算を使用します。6/4 を 1 時間 5 分として処理するには、小数点以下の桁数が 1 桁の DECIMAL 値にキャストします。

mysql> SELECT CAST(6/4 AS DECIMAL(3,1));
        -> 1.5
mysql> SELECT DATE_ADD('1970-01-01 12:00:00',
    ->                 INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE);
        -> '1970-01-01 13:05:00'

時間部分が含まれるものを日付値に加算したり、日付値から減算したりすると、自動的に結果が日付時間値に変換されます。

mysql> SELECT DATE_ADD('2013-01-01', INTERVAL 1 DAY);
        -> '2013-01-02'
mysql> SELECT DATE_ADD('2013-01-01', INTERVAL 1 HOUR);
        -> '2013-01-01 01:00:00'

MONTH、YEAR_MONTH、または YEAR を加算した結果の日付に、新しい月の最大日数よりも大きな日が含まれる場合は、その日が新しい月の最大日数に調整されます。

mysql> SELECT DATE_ADD('2009-01-30', INTERVAL 1 MONTH);
        -> '2009-02-28'

日付算術演算では、完全な日付が必須であるため、'2006-07-00' のような不完全な日付や、誤った形式の日付では正常に機能しません。

mysql> SELECT DATE_ADD('2006-07-00', INTERVAL 1 DAY);
        -> NULL
mysql> SELECT '2005-03-32' + INTERVAL 1 MONTH;
        -> NULL

DATE_FORMAT(date,format)

format 文字列に従って、date 値を書式設定します。

次の指定子が format 文字列で使用されている場合があります。書式指定子文字の前には、「%」文字を付ける必要があります。

指定子	説明
`%a`	簡略曜日名 (`Sun`..`Sat`)
`%b`	簡略月名 (`Jan`..`Dec`)
`%c`	月、数字 (`0`..`12`)
`%D`	英語のサフィクスを持つ日付 (`0th`, `1st`, `2nd`, `3rd`, …)
`%d`	日、数字 (`00`..`31`)
`%e`	日、数字 (`0`..`31`)
`%f`	マイクロ秒 (`000000`..`999999`)
`%H`	時間 (`00`..`23`)
`%h`	時間 (`01`..`12`)
`%I`	時間 (`01`..`12`)
`%i`	分、数字 (`00`..`59`)
`%j`	年間通算日 (`001`..`366`)
`%k`	時 (`0`..`23`)
`%l`	時 (`1`..`12`)
`%M`	月名 (`January`..`December`)
`%m`	月、数字 (`00`..`12`)
`%p`	`AM` または `PM`
`%r`	時間、12 時間単位 (`hh:mm:ss` に `AM` または `PM` が続く)
`%S`	秒 (`00`..`59`)
`%s`	秒 (`00`..`59`)
`%T`	時間、24 時間単位 (`hh:mm:ss`)
`%U`	週 (`00`..`53`)、日曜日が週の初日、`WEEK()` モード 0
`%u`	週 (`00`..`53`)、月曜日が週の初日、`WEEK()` モード 1
`%V`	週 (`01`..`53`)、日曜日が週の初日、`WEEK()` モード 2、`%X` とともに使用
`%v`	週 (`01`..`53`)、月曜日が週の初日、`WEEK()` モード 3、`%x` とともに使用
`%W`	曜日名 (`Sunday`..`Saturday`)
`%w`	曜日 (`0`=Sunday..`6`=Saturday)
`%X`	年間の週、日曜日が週の初日、数字、4 桁、`%V` とともに使用
`%x`	年間の週、月曜日が週の初日、数字、4 桁、`%v` とともに使用
`%Y`	年、数字、4 桁
`%y`	年、数字 (2 桁)
`%%`	リテラル「`%`」文字
`%x`	`x` (上記にないすべての「`x`」)

MySQL では '2014-00-00' などの不完全な日付の格納が許可されるため、月および日の指定子の範囲はゼロから始まります。

日および月の名前と略語に使用される言語は、lc_time_names システム変数 (セクション10.7「MySQL Server のロケールサポート」) の値で制御されます。

%U、%u、%V、および %v 指定子のモード値については、WEEK() 関数の説明を参照してください。モードによって、週番号が付与される方法が影響を受けます。

DATE_FORMAT() は、ASCII 以外の文字を含む月および週の名前を返すことができるように、character_set_connection および collation_connection で指定された文字セットおよび照合順序を含む文字列を返します。

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'

DATE_SUB(date,INTERVAL expr unit)

DATE_ADD() の説明を参照してください。
DAY(date)

DAY() は DAYOFMONTH() のシノニムです。
DAYNAME(date)

date に対応する曜日の名前を返します。名前に使用される言語は、lc_time_names システム変数 (セクション10.7「MySQL Server のロケールサポート」) の値で制御されます。
```
mysql> SELECT DAYNAME('2007-02-03');
        -> 'Saturday'
```
DAYOFMONTH(date)

1 から 31 までの範囲内で date に対応する日を返します。'0000-00-00' や '2008-00-00' のように日の部分がゼロの場合は、0 を返します。
```
mysql> SELECT DAYOFMONTH('2007-02-03');
        -> 3
```
DAYOFWEEK(date)

date の曜日インデックス (1 = Sunday、2 = Monday、…、7 = Saturday) を返します。これらのインデックス値は、ODBC 標準に対応しています。
```
mysql> SELECT DAYOFWEEK('2007-02-03');
        -> 7
```
DAYOFYEAR(date)

1 から 366 までの範囲内で date に対応する通日を返します。
```
mysql> SELECT DAYOFYEAR('2007-02-03');
        -> 34
```

EXTRACT(unit FROM date)

EXTRACT() 関数では、DATE_ADD() または DATE_SUB() と同じ単位指定子が使用されますが、データ演算が実行されるのではなく、データから一部が抽出されます。

mysql> SELECT EXTRACT(YEAR FROM '2009-07-02');
       -> 2009
mysql> SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');
       -> 200907
mysql> SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');
       -> 20102
mysql> SELECT EXTRACT(MICROSECOND
    ->                FROM '2003-01-02 10:30:00.000123');
        -> 123

FROM_DAYS(N)

日数 N が指定され、DATE 値を返します。
```
mysql> SELECT FROM_DAYS(730669);
        -> '2007-07-03'
```
古い日付では、FROM_DAYS() を慎重に使用してください。グレゴリオ暦 (1582) の出現よりも前の値とともに使用することを目的に設計されていません。セクション12.8「MySQL で使用されるカレンダー」を参照してください。
FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

関数が文字列と数値のどちらのコンテキストで使用されたのかに応じて、unix_timestamp 引数の表現を 'YYYY-MM-DD HH:MM:SS' または YYYYMMDDHHMMSS 書式の値として返します。値は、現在のタイムゾーンで表現されています。unix_timestamp は、UNIX_TIMESTAMP() 関数で生成されるような内部タイムスタンプ値です。

format が指定されている場合は、DATE_FORMAT() 関数のエントリで一覧表示される場合と同じ方法で使用される format 文字列に従って、結果が書式設定されます。
```
mysql> SELECT FROM_UNIXTIME(1196440219);
        -> '2007-11-30 10:30:19'
mysql> SELECT FROM_UNIXTIME(1196440219) + 0;
        -> 20071130103019.000000
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
    ->                      '%Y %D %M %h:%i:%s %x');
        -> '2007 30th November 10:30:59 2007'
```
注: UNIX_TIMESTAMP() および FROM_UNIXTIME() を使用して TIMESTAMP 値と Unix タイムスタンプ値とを変換する場合は、どちらの方向でもマッピングが 1 対 1 ではないため、変換は不可逆です。詳細は UNIX_TIMESTAMP() 関数の説明を参照してください。

GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})

書式文字列を返します。この関数は、DATE_FORMAT() およびSTR_TO_DATE() 関数と組み合わせて使用すると便利です。

1 番目と 2 番目の引数に値を指定できるため、複数の書式文字列を生成できます (使用される指定子については、DATE_FORMAT() 関数の説明で示す表を参照してください)。ISO 書式は ISO 8601 ではなく、ISO 9075 を参照しています。

関数呼び出し	結果
`GET_FORMAT(DATE,'USA')`	`'%m.%d.%Y'`
`GET_FORMAT(DATE,'JIS')`	`'%Y-%m-%d'`
`GET_FORMAT(DATE,'ISO')`	`'%Y-%m-%d'`
`GET_FORMAT(DATE,'EUR')`	`'%d.%m.%Y'`
`GET_FORMAT(DATE,'INTERNAL')`	`'%Y%m%d'`
`GET_FORMAT(DATETIME,'USA')`	`'%Y-%m-%d %H.%i.%s'`
`GET_FORMAT(DATETIME,'JIS')`	`'%Y-%m-%d %H:%i:%s'`
`GET_FORMAT(DATETIME,'ISO')`	`'%Y-%m-%d %H:%i:%s'`
`GET_FORMAT(DATETIME,'EUR')`	`'%Y-%m-%d %H.%i.%s'`
`GET_FORMAT(DATETIME,'INTERNAL')`	`'%Y%m%d%H%i%s'`
`GET_FORMAT(TIME,'USA')`	`'%h:%i:%s %p'`
`GET_FORMAT(TIME,'JIS')`	`'%H:%i:%s'`
`GET_FORMAT(TIME,'ISO')`	`'%H:%i:%s'`
`GET_FORMAT(TIME,'EUR')`	`'%H.%i.%s'`
`GET_FORMAT(TIME,'INTERNAL')`	`'%H%i%s'`

TIMESTAMP は、GET_FORMAT() への 1 番目の引数としても使用できます。その場合、関数は DATETIME の場合と同じ値を返します。

mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));
        -> '03.10.2003'
mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));
        -> '2003-10-31'

HOUR(time)

time に対応する時を返します。戻り値の範囲は、日付時間値の 0 から 23 までです。ただし、TIME 値の範囲は実際にはもっと大きいため、HOUR は 23 よりも大きい値を返すことができます。
```
mysql> SELECT HOUR('10:05:03');
        -> 10
mysql> SELECT HOUR('272:59:59');
        -> 272
```

LAST_DAY(date)

日付または日付時間の値が指定され、月の最終日に対応する値を返します。引数が無効である場合は、NULL を返します。

mysql> SELECT LAST_DAY('2003-02-05');
        -> '2003-02-28'
mysql> SELECT LAST_DAY('2004-02-05');
        -> '2004-02-29'
mysql> SELECT LAST_DAY('2004-01-01 01:01:01');
        -> '2004-01-31'
mysql> SELECT LAST_DAY('2003-03-32');
        -> NULL

LOCALTIME, LOCALTIME([fsp])

LOCALTIME および LOCALTIME() は NOW() のシノニムです。
LOCALTIMESTAMP, LOCALTIMESTAMP([fsp])

LOCALTIMESTAMP および LOCALTIMESTAMP() は NOW() のシノニムです。

MAKEDATE(year,dayofyear)

指定された年と年間通算値から、日付を返します。dayofyear は 0 よりも大きくする必要があり、それ以外の場合は結果が NULL になります。

mysql> SELECT MAKEDATE(2011,31), MAKEDATE(2011,32);
        -> '2011-01-31', '2011-02-01'
mysql> SELECT MAKEDATE(2011,365), MAKEDATE(2014,365);
        -> '2011-12-31', '2014-12-31'
mysql> SELECT MAKEDATE(2011,0);
        -> NULL

MAKETIME(hour,minute,second)

hour、minute、および second 引数から計算された時間値を返します。

MySQL 5.6.4 の時点では、second 引数に小数部を含めることができます。
```
mysql> SELECT MAKETIME(12,15,30);
        -> '12:15:30'
```
MICROSECOND(expr)

0 から 999999 までの範囲内の数値として、時間または日付時間式 expr からのマイクロ秒を返します。
```
mysql> SELECT MICROSECOND('12:00:00.123456');
        -> 123456
mysql> SELECT MICROSECOND('2009-12-31 23:59:59.000010');
        -> 10
```
MINUTE(time)

0 から 59 までの範囲内で、time に対応する分を返します。
```
mysql> SELECT MINUTE('2008-02-03 10:05:03');
        -> 5
```
MONTH(date)

1 (1 月) から 12 (12 月) の範囲内で、date に対応する月を返します。'0000-00-00' や '2008-00-00' のように月の部分がゼロの場合は、0 を返します。
```
mysql> SELECT MONTH('2008-02-03');
        -> 2
```
MONTHNAME(date)

date に対応する月の完全名を返します。名前に使用される言語は、lc_time_names システム変数 (セクション10.7「MySQL Server のロケールサポート」) の値で制御されます。
```
mysql> SELECT MONTHNAME('2008-02-03');
        -> 'February'
```
NOW([fsp])

関数が文字列と数値のどちらのコンテキストで使用されているのかに応じて、現在の日付と時間を 'YYYY-MM-DD HH:MM:SS' または YYYYMMDDHHMMSS 書式の値として返します。値は、現在のタイムゾーンで表現されています。

MySQL 5.6.4 の時点では、0 から 6 までの小数秒の精度を指定するために fsp 引数が指定されている場合は、その桁数の小数秒部分が戻り値に含まれます。5.6.4 よりも前では、すべての引数が無視されます。
```
mysql> SELECT NOW();
        -> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;
        -> 20071215235026.000000
```
NOW() は、ステートメントが実行を開始する時刻を示す定数時間を返します。(ストアドファンクションまたはトリガーでは、NOW() は関数またはトリガーステートメントが実行を開始する時間を返します。)これは、正確な実行時間を返す SYSDATE() の動作とは異なります。
```
mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+
```
さらに、SET TIMESTAMP ステートメントによって、NOW() で返された値は影響を受けますが、SYSDATE() で返された値は影響を受けません。つまり、バイナリログのタイムスタンプ設定は、SYSDATE() の呼び出しに影響しないことを意味します。タイムスタンプをゼロ以外の値に設定すると、後続の NOW() が起動されるたびに、その値が返されます。タイムスタンプをゼロに設定すると、この効果が取り消され、再度 NOW() が現在の日付と時間を返すようになります。

2 つの関数の相違点についての詳細は、SYSDATE() の説明を参照してください。
PERIOD_ADD(P,N)

N 月を期間 P に (YYMM または YYYYMM の書式で) 加算します。YYYYMM の書式で値を返します。

注記

期間引数 P は、日付値ではありません。
```
mysql> SELECT PERIOD_ADD(200801,2);
        -> 200803
```
PERIOD_DIFF(P1,P2)

期間 P1 と P2 間の月数を返します。P1 および P2 は、YYMM または YYYYMM の書式にする必要があります。期間引数 P1 および P2 は日付値ではないことに注意してください。
```
mysql> SELECT PERIOD_DIFF(200802,200703);
        -> 11
```
QUARTER(date)

1 から 4 までの範囲内で date に対応する四半期を返します。
```
mysql> SELECT QUARTER('2008-04-01');
        -> 2
```
SECOND(time)

0 から 59 までの範囲内で、time に対応する秒数を返します。
```
mysql> SELECT SECOND('10:05:03');
        -> 3
```
SEC_TO_TIME(seconds)

TIME 値として、時、分、秒に変換された seconds 引数を返します。結果の範囲は、TIME データ型の範囲に制約されます。引数がその範囲外の値に対応している場合は、警告が発行されます。
```
mysql> SELECT SEC_TO_TIME(2378);
        -> '00:39:38'
mysql> SELECT SEC_TO_TIME(2378) + 0;
        -> 3938
```
STR_TO_DATE(str,format)

これは DATE_FORMAT() 関数の逆です。文字列 str と書式文字列 format が指定されます。STR_TO_DATE() は、書式文字列に日付と時間の両方の部分が含まれる場合は DATETIME 値を返し、文字列に日付と時間の部分の一方のみが含まれる場合は DATE または TIME 値を返します。str から抽出された日付値、時間値、または日付時間値が不正な場合は、STR_TO_DATE() によって NULL が返され、警告が発行されます。

サーバーは str をスキャンすることで、format の一致を試みます。書式文字列には、リテラル文字と % で始まる書式指定子を含めることができます。format 内のリテラル文字は、str 内と完全に一致する必要があります。format 内の書式指定子は、str 内の日付または時間の部分に一致する必要があります。format で使用できる指定子については、DATE_FORMAT() 関数の説明を参照してください。
```
mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
        -> '2013-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
        -> '2013-05-01'
```
str の先頭からスキャンが開始され、一致しない format が見つかった場合は失敗します。str の末尾にある余分な文字は、無視されます。
```
mysql> SELECT STR_TO_DATE('a09:30:17','a%h:%i:%s');
        -> '09:30:17'
mysql> SELECT STR_TO_DATE('a09:30:17','%h:%i:%s');
        -> NULL
mysql> SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');
        -> '09:30:17'
```
指定されていない日付または時間の部分の値は 0 になるため、str に指定された値が不完全な場合は、結果の一部または全部が 0 に設定されます。
```
mysql> SELECT STR_TO_DATE('abc','abc');
        -> '0000-00-00'
mysql> SELECT STR_TO_DATE('9','%m');
        -> '0000-09-00'
mysql> SELECT STR_TO_DATE('9','%s');
        -> '00:00:09'
```
日付値の部分をチェックする範囲は、セクション11.3.1「DATE、DATETIME、および TIMESTAMP 型」の説明どおりです。たとえば、「ゼロ」の日付または部分値が 0 の日付は、このような値が許可されないように SQL モードが設定されていなければ、許可されます。
```
mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
        -> '0000-00-00'
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
        -> '2004-04-31'
```
注記

週が月の境界を越えた場合は、年と週の組み合わせでは年と月を一意に識別できないため、"%X%V" の書式を使用しても、年と週の文字列を日付に変換できません。年と週を日付に変換するには、曜日も指定する必要があります。
```
mysql> SELECT STR_TO_DATE('200442 Monday', '%X%V %W');
        -> '2004-10-18'
```
SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)

INTERVAL 形式で 2 番目の引数を付けて呼び出されると、SUBDATE() は DATE_SUB() のシノニムになります。INTERVAL unit 引数については、DATE_ADD() の説明を参照してください。
```
mysql> SELECT DATE_SUB('2008-01-02', INTERVAL 31 DAY);
        -> '2007-12-02'
mysql> SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
        -> '2007-12-02'
```
2 番目の形式では、days の整数値を使用できます。このような場合は、日付または日付時間式 expr から日数が減算されると解釈されます。
```
mysql> SELECT SUBDATE('2008-01-02 12:00:00', 31);
        -> '2007-12-02 12:00:00'
```

SUBTIME(expr1,expr2)

SUBTIME() は、expr1 と同じ書式で表現される expr1 と expr2 を返します。expr1 は時間または日付時間式であり、expr2 は時間式です。

mysql> SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');
        -> '2007-12-30 22:58:58.999997'
mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');
        -> '-00:59:59.999999'

SYSDATE([fsp])

関数が文字列と数値のどちらのコンテキストで使用されているのかに応じて、現在の日付と時間を 'YYYY-MM-DD HH:MM:SS' または YYYYMMDDHHMMSS 書式の値として返します。

MySQL 5.6.4 の時点では、0 から 6 までの小数秒の精度を指定するために fsp 引数が指定されている場合は、その桁数の小数秒部分が戻り値に含まれます。5.6.4 よりも前では、すべての引数が無視されます。

SYSDATE() は、実行された時間を返します。これは、ステートメントが実行を開始する時間を示す定数時間を返す NOW() の動作とは異なります。(ストアドファンクションまたはトリガーでは、NOW() は関数またはトリガーステートメントが実行を開始する時間を返します。)
```
mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+
```
さらに、SET TIMESTAMP ステートメントによって、NOW() で返された値は影響を受けますが、SYSDATE() で返された値は影響を受けません。つまり、バイナリログのタイムスタンプ設定は、SYSDATE() の呼び出しに影響しないことを意味します。

SYSDATE() は、同じステートメント内でもさまざまな値を返すことができ、SET TIMESTAMP による影響も受けないため、非決定的です。そのため、ステートメントベースのバイナリロギングが使用されている場合は、レプリケーションで安全に使用できません。これが問題となる場合は、行ベースのロギングを使用できます。

また、--sysdate-is-now オプションを使用すると、SYSDATE() を NOW() のエイリアスにすることができます。これは、オプションがマスターとスレーブの両方で使用される場合に機能します。

SYSDATE() に非決定的な特性があるということは、それを参照する式を評価する際にインデックスを使用できないことも意味します。
TIME(expr)

時間または日付時間式 expr の時部分を抽出し、文字列として返します。

この関数は、ステートメントベースのレプリケーションでは安全に使用できません。binlog_format が STATEMENT に設定されているときに、この関数を使用すると、警告のログが記録されます。
```
mysql> SELECT TIME('2003-12-31 01:02:03');
        -> '01:02:03'
mysql> SELECT TIME('2003-12-31 01:02:03.000123');
        -> '01:02:03.000123'
```
TIMEDIFF(expr1,expr2)

TIMEDIFF() は、時間値として表現された expr1 − expr2 を返します。expr1 および expr2 は時間または日付時間式ですが、両方とも同じ型にする必要があります。

TIMEDIFF() で返される結果は、TIME 値で許可される範囲に制限されています。また、TIMESTAMPDIFF() および UNIX_TIMESTAMP() 関数のいずれかを使用することもできます。両方とも整数を返します。
```
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',
    ->                 '2000:01:01 00:00:00.000001');
        -> '-00:00:00.000001'
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001',
    ->                 '2008-12-30 01:01:01.000002');
        -> '46:58:57.999999'
```
TIMESTAMP(expr), TIMESTAMP(expr1,expr2)

引数を 1 つ付けると、この関数は日付または日付時間式 expr を日付時間値として返します。引数を 2 つ付けると、時間式 expr2 を日付または日付時間式 expr1 に加算し、その結果を日付時間値として返します。
```
mysql> SELECT TIMESTAMP('2003-12-31');
        -> '2003-12-31 00:00:00'
mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
        -> '2004-01-01 00:00:00'
```
TIMESTAMPADD(unit,interval,datetime_expr)

整数式 interval を日付または日付時間式 datetime_expr に加算します。interval の単位は、unit 引数で指定されます。この引数は、MICROSECOND (マイクロ秒)、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR 値のいずれかにする必要があります。

unit 値を指定するには、ここで示したキーワードのいずれかを使用するか、SQL_TSI_ をプリフィクスとして付けます。たとえば、DAY と SQL_TSI_DAY は両方とも有効です。
```
mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
        -> '2003-01-02 00:01:00'
mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
        -> '2003-01-09'
```
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

datetime_expr2 − datetime_expr1 を返します。datetime_expr1 と datetime_expr2 は、日付または日付時間式です。式の一方が日付で、他方が日付時間にすることもできます。日付値は、必要に応じて時間部分が '00:00:00' の日付時間として処理されます。結果 (整数) の単位は、unit 引数で指定されます。unit の有効な値は、TIMESTAMPADD() 関数の説明で一覧表示された値と同じです。
```
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
        -> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
        -> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
        -> 128885
```
注記

この関数の日付または日付時間引数の順序は、TIMESTAMP() 関数を 2 つの引数を指定して呼び出す場合の順序と逆になります。
TIME_FORMAT(time,format)

これは DATE_FORMAT() 関数と同様に使用されますが、format 文字列には時間、分、秒、マイクロ秒の書式指定子のみを含めることができます。その他の指定子では、NULL 値または 0 が生成されます。

time 値に 23 よりも大きな時間部分が含まれる場合は、%H および %k 時間書式指定子によって、0..23 の通常の範囲よりも大きな値が生成されます。その他の時間書式指定子では、時間値モジュロ 12 が生成されます。
```
mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
        -> '100 100 04 04 4'
```

TIME_TO_SEC(time)

秒に変換された time 引数を返します。

mysql> SELECT TIME_TO_SEC('22:23:00');
        -> 80580
mysql> SELECT TIME_TO_SEC('00:39:38');
        -> 2378

TO_DAYS(date)

日付 date が指定され、日数 (0 年以降の日数) を返します。
```
mysql> SELECT TO_DAYS(950501);
        -> 728779
mysql> SELECT TO_DAYS('2007-10-07');
        -> 733321
```
TO_DAYS() は、カレンダーが変更された際に失われた日が考慮されないため、グレゴリオ暦 (1582) の出現よりも前の値とともに使用するために設計されていません。日付が 1582 よりも前の場合は (ほかのロケールでは、さらにあとの年になる可能性があります)、この関数の結果は信頼できません。詳細はセクション12.8「MySQL で使用されるカレンダー」を参照してください。

MySQL ではセクション11.3「日付と時間型」のルールを使用して、日付の 2 桁の年の値が 4 桁の形式に変換されることを忘れないでください。たとえば、'2008-10-07' と '08-10-07' は同じ日付と認識されます。
```
mysql> SELECT TO_DAYS('2008-10-07'), TO_DAYS('08-10-07');
        -> 733687, 733687
```
MySQL では、ゼロの日付は '0000-00-00' として定義されます。ただし、このデータ自体は無効とみなされます。つまり、'0000-00-00' および '0000-01-01' の場合、TO_DAYS() は次に示す値を返します。
```
mysql> SELECT TO_DAYS('0000-00-00');
+-----------------------+
| to_days('0000-00-00') |
+-----------------------+
|                  NULL |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '0000-00-00' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)


mysql> SELECT TO_DAYS('0000-01-01');
+-----------------------+
| to_days('0000-01-01') |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)
```
このことは、ALLOW_INVALID_DATES SQL サーバーモードが有効であるかどうかに関係なく当てはまります。
TO_SECONDS(expr)

日付または日付時間の expr が指定され、0 年以降の日数を返します。expr が有効な日付または日付時間の値ではない場合は、NULL を返します。
```
mysql> SELECT TO_SECONDS(950501);
        -> 62966505600
mysql> SELECT TO_SECONDS('2009-11-29');
        -> 63426672000
mysql> SELECT TO_SECONDS('2009-11-29 13:43:32');
        -> 63426721412
mysql> SELECT TO_SECONDS( NOW() );
        -> 63426721458
```
TO_DAYS() と同様に、TO_SECONDS() は、カレンダーが変更された際に失われた日が考慮されないため、グレゴリオ暦 (1582) の出現よりも前の値とともに使用する目的で設計されていません。日付が 1582 よりも前の場合は (ほかのロケールでは、さらにあとの年になる可能性があります)、この関数の結果は信頼できません。詳細はセクション12.8「MySQL で使用されるカレンダー」を参照してください。

TO_DAYS() と同様に、TO_SECONDS() はセクション11.3「日付と時間型」のルールを使用して日付の 2 桁の年の値を 4 桁の形式に変換します。

MySQL では、ゼロの日付は '0000-00-00' として定義されます。ただし、このデータ自体は無効とみなされます。つまり、'0000-00-00' および '0000-01-01' の場合、TO_SECONDS() は次に示す値を返します。
```
mysql> SELECT TO_SECONDS('0000-00-00');
+--------------------------+
| TO_SECONDS('0000-00-00') |
+--------------------------+
|                     NULL |
+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '0000-00-00' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)


mysql> SELECT TO_SECONDS('0000-01-01');
+--------------------------+
| TO_SECONDS('0000-01-01') |
+--------------------------+
|                    86400 |
+--------------------------+
1 row in set (0.00 sec)
```
このことは、ALLOW_INVALID_DATES SQL サーバーモードが有効であるかどうかに関係なく当てはまります。
UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)

引数なしで呼び出された場合は、Unix タイムスタンプ ('1970-01-01 00:00:00' UTC 以降の秒数) を符号なし整数として返します。date 引数を付けて UNIX_TIMESTAMP() が呼び出された場合は、その引数の値が '1970-01-01 00:00:00' UTC 以降の秒数として返されます。date には、DATE 文字列、DATETIME 文字列、TIMESTAMP、YYMMDD または YYYYMMDD 書式の数値を指定できます。サーバーは date を現在のタイムゾーンの値として解釈し、UTC の内部値に変換します。クライアントは、セクション10.6「MySQL Server でのタイムゾーンのサポート」で説明するとおりに、独自のタイムゾーンを設定できます。
```
mysql> SELECT UNIX_TIMESTAMP();
        -> 1196440210
mysql> SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');
        -> 1196440219
```
UNIX_TIMESTAMP() が TIMESTAMP カラムで使用されると、暗黙的に「string-to-Unix-timestamp」に変換されずに、内部タイムスタンプ値が直接返されます。UNIX_TIMESTAMP() に範囲外の日付を渡すと、0 が返されます。

注: UNIX_TIMESTAMP() および FROM_UNIXTIME() を使用して TIMESTAMP 値と Unix タイムスタンプ値とを変換する場合は、どちらの方向でもマッピングが 1 対 1 ではないため、変換は不可逆です。たとえば、ローカルタイムゾーンを変更するための変換が原因で、2 つの UNIX_TIMESTAMP() で 2 つの TIMESTAMP 値が同じ Unix タイムスタンプ値にマップされる可能性があります。FROM_UNIXTIME() は、その値を元の TIMESTAMP 値のいずれかにのみマップし直します。次に、CET タイムゾーンでの TIMESTAMP 値の使用例を示します。
```
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
mysql> SELECT FROM_UNIXTIME(1111885200);
+---------------------------+
| FROM_UNIXTIME(1111885200) |
+---------------------------+
| 2005-03-27 03:00:00       |
+---------------------------+
```
UNIX_TIMESTAMP() カラムを減算する場合は、結果を符号付きの整数にキャストします。セクション12.10「キャスト関数と演算子」を参照してください。
UTC_DATE、UTC_DATE()

関数が文字列と数値のどちらのコンテキストで使用されているのかに応じて、現在の UTC 日付を 'YYYY-MM-DD' または YYYYMMDD 書式の値で返します。
```
mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
        -> '2003-08-14', 20030814
```
UTC_TIME, UTC_TIME([fsp])

関数が文字列と数値のどちらのコンテキストで使用されているのかに応じて、現在の UTC 時間を 'HH:MM:SS' または HHMMSS 書式の値で返します。

MySQL 5.6.4 の時点では、0 から 6 までの小数秒の精度を指定するために fsp 引数が指定されている場合は、その桁数の小数秒部分が戻り値に含まれます。5.6.4 よりも前では、すべての引数が無視されます。
```
mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
        -> '18:07:53', 180753.000000
```
UTC_TIMESTAMP、UTC_TIMESTAMP([fsp])

関数が文字列と数値のどちらのコンテキストで使用されているのかに応じて、現在の UTC 日付と時間を 'YYYY-MM-DD HH:MM:SS' または YYYYMMDDHHMMSS 書式の値として返します。

MySQL 5.6.4 の時点では、0 から 6 までの小数秒の精度を指定するために fsp 引数が指定されている場合は、その桁数の小数秒部分が戻り値に含まれます。5.6.4 よりも前では、すべての引数が無視されます。
```
mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
        -> '2003-08-14 18:08:04', 20030814180804.000000
```

WEEK(date[,mode])

この関数は、date に対応する週番号を返します。2 つの引数を取る形式の WEEK() を使用すると、週が日曜日と月曜日のどちらから始まるのか、および戻り値が 0 から 53までと 1 から 53 までのどちらの範囲内であるのかを指定できます。mode 引数が省略された場合は、default_week_format システム変数の値が使用されます。セクション5.1.4「サーバーシステム変数」を参照してください。

次の表では、mode 引数がどのように機能するのかについて説明します。

モード	週の 1 日目	範囲	第 1 週は次の条件を満たす最初の週
0	日曜日	0-53	本年の日曜日を含む
1	月曜日	0-53	本年の 4 日以上を含む
2	日曜日	1-53	本年の日曜日を含む
3	月曜日	1-53	本年の 4 日以上を含む
4	日曜日	0-53	本年の 4 日以上を含む
5	月曜日	0-53	本年の月曜日を含む
6	日曜日	1-53	本年の 4 日以上を含む
7	月曜日	1-53	本年の月曜日を含む

「本年の 4 日以上を含む」という意味を持つ mode 値では、ISO 8601:1988 に従って週番が付けられます。

1 月 1 日を含む週に新年の 4 日以上が含まれる場合は、その週が第 1 週です。
それ以外の場合は、前年の最終週となり、次の週が第 1 週です。

mysql> SELECT WEEK('2008-02-20');
        -> 7
mysql> SELECT WEEK('2008-02-20',0);
        -> 7
mysql> SELECT WEEK('2008-02-20',1);
        -> 8
mysql> SELECT WEEK('2008-12-31',1);
        -> 53

日付が前年の最終週に入っている場合は、オプションの mode 引数として2、3、6、または 7 を使用しなければ、MySQL によって 0 が返されます。

mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
        -> 2000, 0

指定された日付は実際には 1999 年の第 52 週に発生するため、WEEK() は 52 を返す必要があると議論されることもあります。代わりに WEEK() は、戻り値が「指定された年の週番号」となるように 0 を返します。これにより、日付から日付部分を抽出するその他の関数と組み合わせると、WEEK() 関数を信頼して使用できるようになります。

指定された日付に対応する週の 1 日目を含む年について評価された結果を優先する場合は、オプションの mode 引数として 0、2、5、または 7 を使用します。

mysql> SELECT WEEK('2000-01-01',2);
        -> 52

または、YEARWEEK() 関数を使用します。

mysql> SELECT YEARWEEK('2000-01-01');
        -> 199952
mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
        -> '52'

WEEKDAY(date)

date に対応する曜日インデックス (0 = Monday、1 = Tuesday、…6 = Sunday) を返します。
```
mysql> SELECT WEEKDAY('2008-02-03 22:23:00');
        -> 6
mysql> SELECT WEEKDAY('2007-11-06');
        -> 1
```
WEEKOFYEAR(date)

1 から 53 までの範囲内で、日付の暦週を返します。WEEKOFYEAR() は WEEK(date,3) に同等の互換性のある関数です。
```
mysql> SELECT WEEKOFYEAR('2008-02-20');
        -> 8
```
YEAR(date)

1000 から 9999 までの範囲内で、date に対応する年を返します。日付が「ゼロ」の場合は、0 を返します。
```
mysql> SELECT YEAR('1987-01-01');
        -> 1987
```
YEARWEEK(date)、YEARWEEK(date,mode)

日付に対応する年と週を返します。mode 引数は、WEEK() への mode 引数とまったく同様に機能します。結果の年と日付引数の年では、その年の最初と最後の週が異なる可能性があります。
```
mysql> SELECT YEARWEEK('1987-01-01');
        -> 198653
```
WEEK() はその後、指定された年のコンテキストで週を返すため、週番号はオプションの引数 0 または 1 を付けた場合に WEEK() 関数で返される数字 (0) とは異なります。

PREV HOME UP NEXT

User Comments

User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.

Posted by nicolas lumbreras on October 5, 2010

CREATE FUNCTION `workdayadd`(a date, days int) RETURNS date
DETERMINISTIC
COMMENT 'add workdays to date a'
BEGIN
DECLARE freedays int;
DECLARE daysadd, totaldays, workday, di INT;
declare dateinit date;

set dateinit=a;
SET workday=0;
SET di=DAYOFWEEK(dateinit);
set totaldays = 0;

WHILE workday<=days DO
if (di=8) then
set di=1;
end if;
IF (di>1 and di<7) THEN
SET workday=(workday+1);
END IF;
SET di=(di+1);
SET totaldays=(totaldays+1);
END WHILE;
if di<=2 then
set totaldays=totaldays-di;
end if;

set daysadd=totaldays;

while daysadd>0 do
SELECT count(*) INTO freedays
FROM holliday
WHERE d_day BETWEEN dateinit AND date_add(dateinit,interval daysadd-1 day)
AND WEEKDAY(d_day)<5;

if freedays>0 then
SET workday=0;
set dateinit=date_add(dateinit,interval daysadd day);
SET di=DAYOFWEEK(dateinit);
WHILE workday<freedays DO
if (di=8) then
set di=1;
end if;
IF (di>1 and di<7) THEN
SET workday=(workday+1);
END IF;
SET di=(di+1);
SET totaldays=(totaldays+1);
END WHILE;
if di<=2 then
set totaldays=totaldays-di;
end if;
end if;
set daysadd = freedays;
end while;
return date_add(a, interval (totaldays-1) day);
END;

Posted by Neil Hopkins on December 2, 2010

For those looking for a WEEKOFMONTH function:

mysql> SELECT 1 + WEEK(STR_TO_DATE('12/2/2010 11:00:00 AM','%m/%d/%Y %h:%i:%s %p')) - WEEK(DATE_ADD(LAST_DAY(DATE_SUB(STR_TO_DATE('12/2/2010 11:00:00 AM','%m/%d/%Y %h:%i:%s %p'), INTERVAL '1' MONTH)), INTERVAL '1' DAY))
FROM DUAL;

-> 1

Posted by John Burr on December 17, 2010

To calculate the difference between any two dates or times when you want to specify the units (i.e. years, months, weeks, hours, minutes, etc.) just use the TIMESTAMPDIFF function. That's what was designed and optimized to do this.

To calculate the age of all the participants in my table, my SELECT query looks like:

SELECT name_first, name_last, TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) FROM participants;

Posted by Patrick Renaud on April 5, 2011

Please note the TIMEDIFF function is limited to the following range : [-839h 59m 59s ; +839h 59m 59s].
The same stands for SEC_TO_TIME.

Posted by bob lawn on April 21, 2011

Further to Santi Bari's excellent suggestion to "plug the missing gaps", remember that if you have additional clauses in your retrieval, you may have to include a little extra in your syntax.

For example, following on from Santi's example, suppose you had a field in the visits table for capturing the browser used.
To retrieve the data where the browser was Firefox, you would add a WHERE clause, like :
WHERE visits.browser = 'Firefox'

However, this will not work as expected. To plug the holes in the dates, the right syntax would be :
WHERE (visits.browser = 'Firefox' OR visits.browser IS NULL)

and the gaps in the dates will be filled again!

Posted by Serg Kalachev on April 22, 2011

Using function from Martin Minka posted here on January 25 2007 5:23pm in this example we calculate effective working minutes elapsed between creation and completion of ServiceDesk request. I hope it may be useful...

SELECT `created_at` , `finished_at` ,
CASE mysql.workdaydiff( `finished_at` , `created_at`)
WHEN 1 THEN TIMESTAMPDIFF( MINUTE , `created_at` , `finished_at` )
ELSE
( mysql.workdaydiff(`finished_at` , `created_at`) -2
) *60 *9 +
TIMESTAMPDIFF( MINUTE , `created_at` , DATE_ADD( DATE( `created_at` ) , INTERVAL 19 HOUR ) ) +
TIMESTAMPDIFF( MINUTE , DATE_ADD( DATE( `finished_at` ) , INTERVAL 10 HOUR ) , `finished_at` )
END AS working_time_used_to_finish_request
FROM `request`
WHERE `request_status_id` IN ( 4, 5 )

Posted by Mustali Kachwala on April 29, 2011

An example on date_sub to get report for last 7 days

http://mustalikachwala.blogspot.com/2011/04/mysql-query-to-get-data-for-last-7-days.html

Posted by shivam sharma on October 31, 2011

Hello All,

Here are two methods to find first day of month:

SELECT DATE_SUB(LAST_DAY(NOW()),INTERVAL DAY(LAST_DAY(NOW()))-1 DAY)

SELECT DATE_SUB(NOW(),INTERVAL DAY(NOW())-1 DAY)

Hope these will be helpful to you ... :)

Posted by SANATAN OJHA on February 2, 2012

This is simple example wrriten in sql to get date difference in year month day

CREATE TABLE temp_rent_ftth_a (
rent_upto date DEFAULT NULL,
bill_to date DEFAULT NULL,
billy int(4) DEFAULT NULL,
billm int(2) DEFAULT NULL,
billd int(2) DEFAULT NULL,
renty int(4) DEFAULT NULL,
rentm int(2) DEFAULT NULL,
rentd int(2) DEFAULT NULL);
insert into temp_rent_ftth_a (rent_upto,bill_to) values ('2012-02-02','1967-06-10');
update temp_rent_ftth_a set renty=year(rent_upto),rentm=month(rent_upto),rentd=day(rent_upto);
update temp_rent_ftth_a set billy=year(bill_to),billm=month(bill_to),billd=day(bill_to);
update temp_rent_ftth_a set rentd=rentd+30 where rentd<billd;
update temp_rent_ftth_a set rentm=rentm-1 where rentd-30<billd;
update temp_rent_ftth_a set rentm=rentm+12 where rentm+1<billm;
update temp_rent_ftth_a set renty=renty-1 where rentm-12<billm;
select rentd-billd,rentm-billm,renty-billy from temp_rent_ftth_a;

Posted by Denis Kukharev on May 17, 2012

If you need to access LAST_INSERT_ID() outside a trigger where it has been modified, you can use workaround with user-defined variable that is to be assigned with LAST_INSERT_ID() inside the trigger.
This implies an overhead of additional query to read the variable, but it seems to be the only way to solve the problem.

Posted by Luis Lobo Borobia on June 8, 2012

When you need to get the timestamp of a date in a certain timezone or GMT time, use this:

select
UNIX_TIMESTAMP(
CONVERT_TZ( '2012-05-31 23:59:59',
'-03:00',
'+00:00') ) + TIMESTAMPDIFF(second,utc_timestamp(), now()) as time;

This will return the UTC timestamp for the datetime in Argentina at 23:59:59, GMT-3.

A generic way of getting the UTC time of a certain LOCAL DATE TIME is:

select
UNIX_TIMESTAMP(
CONVERT_TZ( '2012-05-31 23:59:59',
substring(
replace(
concat('+',
SEC_TO_TIME( TIMESTAMPDIFF( second,utc_timestamp(), now() ) )
)
,'+-','-')
,1,6),
'+00:00') ) + TIMESTAMPDIFF(second,utc_timestamp(), now()) as time;

Posted by Dennis German on July 6, 2012

Don't use %l format (lower case L; 1..12) for hours as a result of SUBTIME since
select Time_Format( SUBTIME('20:16:44', '20:05:20') , '%l:%i');
results in 12:11 rather than the 00:11 you might have expected.

Posted by John Long on February 5, 2013

Just aggregating some of what I found on this page in a single query.

SELECT
-- DAY OF LAST WEEK - LAST YEAR
DATE_SUB((CURRENT_DATE - INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE - INTERVAL 1 YEAR)) +7 DAY) LAST_YEAR_LAST_WK_MONDAY,
DATE_SUB((CURRENT_DATE - INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE - INTERVAL 1 YEAR)) +6 DAY) LAST_YEAR_LAST_WK_TUESDAY,
DATE_SUB((CURRENT_DATE - INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE - INTERVAL 1 YEAR)) +5 DAY) LAST_YEAR_LAST_WK_WEDNESDAY,
DATE_SUB((CURRENT_DATE - INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE - INTERVAL 1 YEAR)) +4 DAY) LAST_YEAR_LAST_WK_THURSDAY,
DATE_SUB((CURRENT_DATE - INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE - INTERVAL 1 YEAR)) +3 DAY) LAST_YEAR_LAST_WK_FRIDAY,
DATE_SUB((CURRENT_DATE - INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE - INTERVAL 1 YEAR)) +2 DAY) LAST_YEAR_LAST_WK_SATURDAY,
DATE_SUB((CURRENT_DATE - INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE - INTERVAL 1 YEAR)) +1 DAY) LAST_YEAR_LAST_WK_SUNDAY,
--
-- DAY OF THIS WEEK - LAST YEAR
DATE_SUB((CURRENT_DATE - INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE - INTERVAL 1 YEAR)) -0 DAY) LAST_YEAR_THIS_WK_MONDAY,
DATE_SUB((CURRENT_DATE - INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE - INTERVAL 1 YEAR)) -1 DAY) LAST_YEAR_THIS_WK_TUESDAY,
DATE_SUB((CURRENT_DATE - INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE - INTERVAL 1 YEAR)) -2 DAY) LAST_YEAR_THIS_WK_WEDNESDAY,
DATE_SUB((CURRENT_DATE - INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE - INTERVAL 1 YEAR)) -3 DAY) LAST_YEAR_THIS_WK_THURSDAY,
DATE_SUB((CURRENT_DATE - INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE - INTERVAL 1 YEAR)) -4 DAY) LAST_YEAR_THIS_WK_FRIDAY,
DATE_SUB((CURRENT_DATE - INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE - INTERVAL 1 YEAR)) -5 DAY) LAST_YEAR_THIS_WK_SATURDAY,
DATE_SUB((CURRENT_DATE - INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE - INTERVAL 1 YEAR)) -6 DAY) LAST_YEAR_THIS_WK_SUNDAY,
--
-- DAY OF LAST WEEK - THIS YEAR
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +7 DAY) LAST_WK_MONDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +6 DAY) LAST_WK_TUESDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +5 DAY) LAST_WK_WEDNESDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +4 DAY) LAST_WK_THURSDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +3 DAY) LAST_WK_FRIDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +2 DAY) LAST_WK_SATURDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +1 DAY) LAST_WK_SUNDAY,
--
-- DAY OF CURRENT WEEK - THIS YEAR
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -0 DAY) CUR_WK_MONDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -1 DAY) CUR_WK_TUESDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -2 DAY) CUR_WK_WEDNESDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -3 DAY) CUR_WK_THURSDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -4 DAY) CUR_WK_FRIDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -5 DAY) CUR_WK_SATURDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -6 DAY) CUR_WK_SUNDAY,
--
-- DAY OF NEXT WEEK - THIS YEAR
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -7 DAY) NEXT_WK_MONDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -8 DAY) NEXT_WK_TUESDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -9 DAY) NEXT_WK_WEDNESDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -10 DAY) NEXT_WK_THURSDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -11 DAY) NEXT_WK_FRIDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -12 DAY) NEXT_WK_SATURDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -13 DAY) NEXT_WK_SUNDAY,
--
-- FIRST AND LAST OF MONTH --
DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-01') LAST_MON_START,
LAST_DAY(CURRENT_DATE - INTERVAL 1 MONTH) LAST_MONTH_END,
DATE_FORMAT( CURRENT_DATE, '%Y-%m-01' ) THIS_MON_START,
LAST_DAY(SYSDATE()) THIS_MONTH_END,
DATE_FORMAT( CURRENT_DATE + INTERVAL 1 MONTH, '%Y-%m-01') NEXT_MON_START,
LAST_DAY(CURRENT_DATE + INTERVAL 1 MONTH) NEXT_MONTH_END,
--
-- LAST YEAR, THIS YEAR, NEXT YEAR
YEAR(CURRENT_DATE - INTERVAL 1 YEAR) LAST_YEAR,
YEAR(CURRENT_DATE) CURRENT_YEAR,
YEAR(CURRENT_DATE + INTERVAL 1 YEAR) NEXT_YEAR,
--
DATE_FORMAT( CURRENT_DATE, '%Y-01-01') FIRST_DAY_OF_CUR_YEAR,
DATE_FORMAT( CURRENT_DATE, '%Y-12-31') LAST_DAY_OF_CUR_YEAR
;

Posted by Lyle Brewer on February 10, 2013

Calculating age in years from date of birth accurately using datediff() doesn't work due to leap years. If you calcualte a person's age on a date near their birthday, you're likely to get an incorrect result. The older the subject is, the more likely you will get an incorrect result due to the number of leap years that has occurred in a person's life. For example, a person who is 100 has lived through about 25 leap years.

Calculating age is a simple problem. It boils down to subtracting year of birth from the current year (or whatever year you're calculating age at) and then subtractiing 1 if the subject's birth date is after the the date of the calculation in the calendar year of the date of calculation. For example, if the subject was born on June 15th, 1954 and you're calculating their age on July 1st, 1984, then their birthday has already occurred in the calendar year of the date of calculation and the correct answer is 1984 - 1954 = 30. That calculation works if the subject's birth date is on or before the date of calculation. However, if that person were born on August 1st, 1954, then their birthday occurs later in the calendar year than the date of calculation. In that case, the person is still only 29 on July 1st which means that the correct caclulation is 1984 - 1954 - 1 = 29.

To calculate age in years accurately, you need a calculation which simply subtracts the year of the date of birth from the year of the date of calculation and then adds "- 1" to the calculation if the person's birthday occurs after the date of calculation in the calender year of the date of calculation. I was able to get a 100% correct answer using a nested if-then-else function. However, the simplest approach I've come across is actually in this reference manual here:

http://dev.mysql.com/doc/refman/5.5/en/date-calculations.html

It does the same thing as my more complicated if-then-else approach but it's a lot simpler. Stay away from the datediff() approaches for calculating age. This approach is 100% accurate. It will even give you the correct age, in years, for someone born on February 29th.

Posted by Lyle Brewer on February 12, 2013

Paul Dubois of Oracle emailed me with the following comment:

If you're doing age calculations, wouldn't you be better off using
TIMESTAMPDIFF(YEAR,date1,date2) ?

I tried it and it works like a charm. I tested an age calculation against today's date, 2013-02-12, using the birthdates 1911-02-12 (birthday today) and 1911-02-13 (birhtday tomorrow). It returned the correct results of 102 for 1911-02-12 (birthday today) and 101 for 1911-02-13 (birthday tomorrow).

Posted by ALEXANDER SKAKUNOV on March 21, 2013

If you have a queue and you want to find out the average time in which an item gets processed, you can use this snippet (that's what we use for a dashboard in our http://yasno.tv/ project).

If you have a table of queued items with `created_at` and `updated_at` datetime fields that represent when an item was added and when it was updated (processed) accordingly. So,

[code]
SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(`updated_at`, `created_at`)))) AS average_item_time
FROM `mail_queue_item`
[/code]

shows something like "00:02:30" which means average idle time is 2.5 minutes per item.

Posted by Programmer Old on April 16, 2013

In our database there are partial dates. In spite of ALLOW_INVALID_DATES function DATEDIFF yields NULL on them. Therefore, I wrote this function:

CREATE FUNCTION ddiff(lait DATE, earlie DATE) RETURNS INTEGER(7)
COMMENT 'less pickie dait-differens'
DETERMINISTIC
RETURN ROUND(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM lait), EXTRACT(YEAR_MONTH FROM earlie)) * 30.44) + DAY(lait) - DAY(earlie)

It is at most 2 days off the real difference, and the greatly biassed value for DAY(d) = 0 is of little importance in our case. If it mattered, we would make it 15.

Posted by Rick James on November 27, 2013

When did MySQL restart?
SELECT NOW() - INTERVAL VARIABLE_VALUE SECOND
FROM information_schema. GLOBAL_STATUS
WHERE VARIABLE_NAME = 'UPTIME';
--> 2013-11-16 10:07:47

Or, simply the date:
SELECT DATE(NOW() - INTERVAL VARIABLE_VALUE SECOND) AS StartDate
FROM information_schema. GLOBAL_STATUS
WHERE VARIABLE_NAME = 'UPTIME';
--> 2013-11-16

Posted by Chris Wilson on August 15, 2014

The query cache can cause CONVERT_TZ to return invalid results (NULL) even after the timezone data has been loaded, because loading these tables does not properly invalidate the cache. http://bugs.mysql.com/73604.

Posted by Adolfo adolfo on February 11, 2015

Function like Date_add considering only business's day (Monday-Friday)

delimiter $
drop function if exists BusinessDaysDateAdd
$
create function BusinessDaysDateAdd
(
...FromDate datetime,
...DaysToAdd int
)
RETURNS datetime
BEGIN
...SET @Result = (FromDate + interval floor(DaysToAdd / 5) week) +
.............................interval (mod(DaysToAdd,5) +
......................................(CASE mod(DAYOFWEEK(FromDate) + mod(DaysToAdd,5),7)
..........................................WHEN 0 THEN 2
..........................................WHEN 1 THEN 2
..........................................ELSE 0 END)) day;
...RETURN @Result;
END;
$

delimiter ;

Posted by 王軍 on August 2, 2016

a format variable to output the ISO timezone offset "+/-HH:MM":

mysql> set time_zone = '+8:00';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT REPLACE(TIME_FORMAT(NOW() - UTC_TIMESTAMP(), '+%H:%i'), '-+', '-');

+--------------------------------------------------------------------+
| REPLACE(TIME_FORMAT(NOW() - UTC_TIMESTAMP(), '+%H:%i'), '-+', '-') |
+--------------------------------------------------------------------+
| +08:00                                                             |
+--------------------------------------------------------------------+

1 row in set (0.01 sec)