TRUNC関数とは？
TRUNC関数とは、引数に指定した数値や日時の切り捨てができる関数です。
引数を増やすことで切り捨てる位置を指定することもできます。
TRUNC関数の構文
TRUNC関数は次のように記述して使います。
TRUNC(数値)
数値の小数点以下を切り捨て、整数を返す構文です。
指定した桁位置で切り捨てたい場合は、次のように記述しましょう。
TRUNC(数値, 小数点以下の桁位置)
また、日付を切り捨てる場合は、引数にDATE型の値を記述します。
TRUNC(日付)
指定した時間を切り捨てたい場合は、次のように記述しましょう。
TRUNC(日付, 切り捨てる時間の要素)
PostgreSQLでも使用可能
TRUNC関数はOracle Databaseで使われる関数ですが、PostgreSQLでも使用可能です。
この記事ではPostgreSQLで使い方を解説します。
MySQLではTRUNCATE関数を使う
MySQLで指定した小数点以下の桁数を切り捨てたい場合は「TRUNCATE関数」を使います。
TRUNC関数ではないので注意しましょう。
TRUNCATE関数の使い方は、次の記事でも詳しく解説していますので参考までに。
【関連記事】
▶︎MySQLのtruncateとは？テーブル削除と切り捨ての2つの機能について解説
TRUNC関数の使い方
ここでは、サンプルを用いながら実際にTRUNC関数の使い方を見ていきましょう。
数値を切り捨てる方法
まずは数値を切り捨ててみます。
mydb=# SELECT TRUNC(12.34);
trunc
-------
12
(1 row)
TRUNC関数の引数「12.34」の少数部分を切り取り、「12」だけを取得しました。
次に、切り取りたい桁数を指定して取得してみましょう。
mydb=# SELECT TRUNC(3.14159265359, 7); trunc ----------- 3.1415926 (1 row)
引数の数値の後ろに、小数点以下の桁位置を指定すればOKです。
ここでは小数点以下7桁を残し、それ以降を切り捨てました。
第2引数にマイナス値を指定すれば、一の位や十の位などの切り捨ても可能です。
mydb=# SELECT TRUNC(1234.567, -1); trunc ------- 1230 (1 row) mydb=# SELECT TRUNC(1234.567, -2); trunc ------- 1200 (1 row)
このように任意の位置で数値の切り捨てができます！
MySQLで使うTRUNCATE関数についても使い方を確認してみましょう。
次のように記述して数値を切り捨てます。
mysql> SELECT TRUNCATE(12.34, 0); +--------------------+ | TRUNCATE(12.34, 0) | +--------------------+ | 12 | +--------------------+ 1 row in set (0.00 sec)
TRUNC関数とは異なり、第2引数の指定が必須です。
整数で取得場合は「0」を指定しましょう。
それ以外の指定はTRUNC関数と同じです。
mysql> SELECT TRUNCATE(12.34, 1); +--------------------+ | TRUNCATE(12.34, 1) | +--------------------+ | 12.3 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT TRUNCATE(12.34, -1); +---------------------+ | TRUNCATE(12.34, -1) | +---------------------+ | 10 | +---------------------+ 1 row in set (0.00 sec)
それぞれ指定した位置で切り捨てが行われています。
余談ですが、第1引数内で演算した結果の切り捨ても可能です。
mysql> SELECT TRUNCATE(1.23456*100, 0); +--------------------------+ | TRUNCATE(1.23456*100, 0) | +--------------------------+ | 123 | +--------------------------+ 1 row in set (0.00 sec)
このように、演算結果の「123.456」を整数で切り捨てできています。
日付を切り捨てる方法
日付の切り捨て方法を見ていきましょう。
Oracle Databaseでは、次のように記述します。
SELECT TRUNC(SYSDATE, 'MM')
■実行結果
trunc -------- 20-08-01
第2引数に指定できるフォーマットは次の通りです。
| 要素 | 説明 |
|---|---|
| CC | 西暦 |
| YYYY | 年 |
| IYYY | ISO 年 |
| Q | 四半期 |
| MM | 月 |
| WW | その年の最初の曜日 |
| IW | ISO 年での最初の曜日 |
| W | その月の最初の曜日 |
| DAY | 週 |
| DD | 日 （デフォルト |
| HH | 時 |
| MI | 分 |
PostgreSQLで日付を切り捨てるには、DATE_TRUNC関数を使う
PostgreSQLで日付を切り捨てる場合は「DATE_TRUNC関数」を使います。
書式は次の通りです。
DATE_TRUNC(切り捨てる時間の要素, timestamp 時間)
切り捨てる時間の要素には、次の要素が指定できます。
| 要素 | 説明 |
|---|---|
| microseconds | マイクロ秒 |
| milliseconds | ミリ秒 |
| second | 秒 |
| minute | 分 |
| hour | 時 |
| day | 日 |
| month | 月 |
| year | 年 |
| decade | 十年 |
| century | 世紀 |
| millennium | ミレニアム |
サンプルで取得結果を確認してみましょう。
mydb=# SELECT DATE_TRUNC('hour', timestamp '2020-08-17 15:02:40');
date_trunc
---------------------
2020-08-17 15:00:00
(1 row)
このように、hour(時間)までを表示し、「分」と「秒」だけ切り捨てられました。
month(月)以下を切り捨てる
month(月)以下を切り捨てたい場合は、第1引数を「month」にすればOKです。
mydb=# SELECT DATE_TRUNC('month', timestamp '2020-08-17 15:02:40');
date_trunc
---------------------
2020-08-01 00:00:00
(1 row)
取得結果は上記のようになります。
MySQLでは「DATE_FORMAT関数」で擬似的に切り捨て可能
MySQLでは、TRUNC関数やDATE_TRUNC関数のように日付・時間の切り捨てができる関数が用意されていません。
あえて擬似的に似た処理をするSQLクエリを書くのであれば、「DATE_FORMAT関数」を使って以下のように記述します。
mysql> SELECT DATE_FORMAT(CURRENT_DATE,'%Y-%m-01'); +--------------------------------------+ | DATE_FORMAT(CURRENT_DATE,'%Y-%m-01') | +--------------------------------------+ | 2020-08-01 | +--------------------------------------+ 1 row in set (0.00 sec)
これでTRUNC関数やDATE_TRUNC関数のように、日付の切り捨てができています。
時間を切り捨てる
時間を切り捨てる場合は、第1引数に次の要素を指定しましょう。
- microseconds (マイクロ秒)
- milliseconds (ミリ秒)
- second (秒)
- minute (分)
- hour (時)
mydb=# SELECT DATE_TRUNC('minute', timestamp '2020-08-17 15:02:40');
date_trunc
---------------------
2020-08-17 15:02:00
(1 row)
取得結果は上記のようになります。
データ全部を削除するTRUNCATE関数
TRUNC関数と似た関数に「TRUNCATE関数」があります。
「MySQLではTRUNCATE関数を使う」で紹介しましたが、Oracle DatabaseやPostgreSQLではテーブル内のデータを全削除する関数として使われます。
TRUNC関数とは処理が大きく異なるため、使い分けには注意しましょう。
TRUNCATE関数の構文
TRUNCATE関数は次のように記述して使います。
TRUNCATE TABLE テーブル名
TRUNCATE関数は、データをすべて削除する関数のためWHERE句などで削除するデータを指定できません。
サンプルで処理を確認してみましょう。
ここでは、以下の「sample_tb」テーブルのデータを削除します。
mydb=# SELECT * FROM sample_tb; id | name | gender ----+----------+-------- 2 | potepan2 | female 3 | potepan3 | female 4 | potepan4 | male 5 | potepan5 | male 1 | potepan | male (5 rows) mydb=# TRUNCATE TABLE sample_tb; TRUNCATE TABLE
上記のように、「TRUNCATE TABLE」と表示されれば削除完了です。
もう一度テーブル内のデータを確認してみると、削除されているのがわかります。
id | name | gender ----+------+-------- (0 rows)
WHERE句で条件を指定しようとすると、以下のようにエラーが表示されます。
mydb=# TRUNCATE TABLE sample_tb2 WHERE city = 'Tokyo'; ERROR: syntax error at or near "WHERE" LINE 1: TRUNCATE TABLE sample_tb2 WHERE city = 'Tokyo';
まとめ
SQLのTRUNC関数について解説しました。
TRUNC関数は、数値や日時を任意の位置で切り取れる関数で、データを整形した状態で取得したい場合に有効活用できます。
DBMSごとに関数名が異なるので、使用DBに合わせて関数は確認して使いましょう。
